PG主从流复制WAL被覆盖解决方式

最新推荐文章于 2025-06-25 08:54:32 发布

原创最新推荐文章于 2025-06-25 08:54:32 发布 · 3.8k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql

postgresql 专栏收录该内容

13 篇文章

订阅专栏

本文详细介绍了如何通过调整wal_keep_segments、配置archive_mode和archive_command，以及使用NFS等方法来解决PostgreSQL流复制过程中主从数据库不一致的问题。包括增加wal的日志保留时间和优化复制过程的步骤。

PostgreSQL的流复制的原理是通过传递主机(master)上的wal日志信息到备机(slave)然后恢复，这中间就有一个潜在的问题，如果主机端比较忙，wal日志被覆盖了，而从机可能因为网络或者其他原因没有接收到该日志，就会造成主从不一致

requested WAL segment 0000000100000001000000A has already been removed

方式1：

借用老外的办法，国内的基本都是照抄：
archive_mode enables WAL archiving which can be used to recover files older than wal_keep_segments provides. The slave servers simply need a method to retrieve the WAL segments. NFS is the simplest method, but anything from scp to http to tapes will work so long as it can be scripted.

on master

archive_mode = on
archive_command = ‘cp %p /path_to/archive/%f’

on slave

restore_command = ‘cp /path_to/archive/%f “%p”’

When the slave can’t pull the WAL segment directly from the master, it will attempt to use the restore_command to load it. You can configure the slave to automatically remove segments using the archive_cleanup_commandsetting