postgres-database-migration

PostgreSQL 数据库迁移

在空的开发数据库上可行的架构迁移，可能会在拥有数百万行数据的生产表上失败、造成锁定或损坏数据。本指南介绍如何评估风险、使用真实数据进行测试，以及安全地执行迁移。

DDL 锁参考

每次架构更改都会获取一个锁。关键问题是：它会阻塞读写操作吗？会阻塞多久？

快速、非阻塞操作

无论表的大小如何，这些操作都会在毫秒内完成。它们仅在更新系统目录时短暂持有 AccessExclusiveLock，不会重写数据。

操作	锁级别	说明
ADD COLUMN（可为空，无默认值）	AccessExclusiveLock（短暂）	**快速。**不会重写表。仅更改元数据。
ADD COLUMN ... DEFAULT x（PG 11+）	AccessExclusiveLock（短暂）	**快速。**非易变默认值存储在系统目录中，不会回填数据。
DROP COLUMN	AccessExclusiveLock（短暂）	**快速。**列会被标记为不可见；空间会随时间推移由 VACUUM 回收。
SET DEFAULT / DROP DEFAULT	AccessExclusiveLock（短暂）	仅更改元数据，不会触及现有行。
CREATE INDEX CONCURRENTLY	ShareUpdateExclusiveLock	**非阻塞。**创建索引期间允许读写。速度比常规创建索引慢。
DROP INDEX CONCURRENTLY	ShareUpdateExclusiveLock	**非阻塞。**等待正在使用该索引的查询完成后再删除，不会获取表级排他锁。
RENAME COLUMN	AccessExclusiveLock（短暂）	仅更改元数据。速度快。
RENAME TABLE	AccessExclusiveLock（短暂）	仅更改元数据。速度快。
ADD CONSTRAINT ... NOT VALID	ShareUpdateExclusiveLock	仅为新行添加约束，不会扫描现有数据。
VALIDATE CONSTRAINT	ShareUpdateExclusiveLock	扫描现有行，但允许并发读写。
CREATE/DROP TRIGGER	ShareRowExclusiveLock	短暂更新系统目录。

缓慢或会阻塞的操作

这些操作会重写表或扫描所有行。在大型表上，它们可能会阻止所有访问，持续数秒到数小时。

操作	锁级别	缓慢的原因
ADD COLUMN ... DEFAULT x（易变值，例如 now()、gen_random_uuid()）	AccessExclusiveLock	完整重写表。每行都会获得计算后的值。
ALTER COLUMN TYPE（大多数类型更改）	AccessExclusiveLock	完整重写表以转换已存储的数据。
SET NOT NULL（PG < 12，或没有现有 CHECK 约束）	AccessExclusiveLock	完整扫描表，以验证不存在 NULL。参见下方的安全模式。
ADD CONSTRAINT ... CHECK/UNIQUE/FK（已验证）	AccessExclusiveLock 或 ShareRowExclusiveLock	扫描所有行进行验证，并阻塞写入。
CREATE INDEX（不使用 CONCURRENTLY）	ShareLock	在整个创建期间阻塞写入。
CLUSTER	AccessExclusiveLock	按索引顺序重写整个表。
VACUUM FULL	AccessExclusiveLock	重写表以回收空间。

关键要点：AccessExclusiveLock 会阻塞所有操作，包括读写。即使操作本身很快（毫秒级），获取锁之前也必须等待所有正在执行的事务完成。长时间运行的查询或空闲事务可能导致 ALTER TABLE 卡住，并使其后续的所有查询都排队等待。

安全的迁移模式

添加列

-- SAFE: nullable column, no default — instant
ALTER TABLE orders ADD COLUMN tracking_number TEXT;

-- SAFE (PG 11+): column with non-volatile default — instant
ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;

-- UNSAFE: column with volatile default — full table rewrite
-- DON'T: ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ DEFAULT now();
-- DO: add nullable, then backfill, then set default + NOT NULL
ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ;
-- Backfill in batches (see Backfill section)
ALTER TABLE orders ALTER COLUMN created_at SET DEFAULT now();
ALTER TABLE orders ALTER COLUMN created_at SET NOT NULL;  -- only if PG12+ or CHECK exists

删除列

-- SAFE: instant (column marked invisible, space reclaimed by VACUUM)
ALTER TABLE orders DROP COLUMN old_status;

应用协调： 删除列之前，确保应用不再引用该列。要实现零停机部署，需要分两步进行：

1. 部署不再读取或写入该列的代码
2. 然后在单独的迁移中删除该列

