PostgreSQL Major Upgrade Runbook
Tested, rollback-safe runbook for a production Postgres major-version upgrade (pg_upgrade and logical-replication paths) with pre-flight checks, post-upgrade SQL, and rollback per stage.
Product Preview
Try the interactive reader and demo tools below, or get the full product with all content unlocked.
Interactive Reader (Free Preview) Download Free SampleFile Structure 7 files
Documentation Preview README excerpt
PostgreSQL Major Version Upgrade Runbook
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.
Two paths — pick by your downtime budget
| 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 |
What's inside
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).
The golden rules
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.
Requirements
PostgreSQL 12+ source; access to run pg_upgrade / manage replication; a staging
copy to rehearse on. preflight.sh needs psql.
License
MIT.