Linux下使用 pgAdmin 进行数据同步的实用方案
一、方案总览与选择
- 备份与恢复（全量/时点）：用 pgAdmin 的备份/恢复功能导出为 SQL 或 自定义格式（.dump），适合一次性迁移或定期全量同步。
- 数据比较与同步（差异）：用 pgAdmin 的数据比较找出差异，再执行数据同步，适合小中型表或增量对齐。
- 定时/作业调度：用 pgAgent（pgAdmin 集成）或 pg_cron 执行周期性导出/导入或调用自定义同步脚本，适合准实时或定时的批量同步。
- 数据库复制（持续）：配置 流复制（物理复制） 或 逻辑复制，适合高可用/近实时同步；pgAdmin 4 支持创建订阅与复制连接，便于管理。
- 若目标是迁移 pgAdmin 自身的配置与元数据（而非业务库），需迁移 /etc/pgadmin 与 /var/lib/pgadmin 目录到新主机。
二、操作步骤
- 备份与恢复（全量/时点）
- 在 pgAdmin 中连接到源库，右键数据库 → 备份，选择格式（如 SQL 或 自定义），保存到本地/共享存储。
- 在目标库（先创建同名空库）右键 → 恢复，选择备份文件开始导入。
- 大库建议用自定义格式（.dump）+ pg_restore 并行恢复，效率更高。
- 数据比较与同步（差异）
- 在 pgAdmin 中打开数据比较工具，选择源库与目标库及需要比对的表。
- 生成差异报告，勾选需要同步的行/列，执行数据同步将差异写至目标库。
- 定时/作业调度（pgAgent 或 pg_cron）
- pgAgent：在 pgAdmin 左侧 pgAgent Jobs → Create → pgAgent Job，新建作业步骤（如执行备份/恢复脚本或 psql 命令），配置调度计划与通知。
- pg_cron：在目标库所在 PostgreSQL 启用扩展并创建定时任务（如每日 02:00 执行导出并导入），适合无图形界面的服务器。
- 数据库复制（持续）
- 流复制：配置主库 wal_level=replica、创建复制槽，备库使用 pg_basebackup 初始化并从 primary_conninfo 接入；pgAdmin 4 可添加复制连接、管理订阅。
- 逻辑复制：源库启用 wal_level=logical，创建 publication；目标库创建 subscription 指向源库 publication，实现表级近实时同步。
三、关键配置与权限要点
- 连接与网络：如需跨机同步，编辑 pg_hba.conf 放行来源网段（如 host all all 0.0.0.0/0 md5），并在 postgresql.conf 设置 listen_addresses=‘*’，重启 PostgreSQL。
- 权限：执行导出/导入/同步的数据库用户需具备相应权限（导出用 SELECT/READ，导入用 INSERT/CREATE，结构变更需 ALTER/DROP，建议用 postgres 或具备同权的专用账号）。
- 版本兼容：尽量保持源/目标 PostgreSQL 主版本一致；跨版本迁移需评估类型、函数与索引语法差异。
- 大对象与特殊类型：BLOB/JSONB/几何等对象在导出/导入时需保持客户端编码与扩展一致，必要时单独校验。
- 事务一致性：全量导出建议在低峰期执行，或使用 BEGIN; … COMMIT; 包裹自定义脚本，避免半同步状态。
四、验证与常见问题
- 验证
- 结构：对比表、索引、约束、触发器、函数等对象是否一致。
- 数据：对关键表执行 COUNT(*)、抽样校验 SUM/MD5 等，必要时做业务侧回归。
- 常见问题
- 权限不足：为执行用户授予 CREATE/INSERT 等必要权限。
- 版本不兼容：升级/降级函数或语法，或改用与目标版本匹配的备份文件。
- 大文件导入慢：分表/分批导入，或在目标库适度调大 work_mem/maintenance_work_mem 后再导入。
- 网络/超时：增大 statement_timeout、使用 .dump + pg_restore 并行、或在同网段内执行。
五、场景化建议
- 一次性迁移或每周全量：优先用 备份/恢复（SQL 或 .dump），流程简单、可回滚。
- 持续近实时：优先 逻辑复制（表级、灵活）或 流复制（实例级、高可用）。
- 定时批量：用 pgAgent/pg_cron 调度备份/导入脚本，结合保留策略与告警。
- 仅少量表或偶发差异：用 数据比较 + 数据同步 快速对齐。