安全注意事项： DROP COLUMN 不会从物理上删除数据。该列会在 pg_attribute 中标记为已删除，但值仍保留在磁盘上，直到 VACUUM 回收空间——即便如此，超级用户仍可能恢复这些值。如果该列包含敏感数据，请在删除后对表运行 VACUUM FULL，或使用转储/还原来确保数据确实被彻底删除。

重命名列

-- SAFE: instant metadata change
ALTER TABLE orders RENAME COLUMN status TO order_status;

警告： 任何引用旧列名的应用代码、视图或函数都会因此失效。要实现零停机部署，请改用列交换模式：

1. 添加新列
2. 部署同时写入两列的代码
3. 回填旧行
4. 部署从新列读取数据的代码
5. 删除旧列

更改列类型

大多数类型更改都会重写整张表。安全的替代方案：

-- UNSAFE: full table rewrite, blocks everything
-- DON'T: ALTER TABLE orders ALTER COLUMN amount TYPE NUMERIC(12,2);

-- SAFE: use a new column + backfill
ALTER TABLE orders ADD COLUMN amount_new NUMERIC(12,2);

-- Backfill in batches (see Backfill section below)
UPDATE orders SET amount_new = amount WHERE id BETWEEN 1 AND 10000;
-- ... continue in batches ...

-- Swap columns
ALTER TABLE orders DROP COLUMN amount;
ALTER TABLE orders RENAME COLUMN amount_new TO amount;

例外： 某些类型转换不需要重写，且速度很快：

From	To	Rewrite?
VARCHAR(n) → VARCHAR(m)，其中 m > n	No	仅元数据
VARCHAR(n) → TEXT	No	仅元数据
NUMERIC(p,s) → NUMERIC(p2,s)，其中 p2 > p（精度相同）	No	仅元数据
INTEGER → BIGINT	Yes	完整重写
TIMESTAMP → TIMESTAMPTZ	Yes	完整重写

添加 NOT NULL 约束

-- PG 18+: simplified two-step pattern
ALTER TABLE orders ALTER COLUMN order_status SET NOT NULL NOT VALID;
ALTER TABLE orders VALIDATE NOT NULL ON order_status;

-- PG 12–17: fast if a valid CHECK constraint already exists
-- Step 1: add CHECK (non-blocking scan)
ALTER TABLE orders ADD CONSTRAINT orders_status_nn CHECK (order_status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn;

-- Step 2: add NOT NULL (PG12+ recognizes the CHECK and skips the scan)
ALTER TABLE orders ALTER COLUMN order_status SET NOT NULL;

-- Step 3: drop the now-redundant CHECK
ALTER TABLE orders DROP CONSTRAINT orders_status_nn;

-- PG < 12: SET NOT NULL always scans the full table.
-- Ensure no NULLs exist first, then accept the brief lock.

添加外键

-- UNSAFE: validates all existing rows while holding a heavy lock
-- DON'T: ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

-- SAFE: two-step approach
-- Step 1: add without validation (blocks writes briefly, doesn't scan data)
ALTER TABLE orders ADD CONSTRAINT fk_user
    FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Step 2: validate existing rows (allows concurrent reads and writes)
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

添加索引

-- UNSAFE on large tables: blocks all writes for the entire build
-- DON'T: CREATE INDEX idx_orders_user ON orders (user_id);

-- SAFE: concurrent index creation (allows reads and writes)
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);

-- IMPORTANT: if concurrent index creation fails (crashes, deadlock),
-- it leaves an INVALID index behind. Check and clean up:
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND indexrelname = 'idx_orders_user';

-- Check for invalid indexes
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index
WHERE NOT indisvalid;

-- Drop and retry if invalid
DROP INDEX CONCURRENTLY idx_orders_user;
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);

添加唯一约束

-- A UNIQUE constraint creates an index. Use CONCURRENTLY to avoid blocking:

-- Step 1: create a unique index concurrently
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_tracking_uniq ON orders (tracking_number);

-- Step 2: attach it as a constraint (instant)
ALTER TABLE orders ADD CONSTRAINT orders_tracking_uniq UNIQUE USING INDEX idx_orders_tracking_uniq;

重新定义主键

重新定义 PK（例如，从 id 切换到复合键，或从 int 切换到 bigint）需要同时满足唯一约束和 NOT NULL；如果操作不当，两者都可能导致长时间锁定。零停机方案是分别构建各个组成部分：

-- Step 1: add CHECK NOT NULL constraint without validation (brief lock)
ALTER TABLE orders ADD CONSTRAINT orders_new_id_nn
    CHECK (new_id IS NOT NULL) NOT VALID;

-- Step 2: validate existing rows (allows concurrent reads and writes)
ALTER TABLE orders VALIDATE CONSTRAINT orders_new_id_nn;

