The 9-Step Postgres Major-Upgrade Playbook for Teams Without DevOps
The 9-Step Postgres Major-Upgrade Playbook for Teams Without DevOps
Major PostgreSQL upgrades feel disproportionately risky for small engineering teams. There's no dedicated database reliability engineer, no staging environment that perfectly mirrors production, and no time to spend a week validating every extension. Yet skipping upgrades has its own cost: end-of-life versions lose security patches, and the gap between your version and current grows wider every year, making eventual upgrades harder. A structured, minimal-overhead process closes that gap without requiring a platform team.
Pre-Upgrade Assessment
Start with a version check against the official PostgreSQL support policy — know exactly how many major versions behind you are and when your current version loses support. Next, inventory extensions (PostGIS, pg_cron, pgvector, etc.) and confirm each has a compatible build for the target version; extension incompatibility is the most common cause of failed upgrades. Review deprecated features and syntax changes in the release notes for every version you're jumping across, not just the target. Finally, check application-side driver compatibility and any code relying on version-specific behavior (implicit casts, catalog columns, default privileges).
Test Environment and Dry Runs
Without dedicated DevOps staff, the test environment doesn't need to be elaborate — it needs to be representative. Clone production schema and a realistic data sample, then run the actual upgrade tool you'll use in production (pg_upgrade, logical replication, or a managed provider's upgrade path) against it. Time the dry run precisely; this becomes your maintenance-window estimate. Run your application's test suite and a handful of manual smoke tests against the upgraded clone. If query plans are a concern, comparing execution plans before and after is worth the extra hour — planner behavior can shift meaningfully between major versions, and catching a regressed plan in staging is far cheaper than debugging it live. Reviewing query plan changes systematically, rather than spot-checking a few endpoints, reduces the odds of a surprise slow query in production.
Cutover Planning
Choose your method based on downtime tolerance: pg_upgrade with hard links is fast but requires a maintenance window; logical replication allows near-zero downtime but takes more setup and monitoring. Write a runbook with exact commands, not prose — a three-person team under pressure needs copy-pasteable steps, not decisions made on the fly. Schedule the window during genuinely low traffic, communicate it internally and to users if relevant, and assign one person as the sole executor while a second monitors metrics and a third stays reachable for escalation. Freeze schema migrations and non-critical deploys for 24 hours before and after.
Rollback Plan and Post-Upgrade Monitoring
A rollback plan is not optional. Take a verified, restorable backup immediately before cutover, and know your exact rollback trigger conditions in advance (e.g., failed data verification, error rate above a threshold, replication lag past a limit) rather than deciding under stress. After cutover, watch for the two most common post-upgrade issues: stale planner statistics causing slow queries (run ANALYZE promptly) and connection pool misconfiguration. Keep close tabs on slow query patterns for the first week, since regressions often surface under real traffic rather than in testing. Tools like Querk can help surface these regressions without requiring a dedicated ops hire.