Linux环境下pgAdmin的数据同步方法

Linux环境下 pgAdmin 的数据同步方法

一、方法总览与选择

• 逻辑备份与恢复（推荐用于跨版本迁移、选择性同步）
  • 工具与方式：使用 pg_dump / pg_restore 或在 pgAdmin 中右键数据库执行 Backup/Restore。
  • 适用：跨大版本升级、只同步部分表/模式、对停机时间有要求但可接受一次性导出导入的场景。
• 物理备份与恢复（推荐用于同版本整机级复制/快速回滚）
  • 工具与方式：使用 pg_basebackup 进行基础备份，配合 WAL 归档/流复制实现持续同步；pgAdmin 不直接执行物理恢复，但可用于前期准备与日常运维。
  • 适用：同版本近实时复制、快速故障切换、需要完整一致性镜像的场景。
• 表/查询结果级导入导出（适合小数据量或增量补丁）
  • 工具与方式：在 pgAdmin 中对表使用 Import/Export（CSV），或对查询结果导出后导入；适合补数据、结构相同表的迁移。

二、操作步骤

• 逻辑备份与恢复（pgAdmin 图形界面）
  1. 在左侧 Servers 中连接到源库，右键目标数据库 → Backup…，选择格式（如 Plain 或 Custom），指定文件路径与名称，开始备份。
  2. 在目标环境创建空库（右键 Databases → Create → Database），右键该库 → Restore…，选择上一步的备份文件，开始恢复。
  3. 进度与结果在右下角提示，完成后用查询工具校验数据量与样本数据。
• 逻辑备份与恢复（命令行，便于自动化与脚本化）
  • 导出整库（自定义格式，便于选择性恢复）：
    • pg_dump -h 源主机 -U 用户 -F c -b -v -f dbname.backup dbname
  • 导入整库：
    • createdb -h 目标主机 -U 用户 dbname
    • pg_restore -h 目标主机 -U 用户 -d dbname -v dbname.backup
  • 导出/导入单表或查询结果（CSV）：
    • 导出：psql -h 源主机 -U 用户 -c “COPY 表 TO STDOUT WITH CSV HEADER” dbname > table.csv
    • 导入：psql -h 目标主机 -U 用户 -c “COPY 表 FROM STDIN WITH CSV HEADER” dbname < table.csv
  • 提示：大表建议分批导入，避免长事务与锁竞争。
• 物理备份与恢复（同版本复制/快速回滚）
  1. 在目标主机使用 pg_basebackup 拉取基础备份（示例）：
     • pg_basebackup -h 主库主机 -U 复制用户 -D /var/lib/postgresql/14/main/standby -P -X stream -R
     • 参数说明：-X stream 启用 WAL 流复制；-R 自动写入 primary_conninfo 到 standby 的 recovery.conf（或新版本相应目录）。
  2. 启动备库后，主库继续写入，WAL 会自动流式传输到备库，实现近实时同步。
  3. 故障切换：在备库执行 pg_promote() 或创建触发文件（新版本），提升为主库。
  4. 回滚：将业务切回旧主库或基于基础备份+WAL 重新构建。
• 表/查询结果级导入导出（小数据量或增量补丁）
  1. 在 pgAdmin 中右键表 → Import/Export，选择 CSV，设置分隔符、是否包含 Header、编码等，执行导出/导入。
  2. 若目标表不存在，先建表（注意主键、序列、索引、默认值、约束等），再导入数据。
  3. 大表建议分批、禁用/延后索引与触发器，导入完成后再重建以提高性能。

三、方法对比与适用场景

四、实践建议与常见问题

• 版本与兼容性
  • 跨大版本优先用 逻辑备份/恢复；同版本且需近实时，优先 物理复制。
• 对象一致性
  • 同步前对比 扩展（extensions）、数据类型/域、外键/触发器、默认值/序列，必要时在目标库预创建。
• 性能与锁
  • 大表导入前可 禁用/延后索引与触发器，导入后再重建；分批提交，避免长事务。
• 网络与权限
  • 开放必要端口（如 5432），使用具备足够权限的专用账号；WAL 流复制需配置复制权限与 pg_hba.conf。
• 校验与回滚
  • 同步后校验 行数、主键范围、重要业务校验和；保留最近一次可用备份以便回滚。
• 常见报错与处理
  • 导入 CSV 失败：检查 分隔符、Header、编码、引号 是否与导出一致；必要时先建表再导入。
  • 权限不足：确保用于备份/复制/恢复的账号具备 pg_dump、pg_restore、REPLICATION 等权限。
  • 目标库对象冲突：导入前清理或采用 IF NOT EXISTS / ON CONFLICT 策略。