-- Step 3: build unique index concurrently (non-blocking)
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_new_pkey
    ON orders (new_id);

-- Step 4: drop the old PK
ALTER TABLE orders DROP CONSTRAINT orders_pkey;

-- Step 5: add new PK using the existing index (instant — also implicitly adds NOT NULL)
ALTER TABLE orders ADD CONSTRAINT orders_pkey
    PRIMARY KEY USING INDEX idx_orders_new_pkey;

-- Step 6: drop the now-redundant CHECK constraint
ALTER TABLE orders DROP CONSTRAINT orders_new_id_nn;

这样做的原因： 第 5 步速度很快，因为 Postgres 会复用已构建的唯一索引，并识别现有的 CHECK 约束，从而跳过索引构建和全表 NOT NULL 扫描（PG12+）。

删除约束

-- SAFE: instant metadata change
ALTER TABLE orders DROP CONSTRAINT orders_tracking_uniq;

-- If dropping a FK that has a supporting index you no longer need:
ALTER TABLE orders DROP CONSTRAINT fk_user;
DROP INDEX idx_orders_user_id;  -- only if no other queries use it

回填策略

始终分批回填，切勿通过单条 UPDATE 完成。有关按主键分批处理、可恢复的进度跟踪和调优指导，请参阅 backfill-strategies。

迁移验证

每次迁移前后都要运行验证查询。有关完整的检查项，包括 NULL 检测、重复项检测、孤立行、类型转换失败、耗时估算、架构验证、数据完整性和查询性能，请参阅 validation-queries。

回滚规划

执行迁移前，应为每次迁移记录回滚方案。

可逆操作

操作	回滚
ADD COLUMN	DROP COLUMN
ADD CONSTRAINT	DROP CONSTRAINT
CREATE INDEX	DROP INDEX
RENAME COLUMN x TO y	RENAME COLUMN y TO x
SET DEFAULT x	SET DEFAULT old_value 或 DROP DEFAULT
ADD COLUMN new + DROP COLUMN old	无法直接撤销——需要重新添加旧列，并从备份回填

不可逆操作

要撤销这些操作，需要从备份或数据库分叉恢复：

• DROP COLUMN — 一旦 VACUUM 回收空间，数据就会消失
• ALTER COLUMN TYPE 使用有损转换（例如，NUMERIC → INTEGER、TEXT → VARCHAR(50)）
• 数据清理期间执行的 DELETE / TRUNCATE
• DROP TABLE

数据库分叉在此时极具价值。 如果你在迁移前创建了分叉，原始数据库就保留着迁移前的状态。如果迁移出错，生产数据不会受到影响——只需删除分叉，然后重新开始。

事务策略

执行多个 DDL 语句有两种方式，各有利弊：

包装在一个事务中 — 所有更改要么全部成功，要么全部回滚。当原子性比锁持有时间更重要，且所有操作都很快（毫秒级）时，使用此方式。

BEGIN;

ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;
ALTER TABLE orders ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';
CREATE INDEX ON orders USING GIN (tags);
ALTER TABLE orders DROP COLUMN old_priority;

-- Verify before committing
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

COMMIT;
-- Or ROLLBACK; if something looks wrong

使用多个独立事务 — 每条 DDL 语句都独立运行并提交。当锁持有时间比原子性更重要时，使用此方式。在单个事务中，所有锁都会一直持有到 COMMIT — 因此，如果有 5 条 DDL 语句，第一条语句获取的 AccessExclusiveLock 会在全部 5 条语句执行期间阻塞流量。使用多个独立事务，可以在语句之间释放锁。

-- Each statement auto-commits
ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;
ALTER TABLE orders ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';
ALTER TABLE orders DROP COLUMN old_priority;

权衡： 如果后续语句失败，使用多个独立事务可能会导致架构处于部分迁移状态。你需要为每个步骤单独制定回滚计划。

不能在事务中使用：

• CREATE INDEX CONCURRENTLY（明确禁止在事务中执行）
• DROP INDEX CONCURRENTLY
• 任何需要独立事务上下文的语句

处理长时间运行的查询

即使 ALTER TABLE 执行很快，如果它需要在长时间运行的查询之后获取 AccessExclusiveLock，仍可能一直等待。更糟的是，正在等待的 DDL 也会阻塞所有后续查询 — 即使是简单的 SELECT 也会排队等待：

