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. 测试与回滚计划

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

centos postgresql数据库迁移技巧