跨数据库复制数据时锁定特定行及避免主键冲突的方案咨询

原语句的问题 #

你提供的\copy语句完全不对，核心问题有两个：

1. 语法逻辑颠倒：PostgreSQL的\copy命令中，导出数据用\copy (查询语句) TO '文件'，导入数据是\copy 目标表 FROM '文件'，你写的\copy (SELECT ...) FROM '文件'根本不成立，既没法完成导出也没法实现导入。
2. 锁行逻辑错位：SELECT * FROM $TABLE_TARGET FOR UPDATE是锁定目标表中已存在的行，但你要导入的CSV数据可能还没进入目标表，这种锁不仅防不了新写入的主键冲突，还和导入动作毫无关联。

可行解决方案 #

方案一：分批锁行+冲突处理（高并发场景首选） #

核心思路是拆分CSV为小批量，每次仅锁定当前批次涉及的主键行（如果已存在），不影响目标表其他区域的写入，同时处理主键冲突：

1. 将大CSV拆分为多个小批量文件（比如每1000行一批），提前解析每个批次里的主键值。
2. 对每个批次执行以下事务：

BEGIN;
-- 锁定当前批次中已存在于目标表的主键行，防止其他事务修改
SELECT id FROM $TABLE_TARGET WHERE id IN (1,2,3,...) FOR UPDATE; -- 替换为当前批次的主键列表
-- 导入当前批次数据，碰到主键重复直接跳过（也可改为DO UPDATE更新数据）
\copy $TABLE_TARGET FROM 'batch_1.csv' WITH CSV HEADER ON CONFLICT (id) DO NOTHING;
COMMIT;

• 优势：仅锁定必要行，目标表其余区域可正常接收新数据；冲突处理灵活。
• 注意：主键列表可通过脚本（如Python、Shell）提取，比如用cut -d',' -f1 batch_1.csv获取CSV第一列的主键。

方案二：调整序列值（适用于主键为序列生成的场景） #

如果目标表的主键是通过序列自动生成的，直接将序列值调整为CSV中最大主键值+1，让新写入的数据自动使用更大的主键，从根源避免冲突：

1. 先从CSV中提取最大主键值，比如用命令sort -n -k1 temp.csv | tail -n1 | cut -d',' -f1。
2. 在目标数据库执行事务：

BEGIN;
-- 将序列值设置为大于CSV最大主键的值，false表示直接设置不触发自增
SELECT setval('target_table_id_seq', 10000, false); -- 10000替换为你的max_pk+1
-- 导入全部数据
\copy $TABLE_TARGET FROM '$TEMP_FILE' WITH CSV HEADER;
COMMIT;

• 优势：操作简单无需分批；全程不锁表，仅需短事务。
• 注意：必须在事务内执行setval和导入，防止两者之间有新数据写入导致序列值被覆盖。

方案三：可重复读隔离级别全量导入（适合数据量较小的场景） #

利用PostgreSQL的可重复读隔离级别，事务内仅能看到启动时的数据快照，不会读取导入过程中其他事务新增的数据，配合冲突处理即可：

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 导入时碰到主键重复直接跳过
\copy $TABLE_TARGET FROM '$TEMP_FILE' WITH CSV HEADER ON CONFLICT (id) DO NOTHING;
COMMIT;

• 优势：无需分批，操作简洁。
• 注意：如果导入过程中其他事务已写入相同主键，仍会触发冲突，因此ON CONFLICT子句必须添加；数据量过大时，长事务会影响数据库性能。

附：主键/序列信息翻译（通用示例） #

因未获取到图片具体内容，以下为通用翻译模板，你可根据图片内容替换：

• 主键信息：主键字段为[字段名]，属于[$TABLE_TARGET]表，数据类型为[如integer]，具备非空约束与唯一约束。
• 序列信息：序列名称为[如target_table_id_seq]，关联至[$TABLE_TARGET].[主键字段]，当前值为[数值]，增量为1，最小值1，最大值9223372036854775807，不启用循环。

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