Postgres如何拷贝表数据、变更schema并同步直至无损切换主表

你原设想流程的核心问题是操作顺序错误：拷贝存量、改表结构到配置触发器的时间差内，TableA产生的增量数据没有被同步，必然会出现数据丢失。可以通过调整操作顺序、搭配原子操作实现全程无数据丢失，具体可落地流程如下：

操作全流程 #

1. 前置配置（第一步就做，从此时开始不会漏任何增量） #

• 创建和主表结构完全一致的空临时表，自动继承索引、约束、默认值等配置：

  CREATE TABLE TableB (LIKE TableA INCLUDING ALL);
  

• 创建增量同步触发器函数：

  CREATE OR REPLACE FUNCTION sync_a_to_b()
  RETURNS TRIGGER AS $$
  BEGIN
    IF TG_OP = 'INSERT' THEN
      INSERT INTO TableB VALUES (NEW.*);
      RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
      UPDATE TableB SET * = NEW.* WHERE 主键字段 = OLD.主键字段;
      RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
      DELETE FROM TableB WHERE 主键字段 = OLD.主键字段;
      RETURN OLD;
    END IF;
  END;
  $$ LANGUAGE plpgsql;
  

• 给主表绑定行级AFTER触发器，所有主表的新增/修改/删除操作都会自动同步到临时表，该触发器不阻塞主表写入：

  CREATE TRIGGER trigger_sync_a_to_b 
  AFTER INSERT OR UPDATE OR DELETE ON TableA 
  FOR EACH ROW EXECUTE FUNCTION sync_a_to_b();
  

2. 存量数据同步 #

• 用COPY命令或INSERT批量同步主表的存量数据，增加冲突跳过逻辑，避免和触发器已经同步的增量数据重复：

  -- COPY方式（适合超大数据表）
  COPY (SELECT * FROM TableA) TO '/tmp/table_a_stock.csv';
  COPY TableB FROM '/tmp/table_a_stock.csv' ON CONFLICT (主键字段) DO NOTHING;
  
  -- 或者INSERT方式（适合中小表，也可以按主键范围分批同步避免长事务）
  INSERT INTO TableB SELECT * FROM TableA ON CONFLICT (主键字段) DO NOTHING;
  

• 同步完成后做数据一致性校验：比对两表的总行数、最近1小时新增数据的内容，确认完全一致即可。

3. 临时表Schema变更 #

直接对TableB执行你需要的字段新增、类型修改、索引调整等操作，该操作完全不影响主表TableA的业务读写，即使耗时几小时也不会触发主表锁。

4. 原子切流 #

可以用RENAME命令实现秒级切换，要把操作放在同一个事务里保证原子性，不存在中间状态：

BEGIN;
-- 原主表重命名为备份表
ALTER TABLE TableA RENAME TO TableA_bak;
-- 临时表切换为新主表
ALTER TABLE TableB RENAME TO TableA;
COMMIT;

• 重命名操作是毫秒级，只会产生极短的表锁，业务侧基本无感知。切流完成后建议保留TableA_bak7~14天，确认业务无异常后再删除做兜底。

注意事项 #

• 如果表数据量超过100G，建议存量同步按主键范围分批执行，每次同步1万~10万行，避免长事务占锁。
• 如果业务有依赖TableA的视图、外键约束，切流后需要手动重建对应外键，视图一般不需要调整会自动适配新表。

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