适用范围

Postgresql 9 and later

背景：

PostgreSql主从模式的数据库运行中，如果从库丢失了从主库同步过来的wal文件的话，会报以下错误：

主库看到如下错误日志： ERROR: requested WAL segment 00000001000000170000001C has already been removed 从库看到如下错误日志： FATAL: could not receive data from WAL stream：ERROR: requested WAL segment 00000001000000170000001C has already been removed

故障解决方案：

1、备库关闭

[postgres@ora11g-node01 ~]$ pg_ctl stop

2、主库日志参数wal_keep_segments设置：

主库相关参数archive_command、max_wal_size、min_wal_size、wal_keep_segments设置如下，其中设置wal_keep_segments默认为0表示wal日志归档后，就删除

postgres=# show archive_command;
                    archive_command                     
--------------------------------------------------------
 test ! -f /backup/pgarch/%f && cp %p /backup/pgarch/%f
(1 row)

postgres=# show max_wal_size;
 max_wal_size 
--------------
 1GB
(1 row)

postgres=# show min_wal_size;
 min_wal_size 
--------------
 80MB
(1 row)

postgres=# show wal_keep_segments;
 wal_keep_segments 
-------------------
 0
(1 row)

3、主库进行日志切换、事务操作

  postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 7/8B11D1B8
(1 row)

postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 7/8C028200
(1 row)

postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 7/8D000000
(1 row)

postgres=# create table t100 as select * from pg_class;
SELECT 10182

postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 7/8D5B1A00
(1 row)

postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 7/8E08C108
(1 row)

postgres=# checkpoint;
CHECKPOINT

主库切换wal日志前信息：

[postgres@ora11g-node02 ~]$ ls -l /data/pgsql/pg_wal/
total 114720
-rw------- 1 postgres postgres      204 Jul 14 22:27 00000007.history
-rw------- 1 postgres postgres      247 Jul 14 22:27 00000008.history
-rw------- 1 postgres postgres      290 Jul 14 22:27 00000009.history