PostgreSQL数据库wal进程故障处理

select client_addr,sync_state from pg_stat_replication;
\x on
select * from pg_stat_replication;

ps -ef | grep wal

su - postgres
pg_ctl stop
rm -rf data/pgsql/data/*

pg_basebackup -h 10.51.x.1x -D /data/pgsql/data -p 5432 -U repl -Fp -Xs -Pv -R --checkpoint=fast

vim /data/pgsql/data/postgresql.conf
###修改连接数，一定要比主库大
max_connections = 2000
#从机信息和连接用户
primary_conninfo = 'host=10.51.0.109 user=repl password=123456'
#说明恢复到最新状态
recovery_target_timeline = latest
#大于主节点，正式环境重新考虑此值的大小
max_connections = 120
#说明这台机器不仅可以用户数据归档，还可以用于数据查询
hot_standby = on
#流备份的最大延迟时间
max_standby_archive_delay = 30s
#向主机汇报本地状态的间隔时间
wal_receiver_status_interval = 10s
#出现错误复制，向主机反馈
hot_standby_feedback = on
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'

#在data下创建一个standby.signal文件 （默认情况刚刚发备份复制命令已经默认创建这个文件了只需要修改就行）

vim standby.signal
standby_mode = 'on'

su - postgres
pg_ctl start

find /data/pgsql/data/pg_wal/xxxxxxxxxxxxxxxx

vim /data/pgsql/data/postgresql.conf
max_wal_senders = 10
wal_keep_size = 400GB
#让主库的pg_wal目录下的wal日志保留400G，按照我这边的业务增长，每小时100+G，400可以保留接近3小时，够时间恢复了
full_page_writes = on
wal_log_hints = on

###保存，然后重新加载一下配置文件
su - postgres
pg_ctl reload

[root@bds-prd-pg-s data]# ps -ef | grep wal
root 5308 32711  0 11:02 pts/1    00:00:00 grep --color=auto wal
postgres 9131  8233  8 Feb07 ? 01:23:19 postgres: walreceiver streaming 2D593/28B7E000

etl=#select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
####显示f为主库 t为从库
etl=# \x
Expanded display is on.
etl=#select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 8057
usesysid | 16384
usename | repl
application_name | walreceiver
client_addr | XX.XX.0.108
client_hostname |
client_port | 43306
backend_start | 2024-02-07 18:26:30.129682+08
backend_xmin | 62649322
state | streaming
sent_lsn | 2D596/48FA000
write_lsn | 2D596/48FA000
flush_lsn | 2D596/48FA000
replay_lsn | 2D596/48F9A70
write_lag | 00:00:00.023841
flush_lag | 00:00:00.030618
replay_lag | 00:00:00.249837
sync_priority | 0
sync_state | async
reply_time | 2024-02-08 11:05:57.224484+08

etl=# \x
Expanded display is on.
etl=#select * from pg_stat_wal_receiver;
-[ RECORD 1 ]
pid | 9131
status | streaming
receive_start_lsn | 2D23C/7D000000
receive_start_tli | 2
written_lsn | 2D59B/1BA50000
flushed_lsn | 2D59B/1BA50000
received_tli | 2
last_msg_send_time | 2024-02-08 11:12:09.260339+08
last_msg_receipt_time | 2024-02-08 11:12:09.263823+08
latest_end_lsn | 2D59B/1BA50000
latest_end_time | 2024-02-08 11:12:09.002218+08
slot_name |
sender_host | XX.XX.0.109
sender_port | 5432
conninfo | user=repl password=******** channel_binding=prefer dbname=replication host=10.51.0.109 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any