流复制环境中重启主库后报错WAL被removed的解决方法

最新推荐文章于 2025-05-15 13:49:41 发布

原创最新推荐文章于 2025-05-15 13:49:41 发布 · 3.3k 阅读

0 ·

CC 4.0 BY-SA版权

Highgo DB 同时被 2 个专栏收录

171 篇文章

订阅专栏

PostgreSQL

162 篇文章

订阅专栏

本文介绍了解决PostgreSQL重启后出现WAL段已移除错误的方法。通过调整wal_keep_segments参数并重启数据库，确保旧WAL文件不会被过早删除，从而避免复制环境中出现WAL段缺失的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

流复制环境中重启主库后报错WAL被removed的解决方法
[postgres@db1 testspace]$ /usr/local/pgsql/bin/pg_ctl -D ./pgdb -o "--ssha_role=primary" start
server starting
[postgres@db1 testspace]$ LOG: database system was shut down at 2017-05-31 10:54:57 CST
LOG: MultiXact member wraparound protections are now enabled
LOG: autovacuum launcher started
LOG: database system is ready to accept connections

[postgres@db1 testspace]$ ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
ERROR: requested WAL segment 00000001000000000000000B has already been removed
LOG: received fast shutdown request
LOG: aborting any active transactions
LOG: autovacuum launcher shutting down

修改postgresql.conf中的参数wal_keep_segments为10（默认为0）
然后reload使参数生效

[postgres@db1 pgdb]$ /usr/local/pgsql/bin/pg_ctl reload -D /home/postgres/testspace/pgdb/
server signaled
LOG: received SIGHUP, reloading configuration files
[postgres@db1 pgdb]$ LOG: parameter "wal_keep_segments" changed to "10"

[postgres@db1 pgdb]$ /usr/local/pgsql/bin/psql -d postgres
psql (9.6.2)
Type "help" for help.

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

[postgres@db1 testspace]$ /usr/local/pgsql/bin/pg_ctl -D ./pgdb -o "--ssha_role=primary" stop
waiting for server to shut down...LOG: received fast shutdown request
.LOG: aborting any active transactions
LOG: autovacuum launcher shutting down
FATAL: terminating connection due to administrator command
LOG: shutting down
LOG: database system is shut down
done
server stopped
[postgres@db1 testspace]$ /usr/local/pgsql/bin/pg_ctl -D ./pgdb -o "--ssha_role=primary" start
server starting
[postgres@db1 testspace]$ LOG: database system was shut down at 2017-05-31 11:37:46 CST
LOG: MultiXact member wraparound protections are now enabled
LOG: autovacuum launcher started
LOG: database system is ready to accept connections

[postgres@db1 testspace]$ /usr/local/pgsql/bin/psql -d postgres
psql (9.6.2)
Type "help" for help.

postgres=#
postgres=# select * from test;
id
----
1
2
3
4
5
6
7
(7 rows)

postgres=# insert into test values (8);
INSERT 0 1
postgres=#