A tested, rollback-safe runbook for taking a production PostgreSQL database across
a major version boundary (e.g. 13→16) — the kind of task an agent gets handed
("upgrade our Postgres") and that goes badly without a plan. Covers both the
pg_upgrade (in-place) and logical-replication (near-zero-downtime) paths, with a
pre-flight checklist and a rollback for each.
| Path | Downtime | Complexity | Use when |
|---|---|---|---|
pg_upgrade --link | minutes | low | You can take a short maintenance window |
| Logical replication | seconds (cutover only) | higher | You need near-zero downtime |
| Managed (RDS/Cloud SQL) | provider-managed | low | You're on a managed service |
UPGRADE-RUNBOOK.md — the full step-by-step for both paths, with exact commands.PRE-FLIGHT-CHECKLIST.md — everything to verify before you touch prod(extensions, deprecated features, disk, replication slots, pg_upgrade --check).
ROLLBACK.md — how to abort safely at each stage.scripts/preflight.sh — runs the read-only pre-flight checks and prints a report.scripts/post_upgrade.sql — the post-upgrade tasks people forget (ANALYZE,reindex where needed, extension updates, statistics).
1. Back up first, and test the restore — before anything.
2. Run pg_upgrade --check — it catches most blockers before downtime starts.
3. ANALYZE immediately after — the new cluster has no statistics; queries
will be slow and may pick bad plans until you do.
4. Have the rollback ready and rehearsed in staging before prod.
PostgreSQL 12+ source; access to run pg_upgrade / manage replication; a staging
copy to rehearse on. preflight.sh needs psql.
Get the full PostgreSQL Major Upgrade Runbook and unlock everything.
Get the complete guide with every chapter unlocked, including code samples, diagrams, and best practices.
Access all interactive tools with complete data, all workload profiles, and the full scenario library.
Downloadable source code, configuration files, and working examples from every chapter.
Free updates for life. Every new chapter, tool, and improvement included.