如何实现开发与生产PostgreSQL数据库的自动化数据同步？

场景回顾 #

站点存储园区1200余种植物数据，涉及多表多字段，需处理拼写修正、属性修改（位置、尺寸、存活状态等）及后续新数据添加；当前手动同步开发/生产库的方式，无法应对未来数百项变更及大量新数据，系统均基于Ubuntu部署，开发服务器大多处于关闭状态，优先考虑实时同步，也接受定时同步，或反向从生产库同步至开发库。

方案一：开发库→生产库同步（符合初始思路） #

1. 定时同步（低复杂度，适配开发服务器不常启动） #

适合开发服务器间歇性运行的情况，用pg_dump+cron实现定时增量/全量同步：

• 编写同步脚本：
  • 若表中有更新时间字段，可导出增量数据，示例：

    #!/bin/bash
    LAST_SYNC=$(cat /var/lib/postgres/last_sync || echo "1970-01-01 00:00:00")
    pg_dump -h 开发服务器IP -U 用户名 -d 开发库名 --data-only --table=plants --table=plant_attributes --where="last_updated > '$LAST_SYNC'" -f /tmp/plant_changes.sql
    # 导入前先备份生产库关键表
    pg_dump -h 生产服务器IP -U 用户名 -d 生产库名 --table=plants --table=plant_attributes -f /tmp/prod_backup_$(date +%Y%m%d_%H%M).sql
    # 导入变更
    psql -h 生产服务器IP -U 用户名 -d 生产库名 -f /tmp/plant_changes.sql
    # 更新同步时间
    date +"%Y-%m-%d %H:%M:%S" > /var/lib/postgres/last_sync
    # 清理临时文件
    rm /tmp/plant_changes.sql
    

  • 若没有更新时间字段，可每天执行全量同步（适合数据量不大的情况）。
• 用cron定时触发：执行crontab -e，添加定时规则，比如每小时同步一次：

  0 * * * * /path/to/your/sync_script.sh
  

• 注意：给执行脚本的用户配置~/.pgpass文件实现免密访问，避免脚本卡死在密码输入。

2. 实时同步（开发服务器持续运行时可用） #

用PostgreSQL逻辑复制实现实时同步，仅适合开发服务器保持在线的时段：

• 在开发库创建发布：

  -- 同步指定表，或用FOR ALL TABLES同步所有表
  CREATE PUBLICATION plant_sync FOR TABLE plants, plant_attributes;
  

• 在生产库创建订阅：

  CREATE SUBSCRIPTION plant_sub 
  CONNECTION 'host=开发服务器IP dbname=开发库名 user=用户名 password=密码' 
  PUBLICATION plant_sync;
  

• 注意：开发服务器关闭后，重新启动时订阅会自动同步离线期间的变更，但需要确保开发服务器的wal_level设置为logical（在postgresql.conf中配置）。

方案二：生产库→开发库同步（反向思路，更适配开发服务器常关闭的现状） #

所有变更直接在生产库操作，开发服务器启动时同步生产库最新数据：

1. 启动时全量同步 #

编写启动脚本，加到开发服务器的启动流程中：

#!/bin/bash
# 停止依赖开发库的服务（如果有）
systemctl stop plant_dev_service
# 备份开发库
pg_dump -U 用户名 -d 开发库名 -f /tmp/dev_backup_$(date +%Y%m%d_%H%M).sql
# 导入生产库全量数据
pg_dump -h 生产服务器IP -U 用户名 -d 生产库名 -f /tmp/prod_full_dump.sql
psql -U 用户名 -d 开发库名 -f /tmp/prod_full_dump.sql
# 重启服务
systemctl start plant_dev_service
# 清理临时文件
rm /tmp/prod_full_dump.sql

• Ubuntu系统可将脚本加入/etc/rc.local（18.04及以前），或创建systemd服务在开机时执行。

2. 启动时增量同步 #

若全量同步耗时久，且生产库有更新时间字段，可只同步上次同步后的变更：

• 脚本逻辑：读取上次同步时间→导出生产库增量数据→导入开发库→更新同步时间，类似方案一的增量脚本，只是方向反转。

额外注意事项 #

• 所有同步操作前，必须备份生产库，避免误操作导致数据丢失。
• 涉及表结构变更（比如新增字段）时，先在开发库测试验证，再用pg_dump --schema-only导出结构变更语句，在生产库执行后再同步数据。
• 后续开发的本地添加工具，直接连接目标库（开发库用方案一，生产库用方案二），彻底避免手动录入。

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