如何用pg_dump和pg_restore迁移Postgres指定更新表至生产服务器

针对你的需求——将生产库中指定表的数据替换为开发库对应表的数据，同时保留其他表的动态数据，以下是PostgreSQL 15.4版本下的可行步骤：

1. 从开发库导出目标表备份 #

仅导出需要更新的表（假设目标表为table_a, table_b, table_c），采用自定义格式备份以获得后续恢复的灵活性：

pg_dump -h <开发服务器地址> -U <开发库用户名> -d <开发库名> \
  -t table_a -t table_b -t table_c \
  -F c -f /tmp/dev_target_tables.dump

参数说明：

• -t：指定要导出的表，可多次使用导出多个表
• -F c：生成自定义格式备份文件，支持后续pg_restore的精细操作
• -f：指定备份文件路径

2. 备份生产库目标表（关键前置操作） #

操作前务必备份生产库的目标表，防止数据丢失：

pg_dump -h <生产服务器地址> -U <生产库用户名> -d <生产库名> \
  -t table_a -t table_b -t table_c \
  -F c -f /tmp/prod_target_tables_backup.dump

3. 预处理生产库目标表 #

需要清空目标表数据并临时禁用约束/触发器，避免恢复时出现冲突：

1. 禁用目标表的所有触发器（含外键约束触发器）：

ALTER TABLE table_a DISABLE TRIGGER ALL;
ALTER TABLE table_b DISABLE TRIGGER ALL;
ALTER TABLE table_c DISABLE TRIGGER ALL;

2. 清空目标表数据（若表有子表关联，添加CASCADE级联清空）：

TRUNCATE TABLE table_a CASCADE;
TRUNCATE TABLE table_b CASCADE;
TRUNCATE TABLE table_c CASCADE;

注：若需保留表结构仅替换数据，请勿使用DROP TABLE

4. 将开发库表数据恢复到生产库 #

仅恢复数据（不覆盖表结构），并保持触发器禁用状态避免冲突：

pg_restore -h <生产服务器地址> -U <生产库用户名> -d <生产库名> \
  -t table_a -t table_b -t table_c \
  --data-only --disable-triggers \
  /tmp/dev_target_tables.dump

参数说明：

• --data-only：仅恢复数据，不修改表结构
• --disable-triggers：恢复过程中保持触发器禁用，避免触发约束检查

5. 恢复生产库目标表的约束与触发器 #

数据恢复完成后，重新启用所有触发器：

ALTER TABLE table_a ENABLE TRIGGER ALL;
ALTER TABLE table_b ENABLE TRIGGER ALL;
ALTER TABLE table_c ENABLE TRIGGER ALL;

6. 同步自增序列（可选） #

若目标表使用了自增主键（serial/identity类型），需同步序列值至当前最大主键值，避免后续插入主键冲突：

SELECT setval('table_a_id_seq', (SELECT MAX(id) FROM table_a));
SELECT setval('table_b_id_seq', (SELECT MAX(id) FROM table_b));
SELECT setval('table_c_id_seq', (SELECT MAX(id) FROM table_c));

注：将table_a_id_seq替换为对应表的实际序列名，可通过\d table_a在psql中查看

额外注意事项 #

• 尽量在业务低峰期执行操作，减少对生产业务的影响
• 若开发库与生产库的目标表结构存在差异（如新增字段、修改字段类型），需先同步表结构：
  1. 导出开发库目标表的结构：

     pg_dump -h <开发服务器地址> -U <开发库用户名> -d <开发库名> \
       -t table_a -t table_b -t table_c --schema-only > /tmp/dev_target_tables_schema.sql
     

  2. 在生产库中执行结构变更脚本（执行前需确认变更兼容现有数据）

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