CentOS环境下PostgreSQL数据库迁移实用技巧

一、常见迁移方法及步骤

1. 逻辑备份与还原（pg_dump/pg_restore）

逻辑备份是最基础的迁移方式，适用于跨版本、跨平台或需要灵活调整数据的场景。

• 单数据库迁移：
  在源服务器上使用pg_dump导出数据库（-Fc格式支持压缩和并行，提升效率）：

  pg_dump -U postgres -Fc db_name -f db_name.dump
  

  将db_name.dump传输到目标服务器（如用scp），然后使用pg_restore导入：

  pg_restore -U postgres -d new_db_name db_name.dump
  

• 全集群迁移：
  若需迁移所有数据库（包括角色、表空间等），使用pg_dumpall：

  pg_dumpall -U postgres -f all.dump
  

  传输到目标服务器后，用psql恢复：

  psql -U postgres -f all.dump
  

• 注意事项：
  • 导出前停止应用程序或锁定数据库，确保数据一致性；
  • 新版本pg_dump可兼容旧版本数据库，建议用新版本工具导出。

2. 物理迁移数据目录

物理迁移直接复制数据库文件，速度快，适用于同版本、同架构的迁移（如CentOS系统内升级PostgreSQL版本）。

• 步骤：
  ① 停止旧服务：systemctl stop postgresql；
  ② 复制数据目录（默认路径/var/lib/pgsql/data，可通过postgresql.conf确认）：

  cp -rp /var/lib/pgsql/data/* /new/data/directory/
  

  ③ 修改权限：确保新目录属主为postgres，权限为700：

  chown -R postgres:postgres /new/data/directory/
  chmod -R 700 /new/data/directory/
  

  ④ 修改配置：编辑/etc/postgresql/[版本]/main/postgresql.conf，将data_directory指向新路径；
  ⑤ 重启服务：systemctl start postgresql。
• 注意事项：
  • 必须停止旧服务，避免数据损坏；
  • 新旧环境PostgreSQL版本需一致，否则文件格式不兼容。

3. 使用pgloader工具

pgloader支持从MySQL、SQLite、Oracle等多种数据库迁移到PostgreSQL，也适用于PostgreSQL跨版本迁移，具备自动类型转换、并行处理等功能。

• 步骤：
  ① 安装pgloader：yum install -y pgloader；
  ② 创建配置文件（如migrate.load）：

  LOAD DATABASE
      FROM mysql://user:password@source_host/source_db
      INTO postgresql://user:password@target_host/target_db
      WITH include no drop, create tables, create indexes, reset sequences
  

  ③ 执行迁移：pgloader migrate.load。
• 优势：无需手动编写SQL，自动化处理数据类型映射，减少人为错误。

4. 第三方工具（如CloudCanal）

CloudCanal等可视化工具支持PostgreSQL与其他数据库（如Doris、MySQL）的双向同步，适合需要持续数据同步的场景。

• 步骤：
  ① 登录CloudCanal控制台，创建PostgreSQL数据源；
  ② 配置源和目标数据库连接信息；
  ③ 创建迁移链路（选择“全量+增量”模式），启动同步任务。
• 优势：图形化操作，支持实时同步，降低运维成本。

二、迁移注意事项

1. 数据一致性保障

• 迁移前停止应用程序或设置数据库只读（修改pg_hba.conf，禁止写入）；
• 使用事务包裹恢复操作（如psql -f backup.sql时添加-1参数），避免部分失败导致数据不一致。

2. 权限与路径检查

• 确保目标服务器上PostgreSQL用户（通常为postgres）对数据目录有读写权限；
• 迁移后检查角色权限（如GRANT语句是否生效）、表空间路径（pg_tablespace表）是否正确。

3. 版本兼容性处理

• 跨主要版本（如12→13）迁移时，优先使用pg_upgrade（就地升级，停机时间短）或逻辑备份还原（兼容性好）；
• 升级前运行pg_upgrade -c检查兼容性问题（如不支持的扩展、数据类型）；
• 参考PostgreSQL官方发行说明，处理新增/废弃的功能（如pg_stat_statements扩展的变更）。

4. 测试与回滚计划

• 在测试环境模拟迁移流程，验证数据完整性（如对比源和目标数据库的记录数、业务查询结果）；
• 生产环境迁移前，备份所有数据（包括配置文件、扩展脚本）；
• 准备回滚方案（如保留旧数据目录、备份文件），若迁移失败可快速恢复。