CentOS PostgreSQL迁移指南

一、迁移前准备

1. 备份数据：迁移前必须对原数据库进行完整备份，防止数据丢失。常用工具为pg_dump（逻辑备份）或pg_basebackup（物理备份）。例如，备份单个数据库可使用pg_dump -U postgres -h 源主机 -p 端口 数据库名 > backup.sql；备份所有数据库及配置可使用pg_dumpall > all_backup.sql。
2. 检查版本兼容性：确保目标CentOS服务器上的PostgreSQL版本与源服务器相同或更高（如源为15.x，目标可为15.x或16.x），避免因版本差异导致功能异常。
3. 准备目标环境：在目标服务器上安装PostgreSQL（版本符合要求），并创建与原数据库相同的用户（如postgres），确保用户有足够的权限操作数据库。

二、常见迁移方法

方法1：使用pg_dump/pg_restore（逻辑迁移，推荐小规模/跨版本迁移）

适用场景：需要迁移单个/多个数据库、跨PostgreSQL版本（如14→16）、或需要调整数据结构时。
操作步骤：

• 导出数据：在源服务器上执行pg_dump -U postgres -h 源主机 -p 端口 数据库名 -Fc -f backup.dump（-Fc为自定义格式，支持压缩和并行恢复）。
• 传输备份文件：使用scp backup.dump 目标用户@目标主机:/path/to/destination/将备份文件复制到目标服务器。
• 恢复数据：在目标服务器上创建目标数据库（createdb -U postgres 目标数据库名），然后执行pg_restore -U postgres -h 目标主机 -p 端口 -d 目标数据库名 -c backup.dump（-c表示清空目标数据库，避免冲突）。
• 验证数据：使用psql -U postgres -d 目标数据库名 -c "SELECT 1"测试连接，或通过psql客户端查看数据完整性。

方法2：物理迁移数据目录（快速迁移，推荐同版本/大规模数据）

适用场景：同版本PostgreSQL迁移、大规模数据（如TB级）、需要最小化停机时间时。
操作步骤：

• 停止源数据库：在源服务器上执行systemctl stop postgresql，确保数据不再变化。
• 复制数据目录：源服务器默认数据目录为/var/lib/pgsql/data（可通过psql -U postgres -c "SHOW data_directory;"确认），使用rsync -avz /var/lib/pgsql/data/ 目标用户@目标主机:/path/to/new/data/复制数据（-a保留权限，-v显示进度，-z压缩传输）。
• 修改权限：在目标服务器上执行chown -R postgres:postgres /path/to/new/data/（确保属主为postgres），chmod -R 700 /path/to/new/data/（目录权限设为700，保证安全）。
• 修改配置文件：编辑目标服务器的PostgreSQL配置文件（/etc/postgresql/[版本]/main/postgresql.conf），将data_directory参数修改为新路径（如data_directory = '/path/to/new/data'）；若需允许远程连接，还需修改listen_addresses = '*'（允许所有IP连接）。
• 启动目标数据库：执行systemctl daemon-reload（重新加载systemd配置），systemctl start postgresql（启动服务），并通过psql -U postgres -d postgres -c "SELECT 1"验证服务是否正常。

方法3：使用pgloader（异构数据库迁移，如MySQL→PostgreSQL）

适用场景：需要从MySQL、Oracle等其他数据库迁移到PostgreSQL时。
操作步骤：

• 安装pgloader：在目标服务器上执行yum install -y pgloader（CentOS 7/8）或dnf install -y pgloader（CentOS Stream）。
• 配置迁移文件：创建migrate.load文件，内容示例：LOAD DATABASE FROM mysql://mysql_user:mysql_password@mysql_host/mysql_db INTO postgresql://postgres_user:postgres_password@postgres_host/postgres_db（替换为实际用户名、密码、主机和数据库名）。
• 执行迁移：运行pgloader migrate.load，pgloader会自动处理表结构转换、数据同步和冲突解决。

三、迁移后配置

1. 调整网络设置：若目标数据库需要远程访问，需修改postgresql.conf中的listen_addresses（设为*或特定IP），并编辑pg_hba.conf文件添加允许连接的客户端IP（如host all all 0.0.0.0/0 md5，允许所有IP用密码连接）。
2. 重启服务生效：执行systemctl restart postgresql使配置变更生效。
3. 验证连接：在目标服务器或其他客户端使用psql -U postgres -h 目标主机 -p 端口 目标数据库名连接，确认能正常访问数据。

四、注意事项

1. 数据一致性：迁移前停止源数据库的写入操作（如设置应用层只读），或在备份时使用pg_dump的--snapshot选项（确保备份瞬间数据一致）。
2. 权限保留：物理迁移时，确保目标数据目录的属主和权限与源服务器一致（postgres:postgres，700）；逻辑迁移时，pg_dumpall会自动备份角色和权限信息。
3. 停机时间规划：物理迁移需停机（停止源数据库），建议在业务低峰期操作；逻辑迁移可实现零停机（源数据库继续运行），但会增加源数据库负载。
4. 测试验证：迁移后在目标环境执行典型查询（如SELECT COUNT(*) FROM 大表），确认数据量和查询性能符合预期。