Session 1: SELECT COUNT(*) FROM orders;          -- long query, holds AccessShareLock
Session 2: ALTER TABLE orders ADD COLUMN ...;     -- waits for Session 1 (needs AccessExclusiveLock)
Session 3: SELECT * FROM orders WHERE id = 123;   -- BLOCKED by Session 2's lock queue entry
Session 4: INSERT INTO orders (...) VALUES (...);  -- also BLOCKED
-- All sessions freeze until Session 1 finishes and Session 2 completes or times out

这就是 lock_timeout 至关重要的原因 — 如果没有它，单个慢查询就可能引发连锁反应，导致整个应用发生故障。

设置超时

lock_timeout — 等待获取锁的时长上限，超时后放弃。每条生产环境中的 DDL 语句都应设置此项。如果不设置，ALTER TABLE 可能会排在长时间运行的查询后面，并无限期地阻塞所有后续查询。

statement_timeout — 获取锁后语句可以运行的时长上限。这是针对意外耗时操作的安全保障（例如，未预料到某种类型变更会触发表重写）。权衡是：如果操作进行到一半时触发超时，整个语句都会回滚 — 对 DDL 来说这是安全的（不会产生部分更改），但意味着运行时间较长的 CREATE INDEX CONCURRENTLY 可能在接近完成时被终止。因此，对于已知耗时较长的操作（例如在大型表上并发构建索引），应避免设置 statement_timeout，改为手动监控。

选择超时值：

有两种常见做法：

• 保守型（lock_timeout 设为 50-100ms，重试数百次）： 尽量缩短等待中的 DDL 阻塞其他查询的时间窗口。每次尝试对应用流量的影响几乎不可察觉，但需要重试逻辑。最适合高流量 OLTP 系统，在这类系统中，即使查询只被阻塞几秒也无法接受。
• 务实型（lock_timeout 设为 3-5s，重试次数较少）： 让每次尝试都有合理机会获取锁，从而减少对复杂重试逻辑的需求。对于可以容忍短暂停顿的大多数应用而言，这种方式可以接受。

根据流量情况选择：查询吞吐量越高，lock_timeout 就应越短 — 因为即使是短暂排队，每秒也会影响更多查询。对于 statement_timeout，应将其设置为预计操作耗时的宽裕倍数（例如，纯元数据更改设为 30s，大型表上的 VALIDATE CONSTRAINT 设为几分钟，CREATE INDEX CONCURRENTLY 则禁用）。

-- Fail fast instead of blocking all queries behind you
SET lock_timeout = '5s';
SET statement_timeout = '30s';

ALTER TABLE orders ADD COLUMN tracking_number TEXT;

-- If it fails with "canceling statement due to lock timeout":
-- 1. Find what's blocking
SELECT pid, state, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- 2. Wait for the blocker to finish, or cancel it if appropriate
-- SELECT pg_cancel_backend(<pid>);

-- 3. Retry the ALTER TABLE
SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN tracking_number TEXT;

-- Reset timeouts when done
RESET lock_timeout;
RESET statement_timeout;

带超时重试模式

对于自动化迁移运行器，应使用较短的锁超时，在重试循环中执行 DDL：

DO $$
DECLARE
    max_attempts INTEGER := 5;
    attempt INTEGER := 1;
    success BOOLEAN := FALSE;
BEGIN
    WHILE attempt <= max_attempts AND NOT success LOOP
        BEGIN
            SET lock_timeout = '3s';
            -- Replace with your DDL statement
            ALTER TABLE orders ADD COLUMN tracking_number TEXT;
            success := TRUE;
            RAISE NOTICE 'DDL succeeded on attempt %', attempt;
        EXCEPTION
            WHEN lock_not_available THEN
                RAISE NOTICE 'Attempt % failed (lock not available), retrying...', attempt;
                PERFORM pg_sleep(2 * attempt);  -- linear backoff
                attempt := attempt + 1;
        END;
    END LOOP;

    IF NOT success THEN
        RAISE EXCEPTION 'DDL failed after % attempts', max_attempts;
    END IF;
END $$;

这样可以避免迁移在其后方造成大量查询阻塞排队。每次尝试要么快速成功，要么放弃并让正常流量继续通过。

替代方案：NOWAIT — 对于流量最高的系统，可使用 LOCK TABLE ... NOWAIT 在运行 DDL 前测试锁是否可用。与 lock_timeout 不同，NOWAIT 会立即失败，且从不会进入锁队列，因此完全不会有查询阻塞连锁反应的风险。权衡是需要更多次重试：

BEGIN;
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE NOWAIT;
-- If we get here, we have the lock — run DDL
ALTER TABLE orders ADD COLUMN tracking_number TEXT;
COMMIT;
-- If LOCK fails with "could not obtain lock", retry after a short sleep

基于分支的迁移测试

