PostgreSQL逻辑复制大表初始数据同步失败问题求助

问题场景：同步8万行左右的小表时初始数据复制正常，但同步百万/千万级大表时初始数据复制失败。已创建发布pub_a和订阅sub_a，连接成功，但订阅端日志先出现could not receive data from client: Connection reset by peer，之后出现remaining connection slots are reserved for non-replication superuser connections，发布端日志显示An existing connection was forcibly closed by the remote host。

碰到过一模一样的大表逻辑复制卡壳的情况，结合你的日志信息，给你几个实用的排查和解决方向，你可以挨个尝试：

• 调整连接超时相关参数
  大表初始同步耗时远超小表，很容易被中间网络设备（比如防火墙）或者PostgreSQL自身的超时机制断开连接。

  • 检查并修改发布端和订阅端的TCP保活参数：tcp_keepalives_idle（建议设为300，即5分钟）、tcp_keepalives_interval（设为60）、tcp_keepalives_count（设为5），让连接更稳定，不容易被强制断开。
  • 调大订阅端的subscription_timeout参数，默认60秒完全不够大表同步，改成3600（1小时）甚至更高，修改后执行SELECT pg_reload_conf();重载配置即可，不用重启服务。

• 扩容数据库连接槽位
  日志里的remaining connection slots are reserved for non-replication superuser connections提示，说明当前连接数已经触碰到max_connections的上限了。大表同步时会产生大量用于导出数据的SELECT连接，很容易占满槽位：

  • 适当调高发布端的max_connections值，注意同时要对应调整shared_buffers等内存参数，避免内存不足。
  • 先清理掉发布端和订阅端的闲置长连接，腾出现有槽位再尝试同步。

• 手动分批完成大表初始同步
  直接让PostgreSQL自动同步千万级大表确实容易出问题，不如手动拆分步骤：

  1. 先暂停订阅：ALTER SUBSCRIPTION sub_a DISABLE;
  2. 在发布端把大表数据按主键范围分批导出（比如COPY a (id, col1, col2) TO '/tmp/a_batch1.csv' WHERE id BETWEEN 1 AND 100000;），再导入到订阅端对应的表中。
  3. 所有批次数据导入完成后，重新启用订阅并跳过初始同步：ALTER SUBSCRIPTION sub_a ENABLE PUBLICATION pub_a WITH (copy_data = false);
     这样订阅就只会同步后续的增量数据，不用再做全量同步了。

• 排查网络稳定性
  发布端日志里的An existing connection was forcibly closed by the remote host基本指向网络问题：

  • 测试两端的网络连通性，用ping或者mtr工具跑半小时以上，看看有没有丢包、延迟过高的情况。
  • 检查防火墙、路由器的连接超时设置，把PostgreSQL的端口（默认5432）加入白名单，并延长连接超时时间，避免中途断开同步连接。

• 优化初始同步的查询性能
  如果大表的导出查询太慢，也会拖长同步时间导致超时：

  • 确保大表的主键或者同步用到的字段有有效索引，减少查询扫描时间。
  • 临时调高发布端的work_mem参数，让导出数据时的排序、哈希操作能用到足够内存，避免磁盘IO拖慢速度。

备注：内容来源于stack exchange，提问作者m tam