centos pgadmin数据迁移方案

CentOS 下使用 pgAdmin 的 PostgreSQL 数据迁移方案

一 方案选型与适用场景

• 逻辑导出导入（pg_dump / pg_restore）：适合大多数一次性迁移、跨版本/跨平台、选择性对象迁移；可用 自定义归档格式 -F c 提升恢复灵活性与速度，配合 -j N 并行导入。
• 物理备份还原（pg_basebackup）：适合同版本、同平台的大规模库快速迁移或搭建备库；需停库或用备库承接流量，恢复时整库替换数据目录。
• 复制级迁移（流复制/逻辑复制/DRS）：适合对停机时间敏感的业务；流复制/逻辑复制可实现近零停机切换；云上优先用 DRS 实时同步，支持一次性或持续同步、停机时间最低。

二 方案一 逻辑导出导入（pg_dump / pg_restore，pgAdmin 辅助）

• 适用：从 CentOS 自建 PostgreSQL 迁至 云 RDS/自建库，或跨云迁移；支持选择性迁移（库/模式/表）。
• 步骤（命令行执行，pgAdmin 可用于连接与校验）：
  1. 源库导出（自定义归档格式，便于并行恢复）
     pg_dump -h 源主机 -p 端口 -U 用户 -F c -j 4 -f source.dump 源库名
  2. 传输到目标（若跨云无公网，可经中转机 SFTP 分段传输）
     sftp 中转机
     put source.dump /path/
     get /path/source.dump 目标机:/path/
  3. 目标库准备
     createdb -h 目标主机 -p 端口 -U 用户 目标库名
  4. 目标库恢复（需要时加 -c 先清空目标对象）
     pg_restore -h 目标主机 -p 端口 -U 用户 -d 目标库名 -j 4 source.dump
  5. 校验
     psql -h 目标主机 -p 端口 -U 用户 -c “SELECT COUNT(*) FROM 重点表” 目标库名
• 说明：自定义归档格式与并行恢复可显著缩短大库导入时间；跨云无直连时，用中转机 SFTP 分段搬运备份文件是通用做法。

三 方案二 物理备份还原（pg_basebackup，适合同版本整库迁移）

• 适用：同版本、同平台的整库迁移或快速搭建备库；追求速度与一致性。
• 步骤：
  1. 源库做基础备份（含所需 WAL）
     pg_basebackup -h 源主机 -p 端口 -U 复制用户 -D /backup/base -Fp -Xs -P
  2. 传输备份到目标（可用 scp/sftp）
  3. 目标库停止并清空数据目录
     systemctl stop postgresql-<版本>
     rm -rf /var/lib/pgsql/<版本>/data/*
  4. 解压到数据目录并启动
     tar -xvf /backup/base/base.tar -C /var/lib/pgsql/<版本>/data/
     systemctl start postgresql-<版本>
• 说明：物理方式恢复快、一致性好；如需跨小版本升级，优先考虑逻辑迁移或升级后再迁移。

四 方案三 复制级迁移（流复制/逻辑复制/DRS，低停机）

• 流复制搭建备库后切换（近零停机）：
  1. 源库参数（postgresql.conf）
     wal_level = replica
     max_wal_senders = 5
     wal_keep_segments = 32（或设置 WAL 归档）
  2. 创建复制用户并授权（pg_hba.conf 放行复制网段）
     CREATE ROLE repl LOGIN REPLICATION ENCRYPTED PASSWORD ‘pwd’;
  3. 目标库用 pg_basebackup 拉取基础备份（见方案二步骤 1），自动生成 recovery 配置
  4. 启动备库后验证复制状态
     SELECT * FROM pg_stat_replication;
  5. 切换窗口短暂停写，提升备库为主并切流量（配合 VIP/应用 DNS 切换）。
• 逻辑复制（跨版本/跨平台/部分表迁移）：
  • 要求表具备 主键/唯一约束；只同步 DML，DDL 需单独处理；适合分批迁移与持续同步。
• 云上 DRS 实时同步（最低停机）：
  • 适合 自建/他云/其他引擎 → 云 RDS for PostgreSQL 的迁移或持续同步；支持一次性迁移与实时同步两种模式，操作便捷、上线快。

五 迁移前后关键检查与最佳实践

• 版本与兼容性：确认 源/目标 PostgreSQL 主版本 差异；跨大版本优先考虑逻辑迁移或先升级再迁移。
• 对象一致性：迁移后核对 扩展（extension）、角色/权限、视图/函数/触发器、外键/索引、序列值 等；必要时用 pg_dumpall -s 迁移全局对象结构。
• 性能与并行：大库导出/导入使用 -F c 与 -j N 提升速度；导入前临时调大 work_mem/maintenance_work_mem（完成后恢复）。
• 网络与传输：跨云无公网时，使用 中转机 SFTP 分段传输；全程使用 SSL/TLS 或加密通道。
• 一致性与回滚：导出前对关键表做 COUNT/校验和 快照；准备回滚脚本/备份；切换前短暂停写并再次校验。
• 云上优先选项：若目标为云 RDS for PostgreSQL，优先评估 DRS 实时同步 以压低停机时间并简化运维。