Postgres数据库百万级数据跨库迁移方案及重复键报错问题求解

你遇到的「报错提示主键重复但查询不到对应记录」的核心原因：

• 你使用的批量插入是单事务提交单批次，只要批次内存在任意一条主键冲突记录，整个批次的所有插入操作都会触发事务回滚，因此你在目标库中查询不到部分插入的记录，自然找不到重复值。
• 导致批次内主键重复的常见诱因：
  • 从源库查询数据时未做排序+去重，存在重复id的脏数据
  • 插入时直接复用了源库的id字段值，若目标库之前有过写入操作，自增序列生成的id会和你手动插入的id重叠
  • 数据处理过程中存在逻辑错误，同一条记录被多次加入待插入队列

• 插入语句改用INSERT ... ON CONFLICT (id) DO NOTHING，直接跳过冲突的记录，不会触发整个批次回滚，单次运行即可完成所有有效数据插入，不需要重复跑脚本。
• 如果需要更新冲突记录，也可以调整为ON CONFLICT (id) DO UPDATE语法，按需更新对应字段。
• 提前对待插入的数据集按id做去重处理，避免批次内出现重复记录。
• 若你不需要保留源库的id值，插入时直接忽略id字段，让目标库的自增序列自动生成主键，从根源上避免主键冲突。

你当前的拉全量数据到内存再分批插入的方案，在数据量达百万级以上时内存开销大、网络交互次数多，推荐改用以下更适配Postgres的迁移方案：

方案1：使用postgres_fdw跨库直接操作（最省事） #

• 在目标库database b中创建postgres_fdw扩展，连接到源库database a
• 提前在目标库做好sensor_id的映射表，存储源sensor_id到新sensor_id的对应关系
• 直接用跨库INSERT语句批量写入，不需要拉取数据到本地：

INSERT INTO b.readings (sensor_id, value, time)
SELECT map.new_sensor_id, a.value, a.time
FROM foreign_a.readings a
JOIN sensor_id_map map ON a.sensor_id = map.old_sensor_id
WHERE a.sensor_id in (/* 对应站点的所有源sensor_id */)
ON CONFLICT (id) DO NOTHING;

• 可以按时间范围拆分语句分批执行，避免长事务锁表。

方案2：使用COPY命令批量导入（性能最高） #

• 从源库按站点导出指定数据到CSV，导出时就完成sensor_id的替换：

COPY (
SELECT map.new_sensor_id, a.value, a.time
FROM readings a
JOIN sensor_id_map map ON a.sensor_id = map.old_sensor_id
WHERE a.sensor_id in (/* 站点对应sensor_id */)
) TO '/path/export.csv' WITH CSV;

• 再从CSV导入到目标库：

COPY readings (sensor_id, value, time) FROM '/path/export.csv' WITH CSV;

• 该方案比普通批量插入性能高3~10倍，适合千万级以上数据量迁移。

额外优化点 #

• 迁移期间暂时关闭目标表的非必要索引、触发器，迁移完成后再重建，可大幅提升写入速度。
• 调整目标库的maintenance_work_mem、wal_buffers参数，临时调大提升批量写入性能。
• 若保留源库的id值，迁移完成后执行SELECT setval('readings_id_seq', (SELECT MAX(id) FROM readings));更新自增序列，避免后续业务写入触发主键冲突。

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