Midnight cutover to new managed PostgreSQL. 00:17 FK constraint errors — orphan table exists on new only. Rollback announced 45 minutes. Cause: schema migration without expand/contract, traffic switched before row count parity check.
Database migration = sequencing three flows: compatible schema, data sync, traffic switch. Order matters more than the advertised maintenance window.
Expand / contract
- Add nullable columns/tables (expand).
- Deploy app reading both schemas if needed.
- Backfill data.
- Switch writes.
- Remove old (contract) — never before proven parity.
Breaking change same release as cutover = guaranteed incident ticket.
Data sync
| Method | When |
|---|---|
| pg_dump / restore | Small DB, accepted maintenance |
| Logical replication | Large DB, low downtime |
| CDC (Debezium) | Near real-time |
| Snapshot + WAL | Cloud managed PITR |
Test lag and conflicts before minute cutover. Size new instance IOPS/RAM before sync.
Cutover checklist
Freeze schema changes. Compare sample row count hash. Stop old writes. Final sync. Flip connection pool (PgBouncer session mode if prepared statements). Smoke tests. Monitor errors 1h.
DB URL feature flag. Old instance read-only 7d minimum post-cutover.
PgBouncer and pools
Transaction mode breaks prepared statements — session mode or direct connection cutover day in runbook. Pools cache old hostname: restart apps explicitly post-cutover.
Credible rollback: revert DNS/pool + reverse resync tested on clone — not a slide without timed commands.
Clone dry-run
Run full cutover on identical clone — midnight prod is not when to discover order. Archive signed plan: expand/contract, windows, owners, numeric rollback criteria.
Compare RDS/managed DB hosts via comparison tool on IOPS and cutover support.
Post-cutover week
Keep old instance read-only seven days minimum. Monitor error rate schema mismatch — ORM cache stale rare but vicious. Communication: all-clear only after parity checks green.
Character set migration utf8 to utf8mb4 test with emoji customer names before cutover — silent truncation hurts.
Application feature flags decouple schema deploy from traffic flip — deploy schema tolerant code first, then data, then enable feature.
Operational follow-up
Archive signed migration plan — future post-mortem without oral history. Include expand-contract order, windows, owners, numeric rollback criteria. Liquibase checksum conflict on Friday deploy — freeze migrations cutover week documented. Document gaps between host marketing and field measurement in the quarterly review.
Quarterly follow-up
Archive signed migration plan — future post-mortem without oral history. Include expand-contract order, windows, owners, numeric rollback criteria. Liquibase checksum conflict on Friday deploy — freeze migrations cutover week documented. Document gaps between host marketing and field measurement in the quarterly review.
Persistent connection pools cache old DB hostname after cutover — restart apps explicitly in runbook.
Keep a dated runbook, before/after metrics, post-incident review — cumulative discipline beats Friday night panic.
Keep a dated runbook, before/after metrics, post-incident review — cumulative discipline beats Friday night panic.
Keep a dated runbook, before/after metrics, post-incident review — cumulative discipline beats Friday night panic.
Keep a dated runbook, before/after metrics, post-incident review — cumulative discipline beats Friday night panic.
Keep a dated runbook, before/after metrics, post-incident review — cumulative discipline beats Friday night panic.
Keep a dated runbook, before/after metrics, post-incident review — cumulative discipline beats Friday night panic.
Operational follow-through
Keep a dated runbook, quarterly review with business teams, and before/after metrics for each change. Document gaps between host marketing and field measurement: latency, quotas, restore, support. To compare infrastructure and read more field notes, browse our directory, comparison tool, and technical blog guides — a documented decision beats an upgrade bought in Friday night panic.
Quarterly review
Compare field metrics and host product sheet: latency, quotas, restore, support delays. Adjust contract or architecture on evidence, not feeling.
Cycle closure
Share the updated runbook with support and schedule the next drill on a shared calendar — institutional memory avoids repeating the same mistakes.
Decide and move forward without blind spots
- Expand / contract schema — nullable columns, dual read, backfill, switch writes, drop old after parity.
- Cutover dry-run on clone — automated parity checks, 1h load test, timed rollback.
- PgBouncer session mode — during migration if scripts use prepared statements.
- Stakeholder communication — feature freeze, support FAQ, temporary double instance cost.
- Prod window only after clone success — midnight is wrong time to discover order.
RDS and managed DB: comparison tool, directory, migration guides.
Frequently asked questions
Big bang or dual-write?
Big bang concentrates risk in short window. Dual-write lowers minute cutover but complicates the app.
Schema before data?
Backward-compatible schema (expand) first, sync data, switch traffic, then contract.
PgBouncer during migration?
Transaction mode breaks prepared statements — session mode or direct connection on cutover day in runbook.
Credible rollback?
Revert DNS/pool + reverse resync tested on clone — not a slide without timed commands.
Run a full cutover dry-run on a clone — midnight prod is not when to discover order.