Lorixen.
EN

Upgrading PostgreSQL to a new major version without drama

Short answer: Moving between minor versions (say 16.3 to 16.6) is just installing new binaries and restarting. Moving to a new major version (16 to 18) changes how data is stored on disk, so you need one of three methods: dump and restore (simple, slow for big databases), pg_upgrade (fast, works in place), or logical replication to a new server (least downtime, most moving parts). For most small apps, pg_upgrade after a full rehearsal on a copy is the sweet spot. Read the release notes for every version you're skipping, take a backup you've tested, and plan for refreshing statistics afterwards.

Minor versus major

PostgreSQL's documentation explains the numbering: in 18.1, 18 is the major version and 1 the minor. Minor releases never change the internal storage format, so updating is a matter of replacing the executables and restarting; the data directory stays as it is. Apply minor releases promptly, as they carry bug and security fixes.

Major releases may change the storage format, which is why you can't point a new major version at the old data directory. The docs list three ways to move the data: dump and restore, pg_upgrade, and replication.

Method 1: dump and restore

Export everything with a logical backup tool such as pg_dumpall, install the new version, and load the dump. The documentation notes that file system copies won't work for this, and recommends using the dump programs from the newer version, which can read from much older servers.

Good for: small databases, moving to a new server at the same time, cleaning up years of bloat as a side effect. Downside: the app is down for the whole dump and restore, which grows with database size.

Method 2: pg_upgrade

pg_upgrade converts the existing cluster to the new version without dumping and reloading every row. The documentation describes useful options:

  • --check checks both clusters without changing any data. Run it first, always.
  • Copy mode (the default) copies data files to the new cluster. Slower, but the old cluster remains usable if you need to go back.
  • --link uses hard links instead of copying. The docs say it's much faster and uses less disk space, but you can't access the old cluster once you start the new one, and both data directories must be on the same file system.
  • Clone mode, on supported file systems, gives similar speed without that drawback.

Extensions need attention: the docs explain that extension files matching the new server must be installed for the new cluster, but you shouldn't re-run CREATE EXTENSION, because the schema definitions come across from the old cluster.

Good for: most self-hosted app databases where a short maintenance window is acceptable.

Method 3: logical replication

The documentation notes that logical replication works between different major versions, so you can build a new server on the new version, let it catch up with the old one, then switch your app over. Downtime shrinks to the switchover itself.

Good for: larger databases or apps where even a short outage is expensive. Downside: more setup, tables need primary keys, and some objects such as sequences need handling at switchover.

Before you start

  • Read the release notes for every major version between yours and the target. PostgreSQL's docs advise reading all intervening versions' notes and paying attention to the "Migration" sections, since new versions can include changes that require application updates.
  • Check your app's drivers and ORM support the new version.
  • Take a full backup and test restoring it. A backup you haven't restored is a hope.
  • List your extensions and confirm they're available for the new version.
  • Rehearse on a copy. Restore last night's backup to a spare server, run the upgrade there, time it, and run your app's tests against it.

On the day

  1. Announce a maintenance window and put the app into maintenance mode.
  2. Stop writes, then take a final backup.
  3. Run pg_upgrade --check, then the real upgrade (or your chosen method).
  4. Start the new cluster and copy across configuration changes you'd made, such as memory settings and access rules.
  5. Refresh planner statistics. The pg_upgrade docs give vacuumdb commands for this; until it's done, queries may choose poor plans and seem slow.
  6. Run smoke tests: sign in, place a test order, run a report.
  7. Bring the app back and watch errors and slow queries closely for a day.

Plan the rollback

Decide in advance what would make you roll back and how. With copy mode or a separate replica, the old cluster is still there. With link mode, rollback means restoring from backup, so be sure about the backup first.

If you use a managed database

Hosted database services usually offer major upgrades through their console, often using similar methods underneath. The same advice applies: read the release notes, test on a clone, choose a quiet window and refresh statistics if the service doesn't.

A worked example

An illustrative Killarney bike rental company ran its booking app on PostgreSQL several major versions old on a single server. The developer restored a nightly backup to a spare machine and rehearsed pg_upgrade in copy mode, which surfaced one extension that needed a newer package. On the day, the booking site showed a maintenance page for a short early-morning window, the upgrade ran as rehearsed, statistics were refreshed, and test bookings confirmed everything worked. The old cluster was kept for a week, then removed.

FAQ

Can I skip several major versions at once?

Yes, upgrades can jump directly; just read the release notes for every version in between.

How long will it take?

Rehearse with a real copy to find out; timing depends on size, method and hardware.

Do I need to change my app?

Often not, but release notes may list changes that affect queries or settings.

How often should we upgrade?

Plan for a major upgrade well before your version stops receiving fixes.

If your database is overdue for an upgrade, tell us about your setup and we'll help you plan and rehearse it.

Sources

WhatsAppBook a call