测试迁移最安全的方式，是针对实际数据库的副本运行迁移——架构、数据和边界情况都相同。Neon 等提供商支持快速创建数据库分支。如果不支持数据库分支，你需要手动转储并恢复数据库；对于大型数据集，这可能需要很长时间。

支持分支时

1. 创建数据库分支 — 使用提供商的分支功能创建完整副本（只需几秒）
2. 检查分支上的当前架构，确认其与生产环境一致
3. 在分支上运行迁移
4. 验证 — 运行检查（参见上文的迁移前／迁移后验证部分）
5. 如果成功： 将相同的迁移应用到生产环境
6. 如果失败： 删除分支 — 生产数据库不会受到影响

这可以发现空测试数据库中不会出现的问题：

• 违反新约束的数据
• 对实际值执行类型转换时失败
• 在 100 行数据上运行很快，但在 1,000 万行数据上会锁表数分钟的迁移
• 创建索引时耗尽内存或磁盘空间

限制： 基于分支的测试会隔离运行迁移，无法发现由并发数据库流量导致的问题（例如负载下的锁争用、并发写入导致的死锁，或大量生成 WAL 导致的复制延迟）。对于大多数应用，基于分支的测试已足够。对于对运行时间要求极高的应用，可以使用 PgDog 的镜像功能，将生产流量重放到分支上——它会以逼真的时序逐字节重现查询；你还可以筛选仅处理 DDL 或仅处理 DML，并控制流量暴露比例，以便逐步提高比例。

不支持分支时

从备份或转储创建测试数据库：

# Dump your production database
pg_dump -Fc my_app_db > backup.dump

# Restore into a test database
createdb migration_test
pg_restore -d migration_test backup.dump

# Or clone from a live database (requires downtime on source during copy)
createdb migration_test -T my_app_db

完整迁移示例

如需完整的端到端操作指南（计划、创建分支、运行、验证、应用、清理），请参阅 完整示例。

高级注意事项

PL/pgSQL 重试循环中的子事务： 带超时重试模式中的 BEGIN/EXCEPTION WHEN/END 块会创建隐式子事务。在高写入吞吐量下，这可能导致副本上的 SubtransSLRU 争用——尤其是在重试循环作为长期运行的事务执行且尝试次数很多时。如果重试期间发现副本延迟，请将重试逻辑移至应用层（每次尝试使用独立事务），而不要使用 PL/pgSQL 异常处理。

Autovacuum 可能会阻塞 VALIDATE CONSTRAINT： VALIDATE CONSTRAINT 会获取 ShareUpdateExclusiveLock，而该锁与处于事务 ID 回绕预防模式下运行的 autovacuum 冲突。如果 VALIDATE 意外挂起，请检查 pg_stat_activity，查看同一张表上是否有 autovacuum 进程。你可能需要等待防止回绕的 autovacuum 执行完毕——不要取消它，因为如果表接近 XID 回绕上限，这可能导致数据丢失。

常见陷阱

1. 在空表上测试迁移——在空表上只需 1ms 的迁移，可能会锁定一个有 1000 万行的表数分钟。务必使用真实的数据量进行测试。
2. 创建索引时忘记使用 CONCURRENTLY——CREATE INDEX（不带 CONCURRENTLY）会阻塞所有写入。在有活跃流量的表上，这会导致停机。
3. 未采用两步模式添加 NOT NULL——在 PG < 12 的大型表上，SET NOT NULL 会在持有 AccessExclusiveLock 的同时扫描每一行。请使用 CHECK 约束模式。
4. 未设置锁超时——一个很快完成的 ALTER TABLE 可能会被长时间运行的查询阻塞，之后的每个查询也都会排队等待。生产环境执行 DDL 时，务必设置 lock_timeout。
5. 在一个大事务中回填数据——在 1000 万行上执行单条 UPDATE orders SET x = y 会产生海量 WAL、使表膨胀，并在整个执行期间持有锁。务必分批处理。
6. 遗留无效索引——如果 CREATE INDEX CONCURRENTLY 失败，会留下一个不可见的无效索引，该索引会占用空间并拖慢写入。每次并发索引操作后，都要检查 pg_index.indisvalid。
7. 在更新应用代码前删除列——在运行中的系统里，旧代码仍会引用该列。先部署代码变更，再在后续迁移中删除该列。
8. 未检查复制延迟——大型回填会产生大量 WAL。如果有只读副本，请在迁移期间及之后监控 pg_stat_replication。
9. 假设 ALTER COLUMN TYPE 是安全的——大多数类型变更都会重写整个表。对于大型表，请使用“添加新列 + 回填 + 切换”模式。