PostgreSQL 13 Replica服务器启用读写后Replication失效问题求助

我有两台PostgreSQL服务器：

• 10.1.1.49 - master（生产环境）
• 10.1.1.68 - slave（开发环境）

需要将生产服务器的数据复制到开发服务器，同时要求开发服务器支持写入操作。配置主从Replication后功能正常，但关闭开发服务器的只读选项并启用读写选项后，Replication不再工作。

Master服务器（10.1.1.49，生产环境）配置 #

pg_hba.conf #

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             10.1.1.0/24             md5
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    replication     postgres        10.1.1.68/24            md5

postgresql.conf #

listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 8
hot_standby = on

Slave服务器（10.1.1.68，开发环境）配置 #

pg_hba.conf #

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             10.1.1.0/24             md5
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                    peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    replication     postgres        10.1.1.49/24            md5

postgresql.conf #

listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 8
hot_standby = on

初始化复制执行的命令（Slave服务器上） #

rm -rf data; mkdir data; chmod go-rwx data
pg_basebackup -P -R -X stream -c fast -h 10.1.1.49 -U postgres -D ./data

PostgreSQL标准物理主从复制架构中，从库默认只读，开启读写后从库会生成本地WAL日志，与主库同步的WAL日志产生冲突，导致复制因数据不一致中断。要实现读写从库+主库数据同步，可选择以下方案：

方案1：使用逻辑复制（Logical Replication） #

逻辑复制基于表级别同步，允许从库写入，同时接收主库增量更新，不会因本地写入中断复制。

配置步骤： #

1. Master服务器调整：

   • 修改postgresql.conf：

     wal_level = logical
     max_replication_slots = 10
     max_wal_senders = 10
     

   • 重启Master，创建复制用户：

     CREATE USER repl_user WITH REPLICATION LOGIN PASSWORD 'your_password';
     

   • 在pg_hba.conf添加权限：

     host    replication     repl_user       10.1.1.68/24            md5
     

   • 重载配置：pg_ctl reload

2. Slave服务器配置：

   • 确保已通过pg_basebackup完成全量数据同步。
   • 在Master创建发布：

     CREATE PUBLICATION pub_prod FOR ALL TABLES;
     

   • 在Slave创建订阅：

     CREATE SUBSCRIPTION sub_dev CONNECTION 'host=10.1.1.49 port=5432 user=repl_user password=your_password dbname=your_db_name' PUBLICATION pub_prod;
     

方案2：转为独立实例，定期同步数据 #

若无需实时同步，可将Slave转为独立读写实例，定期从Master同步数据：

• 停止复制进程：SELECT pg_wal_replay_stop();
• 修改Slave的postgresql.conf，移除复制相关配置（如primary_conninfo）。
• 定期执行全量同步脚本，例如每周一次pg_basebackup覆盖Slave数据。

注意事项 #

• 物理复制不支持读写从库，块级同步机制会因本地写入破坏数据一致性。
• 逻辑复制需PostgreSQL 10+版本，默认不自动同步DDL，需手动在从库执行或通过pg_dump同步结构。

内容的提问来源于stack exchange，提问作者Jasur Abdunabiyev