使用Flyway执行Postgres pg_dump脚本时的幂等性与外键约束问题

问题1：外键约束冲突（无需调整COPY顺序） #

直接通过临时切换Postgres会话角色跳过约束检查，加载完成后统一验证外键，无需改动原有COPY语句的顺序：

• 在可重复迁移脚本开头执行：

  SET session_replication_role = 'replica';
  

• 执行所有原有的COPY命令加载种子数据
• 恢复正常角色并批量验证所有外键约束：

  SET session_replication_role = 'origin';
  
  -- 自动生成并执行所有外键约束的验证语句
  DO $$
  DECLARE
      rec record;
  BEGIN
      FOR rec IN
          SELECT 'ALTER TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' VALIDATE CONSTRAINT ' || quote_ident(conname) || ';' AS stmt
          FROM pg_constraint
          JOIN pg_class ON conrelid = pg_class.oid
          JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
          WHERE contype = 'f'
      LOOP
          EXECUTE rec.stmt;
      END LOOP;
  END $$;
  

这个方法利用Postgres的复制角色特性批量跳过外键检查，加载完成后统一验证数据约束，无需手动调整数十个表的COPY顺序。

问题2：实现COPY的幂等性（不转INSERT） #

采用「临时表COPY + UPSERT」模式，既保留COPY的高性能和小体积优势，又实现幂等性：
对每个种子数据表，将原有COPY脚本修改为以下结构：

1. 创建与目标表结构完全一致的临时表：

   CREATE TEMP TABLE temp_your_target_table AS SELECT * FROM your_target_table LIMIT 0;
   

2. 用COPY加载数据到临时表（保留原有的FORMAT、HEADER等参数）：

   COPY temp_your_target_table FROM '/path/to/your/data.csv' WITH (FORMAT csv, HEADER);
   

3. 通过UPSERT合并临时表数据到目标表（根据主键/唯一键处理冲突）：

   -- 若需更新重复数据：
   INSERT INTO your_target_table
   SELECT * FROM temp_your_target_table
   ON CONFLICT (your_primary_key_col1, your_primary_key_col2) DO UPDATE SET
       col_a = EXCLUDED.col_a,
       col_b = EXCLUDED.col_b;
   
   -- 若只需忽略重复数据：
   -- INSERT INTO your_target_table
   -- SELECT * FROM temp_your_target_table
   -- ON CONFLICT (your_primary_key_col1) DO NOTHING;
   

临时表会在会话结束后自动销毁，无需额外清理。这种方式完全复用COPY的高效批量加载能力，脚本体积不会因转成INSERT而膨胀，同时保证每次运行脚本都能安全同步种子数据（重复执行不会触发主键/唯一键冲突）。

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