含多字段、函数及索引的PostgreSQL表逻辑复制故障求助

PostgreSQL逻辑复制无数据同步问题排查 #

问题场景 #

我有一张需复制的表，包含1600万行数据、115列（其中50个varchar列），主键为36字符的UUID，还有15个索引、部分依赖及若干函数。
执行步骤：

• 将表复制到订阅端，跳过复制初始化阶段
• 设置replica identity = default
• 创建发布（publication）
• 创建订阅：

CREATE SUBSCRIPTION try1 CONNECTION 'host=10.100.9.40 port=5432 user=jhon password=gr3AT dbname=db_profile_20210714' PUBLICATION pub_try1 with (copy_data = false);

但无数据同步到订阅端。每隔3秒在发布端执行以下语句查看WAL差异：

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) FROM pg_stat_replication

结果循环出现10000、5000、0。

数据库配置

测试验证

测试同结构（同列数、索引等但无函数）的小数据量表（5000行及空表），创建copy_data = false和copy_data = true两个订阅，通过INSERT INTO table_publisher SELECT * FROM table插入数据，均能正常同步。

问题解答 #

1. 数据量大小会影响复制吗？我认为不会

数据量本身不会直接阻止逻辑复制，但会间接产生影响：

• 若涉及初始全量同步，大表可能因WAL生成过快引发性能问题，但你已通过copy_data=false跳过初始化，所以此场景下不是主因。不过大表的索引、约束较多，可能在订阅端应用WAL时产生性能瓶颈，但你当前连新数据都未同步，暂时不考虑此情况。

2. 表数据来自应用程序是否会有影响？

不会。无论数据是应用程序写入还是批量INSERT语句插入，PostgreSQL逻辑复制都是基于WAL日志捕获变更，只要变更被正确记录到WAL，就应当能被同步。除非应用程序使用了UNLOGGED表等特殊写入方式，但你此处为普通表，因此无影响。

3. 函数是否会阻塞复制？

是的，这很可能是问题根源。逻辑复制对函数有严格要求：

• 若表上存在不稳定函数（VOLATILE）或自定义触发器函数，可能导致逻辑解码无法正确捕获变更，或订阅端无法应用变更。
• 若函数涉及非确定性操作（如now()、random()），或修改了其他表的数据，逻辑复制可能跳过这些变更，或应用失败后停止同步。
• 另外，若函数为SECURITY DEFINER类型，权限问题也可能导致订阅端无法执行，进而阻塞复制。

4. 如何让该复制正常工作？

按以下步骤排查修复：

1. 检查函数和触发器：
   • 列出表上所有函数和触发器：

     SELECT proname, provolatile FROM pg_proc JOIN pg_trigger ON pg_proc.oid = pg_trigger.tgfoid WHERE pg_trigger.tgrelid = 'your_table'::regclass;
     

   • 移除或修改不稳定函数，确保所有涉及函数为IMMUTABLE或STABLE，且不包含非确定性逻辑。
   • 暂时禁用表上的自定义触发器，测试是否能正常同步，逐步定位问题函数。
2. 验证发布和订阅配置：
   • 确认发布包含目标表：SELECT * FROM pg_publication_tables WHERE pubname = 'pub_try1';
   • 检查订阅状态：SELECT * FROM pg_subscription WHERE subname = 'try1';，确保subconninfo正确、subenabled为true。
3. 查看复制日志：
   • 发布端查看PostgreSQL日志，搜索replication相关错误；订阅端查看日志，搜索subscription或apply相关错误，日志会明确指出同步失败原因。
4. 调整复制参数：
   • 尝试增大wal_sender_timeout（如设为120000ms），避免网络波动导致连接中断；同时确认max_replication_slots和max_wal_senders有剩余可用额度。
5. 重新初始化订阅：
   • 若以上步骤未解决问题，先删除订阅，再重新创建（可先使用copy_data=true测试全量同步是否正常，再切换至增量同步），确保订阅端表结构与发布端完全一致（包括函数、约束、索引）。

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