PostgreSQL 13逻辑复制周期性消失，接收端订阅异常排查求助

问题描述 #

基于PostgreSQL 13搭建双向逻辑复制架构（发布<->订阅），多数场景运行正常，但每两周左右内部复制监控会触发告警，提示复制“不同步”，底层信息显示接收端不存在订阅。目前未发现关联触发事件，且订阅未被手动删除。

更新：日志信息 #

主库日志 #

10:41:35.486 LOG:  received replication command: IDENTIFY_SYSTEM
10:41:35.486 STATEMENT:  IDENTIFY_SYSTEM
10:41:35.488 LOG:  received replication command: START_REPLICATION SLOT "pub_20240606_0838" LOGICAL DB6/94B7D960 (proto_version '1', publication_names '"pub_20240606_0838"')
10:41:35.488 STATEMENT:  START_REPLICATION SLOT "pub_20240606_0838" LOGICAL DB6/94B7D960 (proto_version '1', publication_names '"pub_20240606_0838"')
10:41:35.489 LOG:  starting logical decoding for slot "pub_20240606_0838"
10:41:35.489 DETAIL:  Streaming transactions committing after DB6/94B7BA68, reading WAL from DB6/94B7B980.
10:41:35.489 STATEMENT:  START_REPLICATION SLOT "pub_20240606_0838" LOGICAL DB6/94B7D960 (proto_version '1', publication_names '"pub_20240606_0838"')
10:41:35.489 LOG:  logical decoding found consistent point at DB6/94B7B980
10:41:35.489 DETAIL:  There are no running transactions.
10:41:35.489 STATEMENT:  START_REPLICATION SLOT "pub_20240606_0838" LOGICAL DB6/94B7D960 (proto_version '1', publication_names '"pub_20240606_0838"')
10:41:40.544 LOG:  received replication command: IDENTIFY_SYSTEM
10:41:40.544 STATEMENT:  IDENTIFY_SYSTEM
10:41:40.546 LOG:  received replication command: START_REPLICATION SLOT "pub_20240606_0838" LOGICAL DB6/94B7D960 (proto_version '1', publication_names '"pub_20240606_0838"')
10:41:40.546 STATEMENT:  START_REPLICATION SLOT "pub_20240606_0838" LOGICAL DB6/94B7D960 (proto_version '1', publication_names '"pub_20240606_0838"')
10:41:40.548 LOG:  starting logical decoding for slot "pub_20240606_0838"
10:41:40.548 DETAIL:  Streaming transactions committing after DB6/94B7D960, reading WAL from DB6/94B7B980.
10:41:40.548 STATEMENT:  START_REPLICATION SLOT "pub_20240606_0838" LOGICAL DB6/94B7D960 (proto_version '1', publication_names '"pub_20240606_0838"')
10:41:40.549 LOG:  logical decoding found consistent point at DB6/94B7B980
10:41:40.549 DETAIL:  There are no running transactions.
10:41:40.549 STATEMENT:  START_REPLICATION SLOT "pub_20240606_0838" LOGICAL DB6/94B7D960 (proto_version '1', publication_names '"pub_20240606_0838"')
10:41:40.555 LOG:  could not send data to client: Connection reset by peer
10:41:40.555 CONTEXT:  slot "pub_20240606_0838", output plugin "pgoutput", in the change callback, associated LSN DB6/94B92E08
10:41:40.555 STATEMENT:  START_REPLICATION SLOT "pub_20240606_0838" LOGICAL DB6/94B7D960 (proto_version '1', publication_names '"pub_20240606_0838"')
10:41:45.595 LOG:  received replication command: IDENTIFY_SYSTEM
10:41:45.595 STATEMENT:  IDENTIFY_SYSTEM

副本库日志 #

10:41:35.422 ERROR:  duplicate key value violates unique constraint "idx_81203126_primary"
10:41:35.422 DETAIL:  Key (version)=(20240501095011) already exists.
10:41:35.434 LOG:  logical replication apply worker for subscription "sub_20240606_0838" has started
10:41:35.447 LOG:  background worker "logical replication worker" (PID 42) exited with exit code 1
10:41:35.495 ERROR:  duplicate key value violates unique constraint "idx_81203126_primary"
10:41:35.495 DETAIL:  Key (version)=(20240501095011) already exists.
10:41:35.498 LOG:  background worker "logical replication worker" (PID 19634) exited with exit code 1

问题分析与解决建议 #

核心原因 #

从日志可明确问题链路：

1. 副本库应用主库同步数据时，触发唯一键冲突（idx_81203126_primary约束，version字段值重复），导致逻辑复制后台worker进程异常退出（退出码1）。
2. worker进程退出后，主库端复制连接被主动重置（Connection reset by peer），后续主库尝试重建连接，但副本库订阅worker无法正常启动，最终监控检测到“接收端不存在订阅”的异常状态。

可能触发场景 #

• 双向复制架构中，同一数据被两端同时修改，同步时引发冲突；
• 订阅端存在未通过复制流程写入的本地数据，与主库同步数据产生键值冲突；
• 复制槽LSN位置异常，导致主库重复发送已同步数据。

解决步骤 #

1. 修复唯一键冲突：

   • 定位冲突表，查询重复version值：SELECT * FROM 表名 WHERE version = '20240501095011';
   • 根据业务逻辑清理重复数据，保留正确记录；
   • 若冲突周期性出现，需检查业务代码是否存在并发写入导致的键值重复问题，或调整双向复制冲突处理策略。

2. 恢复复制链路：

   • 重启订阅worker进程：ALTER SUBSCRIPTION sub_20240606_0838 REFRESH PUBLICATION;
   • 若重启无效，可删除订阅后重建：

     DROP SUBSCRIPTION sub_20240606_0838;
     CREATE SUBSCRIPTION sub_20240606_0838 CONNECTION '主库连接字符串' PUBLICATION pub_20240606_0838;
     

3. 预防措施：

   • 双向复制的表，尽量避免两端同时写入同一数据，或在业务层做冲突规避；
   • 开启逻辑复制冲突日志（调整log_min_messages为WARNING级别），提前发现冲突；
   • 定期检查复制槽状态，确保LSN位置正常，避免重复发送数据：SELECT * FROM pg_replication_slots;

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