Zero-Downtime Migration Runbook
Documented runbook plus bash scripts for the expand/contract migration pattern — add, rename, and drop columns without downtime.
Zero-Downtime Migration Runbook
A practical runbook and set of bash scripts for running schema migrations in production without taking downtime. Covers the expand/contract pattern for the three operations that most commonly cause incidents: adding a column, renaming a column, and dropping a column — with Postgres-specific examples throughout.
What's Included
- Expand/Contract Runbook — step-by-step playbook for each migration type with rollback procedures at every stage
- Add Column Script — adds a nullable column, backfills in batches (configurable size), then adds the NOT NULL constraint separately
- Rename Column Script — expand phase adds new column and syncs data via trigger; contract phase removes old column after deploy confirms
- Drop Column Script — marks column as unused in application first, then removes after deploy cycle; includes index drop ordering
- Lock Timeout Guards — all DDL statements wrapped with
SET lock_timeoutto fail fast rather than queue behind long-running transactions - Batched Backfill Utility — generic bash function for updating large tables in configurable row batches with progress logging
What You Get
RUNBOOK.md— full expand/contract methodology with decision treesscripts/add-column.sh— add column with safe backfillscripts/rename-column.sh— rename via expand/contract with trigger syncscripts/drop-column.sh— safe column removal with pre-flight checksscripts/backfill.sh— reusable batched UPDATE utilityREADME.md— which script to use when, and how to adapt them to your schema
Requirements
Postgres 14+ and psql in your PATH. Scripts use standard bash and psql — no extra dependencies. Adapt the connection string variables in each script to match your environment.