PostgreSQL跨远程库迁移表：目标Schema不一致的解决方案

一、修改目标Schema的具体方法 #

1. 先在目标库创建缺失的Schema #

如果只是目标库缺少源库对应的Schema，直接在目标库执行：

CREATE SCHEMA IF NOT EXISTS <源库缺失的Schema名称>;

执行完成后再运行原有的导入命令即可。

2. 调整dump文件中的Schema映射（适用于需要变更Schema名称的场景） #

如果要将源库old_schema的表迁移到目标库new_schema，可以通过文本替换修改dump文件：

# Linux/macOS下替换Schema引用
sed -i 's/old_schema\./new_schema\./g' database_dump.sql

注意：如果数据中包含old_schema.字符串会被误替换，建议先导出结构文件修改，再单独导数据。

也可以分两步导出结构和数据：

• 导出源库结构：

  pg_dump -h <source_db_host> -U <source_db_user> -d <source_db_name> --schema-only -f schema_dump.sql
  

• 修改schema_dump.sql中的Schema名称后导入目标库
• 导出源库数据：

  pg_dump -h <source_db_host> -U <source_db_user> -d <source_db_name> --data-only -a -f data_dump.sql
  

• 替换数据文件中的Schema引用（如果有）后导入目标库

3. 用pg_restore指定目标Schema（适用于自定义格式dump） #

先以自定义格式导出源库数据：

pg_dump -h <source_db_host> -U <source_db_user> -d <source_db_name> -Fc -f database_dump.dmp

然后用pg_restore直接映射Schema：

pg_restore -h <target_db_host> -U <target_db_user> -d <target_db_name> --schema=<源Schema名> --target-schema=<目标Schema名> database_dump.dmp

需确保目标库已提前创建<目标Schema名>

二、更简便的跨库迁移方案 #

1. 使用dblink直接跨库同步数据 #

无需生成中间文件，直接在目标库通过dblink连接源库拉取数据：

• 先在目标库安装dblink扩展：

  CREATE EXTENSION IF NOT EXISTS dblink;
  

• 执行数据迁移（示例：将源库old_schema.source_table的数据同步到目标库new_schema.target_table）：

  INSERT INTO new_schema.target_table
  SELECT * FROM dblink(
    'host=<source_db_host> user=<source_db_user> dbname=<source_db_name> password=<source_db_pwd>',
    'SELECT * FROM old_schema.source_table'
  ) AS t(col1 INT, col2 VARCHAR(50), col3 DATE); -- 需与源表字段类型一一对应
  

这种方式适合小到中等规模的数据表，能灵活处理字段映射和Schema转换。

2. 批量迁移指定数据表 #

如果只需要迁移部分表，pg_dump可以直接指定表：

pg_dump -h <source_db_host> -U <source_db_user> -d <source_db_name> -t old_schema.table1 -t old_schema.table2 -f tables_dump.sql

再通过替换Schema或指定pg_restore参数导入目标库，减少不必要的数据处理。

内容的提问来源于stack exchange，提问作者YIF99