【PgSQL日常管理】WAL日志管理

一、防止WAL日志过早删除

PostgreSQL提供管理checkpoint用途的WAL日志文件空间的相关配置

max_wal_size：用于设置允许WAL日志文件空间的最大增长上限

min_wal_size：用于设置需要保留的最小WAL日志文件空间

1、设置wal_keep_segments

wal_keep_segments指定在pg_wal目录保留的过去WAL日志文件的最小个数，以防止某个备节点需要获取WAL日志文件的流复制

2、开启WAL日志归档

3、启用复制槽

本例为1主2备库（1个从库sync模式，1个从库异步模式）

postgres=# SELECT * FROM pg_create_physical_replication_slot('pg_slot_100');
slot_name | lsn
-------------+-----
pg_slot_100 |
(1 row)
postgres=# SELECT * FROM pg_create_physical_replication_slot('pg_slot_101');
slot_name | lsn
-------------+-----
pg_slot_101 |
(1 row)

postgres=# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
pg_slot_100 | | physical | | | f | f | | | | |
pg_slot_101 | | physical | | | f | f | | | | |
(2 rows) 

postgres=# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
pg_slot_100 | | physical | | | f | t | 7179 | | | 0/18000000 |
pg_slot_101 | | physical | | | f | t | 7405 | | | 0/18000000 |
(2 rows)

postgres=#

备库的pg_log日志信息
2021-03-15 16:12:00.100 CST,,,11207,,604f16cf.2bc7,2,,2021-03-15 16:11:59 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
2021-03-15 16:12:00.107 CST,,,11214,,604f16d0.2bce,1,,2021-03-15 16:12:00 CST,,0,LOG,00000,"started streaming WAL from primary at 0/17000000 on timeline 1",,,,,,,,,""

 

postgres=# select name,setting from pg_settings where name like 'max_wal_size';
name | setting
--------------+---------
max_wal_size | 1024
(1 row)

postgres=# select name,setting from pg_settings where name like 'min_wal_size';
name | setting
--------------+---------
min_wal_size | 80
(1 row)

postgres=# select name,setting from pg_settings where name like 'synchronous_commit';
name | setting
--------------------+--------------
synchronous_commit | remote_write
(1 row)

postgres=# select name,setting from pg_settings where name like 'synchronous_standby_names';
name | setting
---------------------------+---------
synchronous_standby_names |
(1 row)

postgres=# select client_addr,sync_state from pg_stat_replication;
client_addr | sync_state
----------------+------------
10.13.18.101 | sync
10.13.18.100 | potential
(2 rows)

关闭 10.13.18.101的PG，

postgres=# select client_addr,sync_state from pg_stat_replication;
client_addr | sync_state
----------------+------------
10.13.18.100 | sync
(1 row)

2021-03-15 16:48:07.849 CST,"repuser","",14530,"10.13.18.101:44116",604f1f47.38c2,2,"authentication",2021-03-15 16:48:07 CST,3/5,0,LOG,00000,"replication connection authorized: user=repuser application_name=walreceiver",,,,,,,,,""
2021-03-15 16:48:07.951 CST,"repuser","",14530,"10.13.18.101:44116",604f1f47.38c2,3,"idle",2021-03-15 16:48:07 CST,3/0,0,LOG,00000,"standby ""walreceiver"" is now a synchronous standby with priority 1",,,,,,,,,"walreceiver"