执行零停机 PostgreSQL 列迁移

概述

滚动发布期间新旧代码同时运行，直接 ALTER TABLE ... RENAME COLUMN 会让旧实例立刻报错。expand-contract 把迁移拆成三个阶段：先增加新列并安装双向同步触发器，再部署只读写新列的版本，等旧实例全部退出后删除触发器和旧列。触发器必须按变化方向同步，而不是无条件覆盖两列，否则会丢掉调用方刚写入的值。

步骤

扩展阶段增加可空列和触发器，并设置短锁超时：

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE app.orders ADD COLUMN customer_email text;

CREATE OR REPLACE FUNCTION app.sync_order_email() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.customer_email := COALESCE(NEW.customer_email, NEW.email);
    NEW.email := COALESCE(NEW.email, NEW.customer_email);
  ELSIF NEW.customer_email IS DISTINCT FROM OLD.customer_email THEN
    NEW.email := NEW.customer_email;
  ELSIF NEW.email IS DISTINCT FROM OLD.email THEN
    NEW.customer_email := NEW.email;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_sync_email
BEFORE INSERT OR UPDATE ON app.orders
FOR EACH ROW EXECUTE FUNCTION app.sync_order_email();
COMMIT;

回填必须分批，并在 UPDATE 中再次检查 customer_email IS NULL，否则可能在 SELECT 批次和 UPDATE 之间覆盖新版本刚写入的值：

while true; do
  updated=$(psql "$DATABASE_URL" -Atc "
    WITH batch AS (
      SELECT id FROM app.orders WHERE customer_email IS NULL ORDER BY id LIMIT 1000
    )
    UPDATE app.orders o SET customer_email = o.email
      FROM batch WHERE o.id = batch.id AND o.customer_email IS NULL
    RETURNING 1" | wc -l | tr -d '[:space:]')
  [ "$updated" -eq 0 ] && break
done

回填完成后，用 CHECK ... NOT VALID 加约束并单独 VALIDATE CONSTRAINT，再 SET NOT NULL，最后删除临时约束。验证 email IS DISTINCT FROM customer_email 为 0，并在测试环境模拟旧版本只写 email，确认触发器仍会填充新列。等所有旧实例退出后，删除触发器、函数和旧列；用 CREATE INDEX CONCURRENTLY 建索引，且不能放在事务块中。

验证

执行 SELECT count(*) FILTER (WHERE customer_email IS NULL) AS missing_new, count(*) FILTER (WHERE email IS DISTINCT FROM customer_email) AS drift FROM app.orders;，两列都应为 0。契约阶段结束后查询 information_schema.columns，只应剩下 customer_email，且 is_nullable 为 NO。并用 pg_index.indisvalid 确认并发索引有效。

注意事项

SET NOT NULL 仍会短暂获取排他锁，必须保留 lock_timeout。不要为了迁移成功而删除锁超时，否则后续查询会排在 DDL 后面。CREATE INDEX CONCURRENTLY 失败可能留下无效索引，需要先删除再重建。迁移前要在接近生产数据量的副本上演练，空表无法暴露锁等待和回填时长。临时触发器增加每次写入开销，契约阶段要及时移除。