PostgreSQL Upgrades

Upgrading PostgreSQL splits into two very different tasks. Minor upgrades (e.g. 17.4 → 17.5) are routine: bug and security fixes with no change to the on-disk format — you install the new binaries and restart. Major upgrades (e.g. 16 → 17) are a real project: the on-disk data format can change, so you must actively migrate the data, and behavior or features may shift enough to require testing.

Neglecting upgrades is a slow-motion risk — running an end-of-life Postgres version means no security patches and eventual forced migration under pressure. Doing major upgrades well means choosing the right method for your downtime tolerance (in-place pg_upgrade, dump/restore, or near-zero-downtime logical replication) and following a disciplined checklist. This page covers the distinction, the methods, and how to upgrade without an incident.

TL;DR

Quick Example

Minor upgrades are trivial; major upgrades need pg_upgrade (or another migration method):

--link mode makes pg_upgrade fast even on large databases by hard-linking data files rather than copying them — but it means you can't fall back to the old cluster once started, so back up first.

Core Concepts

Minor vs Major Versions

PostgreSQL versioning: the first number is the major version (16, 17, 18…), the second is the minor (17.4, 17.5). The distinction is everything for upgrades:

Apply minor upgrades promptly (they're security fixes and low-risk). Treat each major upgrade as a planned, tested migration.

The Three Major-Upgrade Methods

1. pg_upgrade (in-place). Migrates the existing data directory to the new version's format. In --link mode it's fast even for large databases (hard-links instead of copies). Requires downtime during the upgrade window (both clusters stopped/started), but that window is short. The standard choice when a maintenance window of minutes is acceptable.

2. Dump and restore. pg_dump from the old version, pg_restore into the new one. Simplest and most robust (it rebuilds everything cleanly, and dumps are cross-version), but slow for large databases and requires downtime for the whole dump+restore. Fine for small databases; impractical for big ones.

3. Logical replication (near-zero downtime). Because logical replication works across major versions, you replicate from the old-version primary to a new-version database, let it catch up, then cut over in seconds. This achieves near-zero downtime but is the most setup and care (schema, sequences, coordination). The go-to when downtime must be minimal.

End-of-Life and the Support Window

Each major version receives fixes for about five years, then reaches end-of-life (EOL) — no more patches, including security fixes. Running an EOL version is a growing liability, and upgrading across many versions at once is harder than staying reasonably current. Track the support timeline and upgrade before EOL, ideally on a routine cadence rather than a panicked jump.

Best Practices

Always Back Up Before a Major Upgrade

Take (and verify) a backup before any major upgrade. pg_upgrade --link in particular leaves no clean fallback once the new cluster starts. A tested backup is your safety net if the upgrade goes wrong. Never upgrade a production database without one.

Test the Upgrade on a Copy First

Run the entire upgrade against a copy of production data in a staging environment: verify it completes, check application behavior against the new version, and confirm your queries still perform (major versions can change the planner). Surprises found in staging are cheap; found in production they're an incident.

Check Extension and Application Compatibility

Every extension you use must support the target version, and your application/driver should be tested against it. An incompatible extension can block the whole upgrade. Verify compatibility before scheduling, not during the maintenance window.

Run ANALYZE After Upgrading

pg_upgrade does not carry over planner statistics, so the new cluster starts "blind" and may pick bad plans until stats are rebuilt. Run vacuumdb --all --analyze-in-stages (or ANALYZE) right after the upgrade so the planner has current statistics. Skipping this causes mysterious post-upgrade slowness.

Choose the Method by Downtime Tolerance

Match the method to what you can afford: pg_upgrade --link for a short maintenance window on large data, dump/restore for small databases, and logical replication when you need near-zero downtime. Decide this early — it shapes the whole plan.

Common Mistakes

Running an End-of-Life Version

Skipping ANALYZE After pg_upgrade

The upgrade doesn't transfer statistics, so the freshly-upgraded database can suddenly run queries slowly with bad plans. Always ANALYZE (via vacuumdb --analyze-in-stages) immediately after, before pointing production traffic at it.

Upgrading Without Testing on a Copy

Going straight to production assumes no behavior, compatibility, or performance regressions — major versions can bring all three. Rehearse the full upgrade on a production-data copy in staging first.

FAQ

What's the difference between a minor and a major PostgreSQL upgrade?

A minor upgrade (like 17.4 → 17.5) delivers only bug and security fixes and doesn't change the on-disk data format — you just install the new binaries and restart. A major upgrade (like 16 → 17) can change the on-disk format and behavior, so you must actively migrate the data (via pg_upgrade, dump/restore, or logical replication) and test for changes. Minors are routine; majors are planned projects.

How do I upgrade with minimal downtime?

Use logical replication. Because it works across major versions, you replicate from the old-version primary to a new-version database, let it fully catch up, then cut the application over in seconds. It requires the most setup and care (schema, sequences, coordination) but achieves near-zero downtime. If a short maintenance window is acceptable instead, pg_upgrade --link is fast and much simpler.

Do I really need to do major upgrades?

Yes, eventually — each major version is supported for about five years, then reaches end-of-life with no further security patches. Running an EOL version is a growing security and support risk, and jumping across many versions later is harder than stepping up regularly. Treat major upgrades as routine maintenance on a cadence, not something to defer indefinitely.

Why is my database slow right after a major upgrade?

Almost certainly missing statistics. pg_upgrade doesn't carry over the planner's statistics, so the new cluster starts without them and may choose poor query plans. Run vacuumdb --all --analyze-in-stages (or ANALYZE) immediately after upgrading to rebuild statistics, ideally before directing production traffic at the new cluster.

What should I check before a major upgrade?

Take and verify a backup; rehearse the full upgrade on a copy of production data in staging; confirm every extension and your application/driver support the target version; plan the method by your downtime tolerance; and prepare to ANALYZE afterward. Verifying extension compatibility and testing on a copy are the steps most often skipped — and most likely to cause trouble.

Related Topics

References