如何在不破坏外键引用的情况下转储/恢复Postgres的部分表？

针对你需要将staging环境中管理员维护的表同步到生产环境（保留用户表外键完整性）的需求，以下是几种实用的原生解决方案：

核心思路 #

通过临时表中转+Upsert语句实现数据同步，既避免直接删除生产表触发外键错误，又能完成新增/更新操作。

方案一：基于COPY+临时表的高效同步（推荐） #

适合数据量稍大的场景，操作更可靠：

1. 备份生产环境目标表（必做）

# 单个表备份
pg_dump -h prod-host -U prod-user -d prod-db --table=ingredients > prod_ingredients_backup.sql

# 批量备份管理员表
admin_tables=(ingredients categories units)
for table in "${admin_tables[@]}"; do
  pg_dump -h prod-host -U prod-user -d prod-db --table="$table" > "prod_${table}_backup.sql"
done

2. 从staging导出目标表数据（自定义格式）

for table in "${admin_tables[@]}"; do
  pg_dump -h staging-host -U staging-user -d staging-db --data-only --format=custom --table="$table" > "${table}_staging.dump"
done

3. 在生产环境创建临时表并导入数据

-- 单个表示例
CREATE TEMP TABLE temp_ingredients AS SELECT * FROM ingredients LIMIT 0; -- 复制表结构，不复制数据

# 导入staging数据到临时表
pg_restore -h prod-host -U prod-user -d prod-db --data-only --table=temp_ingredients ingredients_staging.dump

4. 执行Upsert同步数据
   根据表的主键编写Upsert语句，例如ingredients表主键为id：

BEGIN; -- 开启事务，出错可回滚

INSERT INTO ingredients (id, name, description, stock)
SELECT id, name, description, stock FROM temp_ingredients
ON CONFLICT (id) DO UPDATE SET
  name = EXCLUDED.name,
  description = EXCLUDED.description,
  stock = EXCLUDED.stock;

COMMIT; -- 验证无误后提交

方案二：修改pg_dump生成的INSERT语句为Upsert #

适合数据量小、需要快速调整的场景：

1. 导出staging表数据为列插入模式

pg_dump -h staging-host -U staging-user -d staging-db --data-only --column-inserts --table=ingredients > ingredients_raw.sql

2. 手动/脚本替换为Upsert语句
   将生成的INSERT INTO ... VALUES (...);替换为带冲突处理的语句：

-- 原语句
INSERT INTO ingredients (id, name, description) VALUES (1, 'Salt', 'Table salt');

-- 修改后Upsert语句
INSERT INTO ingredients (id, name, description) VALUES (1, 'Salt', 'Fine table salt')
ON CONFLICT (id) DO UPDATE SET
  name = EXCLUDED.name,
  description = EXCLUDED.description;

如果表数量多，可通过shell脚本批量替换（需先获取每个表的主键）：

# 获取表的主键列
get_pk() {
  psql -h prod-host -U prod-user -d prod-db -t -c "
    SELECT column_name 
    FROM information_schema.key_column_usage 
    WHERE table_name = '$1' AND constraint_name LIKE '%_pkey'
  " | xargs
}

for table in "${admin_tables[@]}"; do
  pk=$(get_pk "$table")
  # 替换INSERT为Upsert（仅适用于简单列结构）
  sed -i.bak "s/INSERT INTO $table \(.*\) VALUES (/INSERT INTO $table \1 VALUES (/g" "${table}_raw.sql"
  sed -i.bak "s/);$/ ON CONFLICT ($pk) DO UPDATE SET $(echo "\1" | sed 's/, / = EXCLUDED., /g') = EXCLUDED.\1;/" "${table}_raw.sql"
done

3. 在生产环境执行修改后的SQL

psql -h prod-host -U prod-user -d prod-db -f ingredients_raw.sql

注意事项 #

• 删除行处理：如果staging中有删除的行，直接同步会导致生产环境用户表的外键关联出错。建议通过标记is_deleted字段软删除，或提前清理生产环境中关联的用户数据（需业务确认）。
• 事务包裹：所有同步操作务必放在事务中，避免部分同步导致数据不一致。
• 权限控制：确保执行操作的数据库用户拥有目标表的INSERT、UPDATE权限。

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