Ubuntu PostgreSQL数据库迁移技巧

以下是Ubuntu下PostgreSQL数据库迁移的常用技巧及工具，涵盖不同场景：

一、命令行工具迁移

1. pg_dump/pg_restore（推荐，跨版本兼容）

• 备份源数据库

  pg_dump -U 用户名 -h 源主机 -p 端口 -F c -b -v -f /path/backup.dump 数据库名
  

  （-F c为自定义格式，保留权限和依赖关系）

• 恢复到目标数据库

  pg_restore -U 用户名 -h 目标主机 -p 端口 -d 目标数据库 /path/backup.dump
  

  （支持覆盖现有数据，需提前创建目标库结构）

2. 逻辑转储（跨版本/跨平台）

• 导出所有数据库

  pg_dumpall -U postgres -f /path/all_databases.sql
  

• 导入到新版本

  psql -U postgres -f /path/all_databases.sql
  

  （需先安装新版本PostgreSQL，适用于大版本升级）

二、图形化工具迁移

1. pgAdmin

• 导出/导入向导
  1. 连接源/目标数据库，右键选择“备份”或“恢复”。
  2. 选择格式（SQL/CSV/自定义），配置表/数据范围，执行操作。

三、特殊场景工具

1. 零停机迁移（pgsync）

• 实时同步数据（适用于生产环境）
  1. 安装：sudo apt-get install pgsync
  2. 配置pgsync.yml：指定源/目标库、排除表等。
  3. 执行同步：pgsync
     （支持并行传输，避免锁表）

2. 异构数据库迁移（pgloader）

• 从MySQL/Oracle迁移到PostgreSQL
  1. 安装：sudo apt-get install pgloader
  2. 创建配置文件（如mysql_to_pg.load）：

     LOAD DATABASE
     FROM mysql://user:pass@host/db
     INTO postgresql://user:pass@host/db
     

  3. 执行：pgloader mysql_to_pg.load
     （自动处理数据类型转换）

四、关键注意事项

1. 权限与安全

   • 确保目标库用户有足够权限（如CREATE、INSERT）。
   • 迁移敏感数据时使用SSL加密（sslmode=require）。

2. 版本兼容性

   • 大版本升级（如14→15）需先安装新版本，再通过pg_upgrade或pg_dump迁移。
   • 检查扩展（如PostGIS）是否需重新安装。

3. 验证与回滚

   • 迁移后通过pg_checksums验证数据完整性。
   • 保留备份文件，确保可快速回滚。

工具选择建议：

• 小规模迁移：pg_dump/pg_restore或pgAdmin。
• 大规模/零停机：pgsync。
• 异构数据库：pgloader。

参考资料：