PostgreSQL旧库执行CRUD新库同步接数的一致性方案咨询

你构思的业务层发MQ同步方案属于典型的重复造轮子，而且是可靠性远低于数据库原生能力的轮子，PostgreSQL生态针对这类单主多从、只读副本同步的场景已经有非常成熟的标准实践，完全不需要在业务层实现同步逻辑。

这是业界同PG版本、同构只读副本场景的通用最优解，完全匹配你的需求：

• 同步基于WAL预写日志的物理块复制，会100%复刻主库（也就是你的A库）的所有内容：表结构、主外键关联、约束、索引、触发器、存储过程，甚至数据库配置都完全一致，不存在逻辑同步漏字段、漏约束的问题
• 架构天然符合要求：主库A正常处理所有CRUD请求，从库B默认处于只读状态，仅接收日志重放，不会接受业务写入
• 一致性是原生保障的：主库会按事务顺序持久化WAL日志后发送给从库，从库按相同顺序重放日志。如果B库宕机、主从网络中断，恢复后B库会自动从上次中断的LSN（日志序列号）位置断点续传追数，全程不需要人工介入，也不会丢任何变更；追平数据之前你可以配置从库拒绝访问，避免读到不一致的中间数据
• 部署无业务侵入：不需要修改任何Node.js业务代码，只需要调整数据库配置：
  1. 主库A修改postgresql.conf，设置wal_level = replica，开启归档相关配置
  2. 主库A修改pg_hba.conf，放通B库的复制连接权限
  3. 在B库上用pg_basebackup拉取A库的全量基础备份完成初始化
  4. 配置B库的主库连接信息后启动，B库会自动进入从库状态持续同步
• 如果需要强一致保证，可以配置同步复制模式：在主库配置synchronous_standby_names指定B库为同步备，设置synchronous_commit = on，此时主库的每笔事务提交，必须等B库收到日志并落盘后才会给业务返回成功，两库数据零误差，代价是写入延迟会增加主从之间的网络RTT，同机房部署下这个延迟通常在1ms以内，基本无感知。

如果你的B库后续需要存储自有业务数据、不需要完全和A库物理一致，或者PG跨大版本部署，可以选PG10版本之后自带的逻辑复制：

• 基于发布订阅模型：在A库上创建PUBLICATION，把需要同步的表加入发布范围；在B库上创建SUBSCRIPTION指向A库的发布，初始化阶段会自动完成全量数据拷贝，后续持续同步对应表的INSERT/UPDATE/DELETE操作
• 同样自带断点续传、位点持久化能力，B库宕机恢复后会自动从上次消费的位置继续追数，不丢数据
• 注意缺陷：逻辑复制默认不同步DDL变更，如果A库后续加字段、改表结构，需要先在B库执行对应的DDL语句，否则同步会报错中断。

你最初构思的方案存在本质的架构缺陷，靠MQ重试根本解决不了：

• 无事务原子性保障：业务代码里提交A库事务、发送MQ消息这两个操作无法做到原子一致，比如A库事务提交成功，发MQ前服务宕机，这条变更会永久丢失，MQ的重试机制完全覆盖不到这个场景
• 乱序风险：同一条数据的多次变更如果因为MQ重试、分区路由错误出现乱序（比如先删后改的消息变成先改后删），B库会直接产生脏数据
• 维护成本极高：你需要给所有表的所有操作做幂等处理、自己维护消费位点、处理宕机后的消息堆积重放、处理DDL变更的同步逻辑，相当于从零实现了一遍数据库原生复制的能力，可靠性还远不如原生实现，只要漏一个异常分支就会出现数据不一致，排查成本极高。

哪怕用原生复制方案，也建议做定期校验兜底，避免磁盘坏块、从库误写入等极端情况导致的不一致：

• 开启PG的数据页校验功能，定期用内置工具对比两库的表结构、数据行内容
• 核心表可以按天做全量校验，非核心表按周校验即可，发现不一致直接重新拉取全量备份恢复B库就行，操作成本很低。

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