A migration plan is incomplete until restore works

Define the invariant before the script

State what must remain true across the migration: row counts within defined filters, unique relationships, non-null business fields, balances, permissions, or application reads. Add acceptable duration and lock budgets. An agent can draft SQL from a schema, but it cannot infer which duplicate is legally or operationally correct to keep. Ambiguous records need a named owner and a disposition rule before production execution.

Use an expand-and-contract sequence when compatibility matters: add the new shape, deploy code that can tolerate both shapes, backfill in bounded batches, switch reads, then remove the old shape later. This costs time and temporary complexity, but it separates reversible application changes from destructive cleanup.

Rehearse with representative scale

A migration that succeeds on ten fixture rows says little about locks, log growth, replica lag, or a six-hour backfill. Use a production-like copy that is authorized and protected, or generate data matching relevant distributions when production data cannot be used. Record database version, row counts, indexes, hardware class, batch size, elapsed time, lock observations, failures, and restart behavior. Remove or mask sensitive information according to policy.

The hypothetical case is a nullable customer key becoming required. Rehearsal would first count nulls, exercise the backfill in resumable batches, verify no new nulls arrive during the transition, and measure the final constraint operation. The numbers must come from an actual run; none are invented here.

Choose rollback or restore deliberately

Schema rollback is not always safe after new writes use the new shape. Decide the last reversible point and the conditions that trigger stop, roll-forward repair, application rollback, or data restore. PostgreSQL documents several backup approaches with different properties. Logical dumps can be portable and selective, while continuous archiving with WAL supports point-in-time recovery but requires an intact sequence and is operationally more complex.

A backup file is evidence of a backup operation, not evidence of recoverability. Restore it into an isolated target, validate ownership and permissions, run integrity queries, and measure recovery time. PostgreSQL's recovery documentation explicitly calls for inspecting the recovered database before allowing ordinary users to connect.

PostgreSQL SQL dump and restore ↗

PostgreSQL point-in-time recovery ↗

Put authority outside the agent

The runbook should name the exact target, migration identity, application versions compatible with each state, preflight queries, monitoring, abort thresholds, backup identifier, restore procedure, and human decision owner. Use a least-privileged migration credential and keep production execution behind explicit approval. Retries must be idempotent or checkpointed so a restarted backfill does not duplicate or corrupt work.

After rehearsal, retain command output and validation results, but avoid copying secrets or personal data into the evidence packet. State the gap between rehearsal and production. Different load, extensions, replication topology, and concurrent traffic can invalidate a confident estimate. The proof is bounded preparation, not a promise that failure is impossible. Schedule cleanup separately: dropping old columns or compatibility code removes recovery options and deserves its own evidence and approval window.