PostgreSQL在CentOS中的数据迁移策略

PostgreSQL 在 CentOS 的数据迁移策略

一、策略总览与选型

• 逻辑导出导入：使用 pg_dump/pg_restore/pg_dumpall 做全量迁移，适合跨小版本/跨大版本、跨服务器/跨平台、变更库名或调整编码/表空间等场景。优点是兼容性强、可选择性迁移；停机时间取决于导出与导入窗口。
• 物理迁移数据目录：在同一主版本内，直接拷贝 PGDATA 到新磁盘/新路径，快速且一致性强；跨主版本通常不可用。
• 复制级迁移：
  • 逻辑复制（内置或扩展）：适合跨大版本、跨集群的近零停机升级/迁移，支持表级复制，订阅端可读写；但 DDL 不同步，需手工对齐。
  • 物理复制/流复制：要求主备版本一致，主要用于高可用，不作为跨版本升级手段。
• 异构迁移：从 MySQL/SQL Server/Oracle 等迁入 PostgreSQL，可用 pgloader 或 ora2pg 等工具。
• 版本原则：跨主版本升级不建议直接替换数据目录，应使用 新版本 pg_dump 导出并在新版本实例中恢复；同主版本补丁内可考虑物理拷贝。

二、常用方法步骤要点

• 逻辑导出导入（单库/全库）

  • 单库（自定义格式，便于选择性恢复与并行导入）：
    • 导出：pg_dump -U postgres -Fc -f dbname.dump dbname
    • 导入：pg_restore -U postgres -d dbname dbname.dump
  • 全库（含角色、表空间、全局权限）：
    • 导出：pg_dumpall -U postgres -f all.dump
    • 导入：psql -U postgres -f all.dump（或按库分批导入）
  • 适用：跨版本、跨平台、结构调整；停机≈导出+导入时间。

• 物理迁移数据目录（同主版本）

  • 停止服务：systemctl stop postgresql（或 postgresql-）
  • 拷贝数据：cp -rp /var/lib/pgsql/data /new/data/dir
  • 权限：chown -R postgres:postgres /new/data/dir && chmod 700 /new/data/dir
  • 修改服务环境变量：编辑服务文件（如 /usr/lib/systemd/system/postgresql.service 或 /usr/lib/systemd/system/postgresql-.service），设置 Environment=PGDATA=/new/data/dir
  • 使配置生效并启动：systemctl daemon-reload && systemctl start postgresql
  • 适用：同主版本磁盘/路径迁移，速度快、一致性高。

• 逻辑复制迁移（跨大版本近零停机）

  • 发布端（旧库，≥10 可用内置逻辑复制；更早版本可用 pglogical）：
    • 配置：wal_level = logical，max_replication_slots 充足
    • 建发布：CREATE PUBLICATION mig_pub FOR ALL TABLES;
    • 无主键表：ALTER TABLE t REPLICA IDENTITY FULL;
  • 订阅端（新库，目标版本）：
    • 先建同名同构表（可用 pg_dump -s 导出结构）
    • 建订阅：CREATE SUBSCRIPTION mig_sub CONNECTION 'host=old_host ...' PUBLICATION mig_pub;
  • 切换与回滚：数据追平后，通过 VIP/HAProxy 切换应用；异常可快速回切旧库。
  • 注意：DDL 需手工同步，大事务需拆分，复制冲突需监控与处理。

• 异构迁移（MySQL/SQL Server/Oracle → PostgreSQL）

  • pgloader：pgloader mysql://user:pass@host/db postgresql://user:pass@host/db（支持类型映射、并发加载、转换规则）。
  • ora2pg：从 Oracle 迁移到 PostgreSQL 的成熟方案（DDL/数据/序列/索引等）。
  • 适用：数据库平台更换或整合。

三、迁移前后检查清单

• 版本与兼容性：跨主版本优先用 逻辑导出导入；同主版本才考虑物理拷贝。
• 一致性：迁移窗口内避免写入或采用复制保持增量同步；导入后做 行数与关键业务校验。
• 权限与安全：确保 postgres 用户与 700 权限；核对 pg_hba.conf 访问规则与防火墙放行。
• 参数与路径：服务文件 Environment=PGDATA 指向新目录；必要时调整 listen_addresses、port。
• 网络与带宽：大数据量迁移保证稳定网络与合理并发；必要时压缩/分批。
• 回滚预案：保留旧库只读一段时间；准备 反向复制 或快速回切策略。

四、场景化推荐

• 同主版本、仅换磁盘/路径：优先 物理迁移数据目录（停库短、一致性强）。
• 跨小版本或跨服务器/平台：使用 pg_dump/pg_restore 做逻辑迁移（兼容性好、步骤清晰）。
• 跨大版本、要求近零停机：采用 逻辑复制（发布/订阅），DDL 手工对齐，配合 VIP/HAProxy 平滑切换。
• 异构数据库迁入：选择 pgloader（MySQL/SQL Server 等）或 ora2pg（Oracle）。