Ubuntu环境下PostgreSQL数据迁移的常用方法

1. 使用pg_dump与pg_restore命令行工具（推荐）

这是PostgreSQL官方提供的标准迁移方案，适用于大多数场景（如跨服务器、跨版本迁移），支持自定义格式（保留所有数据库对象完整性）。

操作步骤

• 备份源数据库：在源服务器终端执行以下命令，生成自定义格式（.dump）的备份文件（包含数据库结构、数据及大对象）：

  pg_dump -U postgres -h 源服务器IP -p 5432 -d 源数据库名 -F c -b -v -f /path/to/backup.dump 

  参数说明：-U（用户名）、-h（服务器地址）、-p（端口，默认5432）、-d（数据库名）、-F c（自定义格式）、-b（包含大对象）、-v（详细输出）、-f（输出文件路径）。

• 传输备份文件到目标服务器：使用scp（安全复制）将备份文件从源服务器复制到目标服务器：

  scp /path/to/backup.dump 目标服务器用户名@目标服务器IP:/path/to/destination/ 

• 恢复到目标数据库：在目标服务器终端执行以下命令，将备份文件恢复到指定数据库（需提前创建目标数据库）：

  pg_restore -U postgres -h 目标服务器IP -p 5432 -d 目标数据库名 /path/to/backup.dump 

  参数说明：-d（目标数据库名），其余参数与备份命令一致。

2. 使用pgAdmin图形界面工具

pgAdmin是PostgreSQL的流行管理工具，适合不熟悉命令行的用户，操作直观。

操作步骤

• 备份源数据库：

  1. 打开pgAdmin，连接到源PostgreSQL服务器。
  2. 在左侧导航栏展开“服务器”，右键点击要迁移的数据库，选择“备份”。
  3. 在“备份”对话框中，设置“格式”为“自定义”（或“SQL”），指定备份文件保存路径（如/var/lib/pgadmin/backup.dump），勾选“详细模式”（可选），点击“备份”按钮。

• 传输备份文件：同上述scp命令，将备份文件从源服务器复制到目标服务器。

• 恢复到目标数据库：

  1. 打开pgAdmin，连接到目标PostgreSQL服务器。
  2. 右键点击目标数据库（需提前创建），选择“恢复”。
  3. 在“恢复”对话框中，点击“选择文件”按钮，选择传输过来的备份文件，确认“数据库”名称无误，点击“恢复”按钮。

3. 使用pgloader工具（跨数据库迁移）

若需要从MySQL、Oracle等其他数据库迁移到PostgreSQL，pgloader是高效的选择，支持自动转换数据类型和表结构。

操作步骤

• 安装pgloader：在Ubuntu终端执行以下命令安装：

  sudo apt-get update sudo apt-get install pgloader 

• 配置迁移文件：创建一个.load配置文件（如my_migration.load），内容如下（以MySQL迁移到PostgreSQL为例）：

  LOAD DATABASE FROM mysql://mysql_username:mysql_password@mysql_host/mysql_database INTO postgresql://postgres_user:postgres_password@postgres_host/postgres_database WITH include no drop, create tables, create indexes, reset sequences SET work_mem to '16MB', maintenance_work_mem to '512 MB' ;``` 

• 执行迁移：在终端运行以下命令，启动迁移过程：

  pgloader my_migration.load 

  迁移完成后，pgloader会生成报告，显示迁移的数据量、耗时及错误信息（若有）。

4. 使用COPY命令迁移CSV/TSV数据

若只需迁移表中的数据（而非整个数据库），COPY命令是最快捷的方式，支持从文件直接导入导出。

操作步骤

• 导出数据到CSV文件：在源数据库终端执行以下命令，将指定表的数据导出到CSV文件（如data.csv）：

  psql -U postgres -d 源数据库名 -c "COPY (SELECT * FROM 表名) TO '/path/to/data.csv' WITH CSV HEADER;" 

  参数说明：HEADER表示包含表头（列名）。

• 传输CSV文件到目标服务器：使用scp命令将CSV文件复制到目标服务器。

• 导入数据到目标表：在目标数据库终端执行以下命令，将CSV文件的数据导入到指定表：

  psql -U postgres -d 目标数据库名 -c "COPY 表名 FROM '/path/to/data.csv' WITH CSV HEADER;" 

  注意：目标表的结构需与源表一致（列名、数据类型），否则需提前创建目标表。

5. 使用INSERT语句迁移数据

若数据量较小（如几百条记录），可直接将源数据库中的INSERT语句导出，然后在目标数据库中执行。

操作步骤

• 导出INSERT语句：在源数据库终端执行以下命令，将指定表的INSERT语句导出到SQL文件（如insert.sql）：

  pg_dump -U postgres -d 源数据库名 -t 表名 --data-only --column-inserts -f /path/to/insert.sql 

  参数说明：--data-only（仅导出数据）、--column-inserts（生成带列名的INSERT语句，避免列顺序错误）。

• 传输SQL文件到目标服务器：使用scp命令复制文件。

• 导入INSERT语句：在目标数据库终端执行以下命令，运行SQL文件：

  psql -U postgres -d 目标数据库名 -f /path/to/insert.sql 

迁移注意事项

• 备份数据：迁移前务必对源数据库和目标数据库进行完整备份（如使用pg_dump备份目标数据库），防止数据丢失。
• 权限检查：确保源服务器和目标服务器的PostgreSQL用户具有足够的权限（如BACKUP、RESTORE、SELECT、INSERT）。
• 版本兼容性：跨版本迁移时，建议先查阅PostgreSQL官方文档，确认版本间的兼容性（如PostgreSQL 12迁移到15）。
• 数据类型匹配：若迁移跨数据库（如MySQL到PostgreSQL），需检查数据类型是否兼容（如MySQL的INT对应PostgreSQL的INTEGER）。
• 测试验证：迁移完成后，通过SELECT语句查询目标数据库中的数据，确认完整性（如记录数、字段值是否一致）。