如何实现本地与远程PostgreSQL特定Schema的同步（含断网续传）

核心思路 #

你的需求核心是捕获本地指定Schema的所有增删改变更，在线时实时推送到远程；离线时暂存本地队列，网络恢复后自动重试同步，同时保障数据一致性。

基础准备 #

• 明确同步方向：单向（本地→远程）逻辑更简单，双向同步需额外处理冲突。
• 确保本地/远程PostgreSQL版本兼容（同版本或差1个大版本以内），双方postgresql.conf中设置wal_level = logical，重启服务生效。
• 初始全量同步：用pg_dump导出指定Schema并导入远程库，保证初始数据一致：

  # 导出本地目标Schema
  pg_dump -h localhost -U username -d dbname -n target_schema > schema_dump.sql
  # 导入远程库
  psql -h remote_host -U remote_username -d remote_dbname -f schema_dump.sql
  

在线即时同步（场景一） #

方案1：原生逻辑复制（推荐） #

PostgreSQL原生支持特定Schema的增量同步，在线实时推送变更：

• 本地库创建发布：

  CREATE PUBLICATION pub_target_schema FOR SCHEMA target_schema;
  

• 远程库创建订阅：

  CREATE SUBSCRIPTION sub_local_target_schema
  CONNECTION 'host=local_host port=5432 dbname=local_dbname user=username password=xxx'
  PUBLICATION pub_target_schema;
  

• 优势：无需额外工具，性能稳定，仅同步增量；不足：默认不同步DDL，双向同步需两端分别配置发布订阅。

方案2：触发器+自定义同步脚本 #

适合需要灵活控制（如过滤特定表、修改字段后同步）的场景：

• 在目标Schema的所有表上创建触发器，将增删改操作记录到本地change_log表（字段包含操作类型、表名、数据内容、时间戳、同步状态）。
• 编写定时脚本（Python/Shell均可），在线时读取change_log中未同步记录，推送到远程库并标记状态为已同步。

离线缓存与自动同步（场景二） #

逻辑复制适配离线 #

逻辑复制会自动缓存未同步的WAL日志，网络恢复后自动续传，但需配置防止WAL被提前清理：

-- 本地库创建持久化复制槽，避免WAL被清理
SELECT pg_create_logical_replication_slot('slot_target_schema', 'pgoutput');

修改本地postgresql.conf：

wal_keep_size = 1GB  # 根据预期离线时长调整大小

自定义触发器方案适配离线 #

change_log表本身就是离线缓存，部署守护进程（如用systemd托管的Python脚本）：

• 定期检测网络连通性（如ping远程数据库端口）。
• 网络恢复后，批量同步change_log中未同步记录，同步成功标记为已同步，失败则按固定间隔重试。

双向同步冲突处理 #

若为双向同步，需解决两端并发修改的冲突：

• 最后写入获胜：给表添加last_updated字段，同步时保留时间戳最新的版本。
• 版本号策略：给表添加version字段，每次更新时版本号+1，仅当本地版本高于远程版本时才执行同步。

工具选型 #

• 极简稳定：优先选择原生逻辑复制+复制槽方案。
• 复杂业务需求：使用触发器+自定义脚本/任务队列（如Celery）。
• 企业级大规模场景：采用Debezium（基于Kafka的CDC工具），捕获变更后缓存到Kafka，在线时实时消费同步，离线时自动留存消息，网络恢复后继续同步。

监控与告警 #

• 逻辑复制：通过pg_stat_subscription视图查看订阅同步状态。
• 自定义方案：监控change_log表中未同步记录的数量。
• 告警配置：当未同步记录超过阈值或同步失败次数过多时，触发邮件/短信告警。

内容的提问来源于stack exchange，提问作者Sandeep Prasad Kushwaha