如何通过pg_dump备份更新Postgres表并避免导入重复键错误？

问题结论 #

原生pg_dump没有内置的--column-updates类参数，无法直接导出INSERT ON CONFLICT (column) DO UPDATE SET格式的语句，你可以通过以下几种方案实现无需清空旧表前提下的增量更新：

可行方案 #

方案1：批量修改导出的SQL文件实现UPSERT #

适合两个数据库无法直接连通的场景：

1. 用带--column-inserts参数导出新表数据：

pg_dump --table=table00 --data-only --column-inserts db00 > table00.sql

2. 用文本替换工具批量修改INSERT语句，追加冲突更新逻辑，假设你的表主键为id，包含name、age两个非主键列为例，用sed命令批量替换：

sed 's/^INSERT INTO table00 (id, name, age) VALUES \(.*\);$/INSERT INTO table00 (id, name, age) VALUES \1 ON CONFLICT (id) DO UPDATE SET name=EXCLUDED.name, age=EXCLUDED.age;/' table00.sql > table00_upsert.sql

3. 导入修改后的SQL文件即可：

psql postgresql://<username>:<password>@localhost:5432/db00 --file=table00_upsert.sql

方案2：跨库直接同步（dblink方案） #

适合两个数据库可以直接网络连通的场景，无需导出中间文件：

1. 先在旧库安装dblink扩展：

CREATE EXTENSION IF NOT EXISTS dblink;

2. 直接执行跨库UPSERT语句同步数据：

INSERT INTO table00 (id, name, age)
SELECT * FROM dblink(
    'host=新库IP地址 port=新库端口 dbname=db00 user=新库用户名 password=新库密码',
    'SELECT id, name, age FROM table00'
) AS t(id int, name varchar, age int)
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
age = EXCLUDED.age;

方案3：临时表过渡方案 #

适合表数据量较大的场景，避免网络波动导致同步中断：

1. 在旧库创建和目标表结构一致的临时表（临时表无主键约束，导入不会报重复键）：

CREATE TEMP TABLE temp_table00 AS SELECT * FROM table00 LIMIT 0;

2. 将新表数据直接导入临时表：

pg_dump --table=table00 --data-only --column-inserts db00 | sed 's/table00/temp_table00/g' | psql postgresql://<username>:<password>@localhost:5432/db00

3. 用临时表数据更新目标旧表：

INSERT INTO table00 SELECT * FROM temp_table00
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
age = EXCLUDED.age;

注意事项 #

• 执行更新前请务必确认ON CONFLICT后面指定的列必须是主键或者有唯一约束，否则语句会报错
• 操作前建议先备份旧表数据，避免误操作导致数据丢失
• 大数据量同步建议选择业务低峰期操作，避免锁表影响正常业务

内容的提问来源于stack exchange，提问作者étale-cohomology