PostgreSQL数据库迁移至CentOS步骤

PostgreSQL 数据库迁移至 CentOS 步骤

一 迁移方式总览与选择

• 逻辑迁移：使用 pg_dump/pg_dumpall 导出为 SQL 脚本或自定义归档（-F c），在目标 CentOS 上用 psql/pg_restore 导入。适合跨版本、跨平台、只迁部分库或需要改造结构的场景，操作灵活、兼容性好。
• 物理迁移：直接拷贝 PGDATA 数据目录（同版本、同平台、同架构优先），停机时间短、速度快，适合整库迁移或需要保持 OID/事务一致性的场景。
• 基础备份 + WAL 的 PITR：用 pg_basebackup 做基础备份，配合 WAL 归档恢复到指定时间点，适合需要最小数据丢失或回滚到故障前某一时刻的场景。

二 准备与环境检查

• 版本与兼容性：尽量保持源与目标 PostgreSQL 主版本一致；如跨版本，优先选择逻辑迁移并充分回归测试。
• 目录与权限：规划目标数据目录（如 /data/pgsql/data），确保属主为 postgres:postgres，权限 700。
• 服务与网络：安装同版本 PostgreSQL；开放防火墙端口（默认 5432/TCP）；准备 pg_hba.conf 允许迁移来源访问。
• 工具链：安装 pg_dump/pg_restore/psql（逻辑迁移）与 rsync/tar（物理迁移）；如需跨库（如 MySQL→PostgreSQL）可准备 pgloader。

三 方法一 逻辑迁移步骤（pg_dump/pg_restore）

• 源库导出
  • 单库自定义归档（推荐，便于选择性恢复）：
    pg_dump -U postgres -h 源主机 -p 端口 -F c -b -v -f dbname.dump dbname
  • 单库 SQL 脚本（通用、易审阅）：
    pg_dump -U postgres -h 源主机 -p 端口 -F p -f dbname.sql dbname
  • 全库角色与表空间：
    pg_dumpall -U postgres -f globals.sql
• 传输到目标 CentOS
  • scp dbname.dump root@目标IP:/data/backup/
• 目标库准备
  • 安装同版本 PostgreSQL；如自定义数据目录：
    postgresql-setup --initdb -D /data/pgsql/data
    编辑服务文件设置 Environment=PGDATA=/data/pgsql/data，执行 systemctl daemon-reload
  • 创建目标库：createdb -U postgres dbname
• 目标库导入
  • 自定义归档：pg_restore -U postgres -d dbname -v dbname.dump
  • SQL 脚本：psql -U postgres -d dbname -f dbname.sql
• 验证
  • 校验对象数量、数据量、关键业务查询与约束、触发器/视图执行结果。

四 方法二 物理迁移步骤（同版本整库拷贝）

• 源库停机
  • systemctl stop postgresql（或 postgresql-版本.service）
• 拷贝数据目录
  • rsync -av /var/lib/pgsql/版本/data/ /data/pgsql/data/
  • 或 tar 打包后解压到目标目录
• 目标库权限与目录
  • chown -R postgres:postgres /data/pgsql/data
    chmod 700 /data/pgsql/data
• 调整服务指向
  • 编辑服务文件设置 Environment=PGDATA=/data/pgsql/data，执行 systemctl daemon-reload
• 启动与验证
  • systemctl start postgresql
  • 检查日志、数据库列表、表空间与连接。

五 方法三 基础备份与时间点恢复 PITR（可选）

• 源库准备 WAL 归档
  • postgresql.conf：archive_mode = on
    archive_command = ‘cp %p /backup/pgarch/%f’
• 获取基础备份
  • pg_basebackup -h 源主机 -U 复制用户 -D /backup/base -F p -P -X stream
• 目标库恢复
  • 将 base 目录内容复制到目标 PGDATA；如存在未归档 WAL，先复制到 pg_wal/
  • 在 PGDATA 创建 recovery.signal（PostgreSQL 12+），编辑 postgresql.conf：
    restore_command = ‘cp /backup/pgarch/%f %p’
    recovery_target_timeline = ‘latest’（或指定时间点/事务）
  • 启动服务：systemctl start postgresql；恢复完成后会自动切换到正常模式。

六 常见问题与关键注意事项

• 版本与兼容性：跨大版本不建议物理迁移；逻辑迁移需检查数据类型、索引、函数、扩展兼容性。
• 一致性：物理迁移务必在停机窗口内完成；逻辑迁移建议在低峰期执行并使用一致快照（如事务一致性导出）。
• 权限与所有权：数据目录必须为 postgres:postgres 700；导入前确保目标库角色、表空间存在。
• 表空间与链接：物理迁移需一并迁移 pg_tblspc/ 下的表空间目录与软链接，避免路径错位。
• WAL 与归档：启用 WAL 归档 可实现 PITR；恢复时确保归档完整，必要时设置 restore_command 与时间线。
• 网络与安全：迁移期间限制来源 IP，传输使用加密通道（如 scp/TLS），导入后及时收回不必要权限。