DebianPostgreSQL数据库迁移方案有哪些

Debian 下 PostgreSQL 数据库迁移方案

一、方案总览与选型

二、方案详解

• 通过pg_dump/pg_restore以及pg_dumpall实现逻辑的导出与导入

  • 单库/多表迁移：使用 pg_dump -F c -b -v -f backup.dump db 生成自定义格式（含大对象），在目标端用 pg_restore -d target_db -v backup.dump 恢复；文本格式可用 psql -f backup.sql db 导入。适合跨版本与跨平台迁移。
  • 全集簇迁移：用 pg_dumpall > globals.sql 导出角色/表空间/全局对象，目标端先执行 psql 创建全局对象，再导入各库。适合整机迁移或重建环境。
  • 大对象与权限：自定义格式可保留大对象（-b），恢复前确保目标端已存在对应角色与权限，否则属主/权限可能无法完全保留。恢复后可执行 ANALYZE 更新统计信息。

• 物理迁移（如文件系统拷贝、pg_basebackup、WAL PITR等方式）

  • 冷备份迁移：干净停库（如 pg_ctl stop -m fast），打包 $PGDATA 到新机器，解压到目标 $PGDATA，修正权限（属主 postgres:postgres、权限 0700），启动服务并验证。适合同版本同平台快速整机迁移。
  • 基础备份与时间点恢复：用 pg_basebackup -h src -D /pgbak/data -Ft -z -P 拉取基础备份；若启用 WAL 归档（archive_command），可结合备份与归档日志将数据库恢复到任意时间点（PITR），满足故障恢复与近零数据丢失需求。
  • 版本/平台注意：物理方式对 PostgreSQL 主版本、操作系统、CPU 架构较敏感，跨版本直接恢复成功率不高，通常优先用于同版本迁移或配合升级流程。

• 复制/迁移工具（流复制、pgloader）

  • 流复制/备用库：源端设置 wal_level=replica、max_wal_senders、wal_keep_segments，创建复制用户；目标端作为 hot_standby 接收流复制，验证一致后短暂停写切换。适合零/低停机升级或迁移。
  • 异构迁移到 MySQL/GreatSQL：使用 pgloader 将 PostgreSQL 数据加载到 MySQL/GreatSQL，可在配置文件中指定创建表/索引、重置序列、批量加载参数等，简化类型与语法差异处理。适合一次性或持续同步到 MySQL 生态。

三、迁移前后关键检查清单

• 版本与兼容性：确认源/目标 PostgreSQL 主版本与扩展插件；跨版本优先考虑逻辑迁移或升级后迁移。
• 对象与权限：角色、表空间、默认权限、所有者、FDW/扩展对象需提前在目标端准备；逻辑导入时若角色不存在，属主/权限可能无法保留。
• 大对象与特殊类型：确认 LO、JSON/JSONB、hstore、几何类型等迁移完整性；自定义格式（-F c -b）更可靠。
• 统计信息与性能：导入后执行 ANALYZE 更新统计信息；按需执行 VACUUM FULL 或重建索引。
• 配置与网络：核对 postgresql.conf（listen_addresses、port） 与 pg_hba.conf（认证方式、网段）；迁移窗口内控制连接与写入，避免增量干扰。
• 验证与回滚：准备回滚预案与验证脚本（行数、校验和、关键业务查询、应用冒烟测试），在切换前完成演练。

四、常用命令速查

• 逻辑导出导入
  • 单库自定义格式备份与恢复：
    • pg_dump -U postgres -d mydb -F c -b -v -f mydb.dump
    • pg_restore -U postgres -d mydb -v mydb.dump
  • 全集簇导出与导入：
    • pg_dumpall -U postgres -f globals.sql
    • psql -U postgres -f globals.sql
    • 各库分别用 pg_dump/pg_restore 或 psql 导入
• 物理迁移
  • 冷备份与恢复：
    • 停库：pg_ctl stop -m fast
    • 打包：tar czf pgdata.tar.gz -C /var/lib/postgresql/15/main .
    • 解压与授权：tar xzf pgdata.tar.gz -C /var/lib/postgresql/15/main；chown -R postgres:postgres/var/lib/postgresql/15/main
    • 启动：systemctl start postgresql
  • 基础备份与 PITR：
    • pg_basebackup -h src_host -D /pgbak/base -Ft -z -P
    • 配置 recovery.conf（或 postgresql.auto.conf）指向 restore_command 与 recovery_target_time，启动至目标时间点
• 复制/迁移工具
  • pgloader（PostgreSQL → MySQL/GreatSQL）示例：
    • 保存为 load.load：
      • LOAD DATABASE
        • FROM postgresql://u:p@src/db
        • INTO mysql://u:p@dst/db
        • WITH data only, include no drop, create tables, create indexes, reset sequences
      • 执行：pgloader load.load以上命令为常见用法示例，实际需结合你的目录、版本与网络环境调整。