PostgreSQL 15 to 17 Major Upgrade with pg_upgrade

A major PostgreSQL upgrade changes the on-disk data format, so the naive pg_dump-and-restore route can take a busy database offline for hours you do not have. pg_upgrade with –link does the same job in minutes — provided you have rehearsed it and have a rollback you have actually tested.

Most upgrade guidance treats the version bump as the difficult part. It is not. PostgreSQL 15 leaves community support in November 2027; 17 is the current stable series and well understood. The mechanics of pg_upgrade have been stable for years. The parts that actually bite are matching extension binaries across the two clusters, deciding whether –link is safe for your storage, and having a rollback you have practised rather than one you have written down. This walks a 15-to-17 in-place upgrade on a Linux host running the PGDG packages, with the exact commands and where each one can go wrong.

Why pg_upgrade and not pg_dump

Between major versions the internal storage format can change, which is why you cannot simply point a version 17 server at a version 15 data directory. There are two honest ways across. A logical dump and restore rebuilds every table and index from SQL — correct, portable, and painfully slow on anything past a few gigabytes, because it rewrites and re-indexes the entire database. pg_upgrade instead migrates the system catalogs and leaves the user data files where they are. With the default copy mode it duplicates the data files into the new cluster; with --link it creates hard links instead, so no bulk copy happens at all and the switch is close to instant regardless of database size.

The --link speed comes with a condition worth stating plainly: the old and new data directories must sit on the same filesystem, because you cannot hard-link across filesystems. And once you have started the version 17 cluster on hard-linked files, the version 15 cluster is no longer safe to use — the two clusters share the underlying data files. That single fact shapes the whole rollback plan, so hold on to it.

Install both binaries side by side

pg_upgrade needs both the old and new server binaries present at the same time. The PGDG packages install each major version under its own path, so 15 and 17 coexist without conflict. Install the new server and its contrib modules, then initialise an empty 17 cluster.

# Rocky/RHEL with the PGDG repository already configured
sudo dnf install -y postgresql17-server postgresql17-contrib

# Initialise an empty version 17 data directory
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb

# Do NOT enable or start the 17 service yet; pg_upgrade needs it stopped
sudo systemctl stop postgresql-15 postgresql-17

Initialise the new cluster with the same locale and encoding as the old one, otherwise pg_upgrade will refuse to proceed. If you set anything non-default on the version 15 cluster, pass the matching --locale and --encoding to initdb.

Run –check before you touch anything

pg_upgrade has a read-only pre-flight mode. --check validates both clusters — versions, locales, extensions, incompatible data types — and changes nothing. Run it as the postgres user, from a directory that user can write to, because pg_upgrade drops its log files in the working directory.

sudo -iu postgres
cd /tmp

/usr/pgsql-17/bin/pg_upgrade 
  --old-bindir=/usr/pgsql-15/bin 
  --new-bindir=/usr/pgsql-17/bin 
  --old-datadir=/var/lib/pgsql/15/data 
  --new-datadir=/var/lib/pgsql/17/data 
  --link 
  --check

A clean run ends with “Clusters are compatible”. Treat any warning as a stop sign, not a suggestion — the most common one is a missing extension binary, which is the next section. Run --check against a restored copy of production first, on a rehearsal host, so the real cutover holds no surprises. Treat the upgrade as a governed change like any other: rehearse it, and measure it against the same change-failure and recovery metrics you hold your deployments to.

Match extensions across both clusters

This is where in-place upgrades most often fail. pg_upgrade carries the extension entries over from the old cluster’s catalog, but the shared object files those extensions load — postgis.so, pgcrypto.so and the like — must already exist under the version 17 installation. If the old cluster used PostGIS or any third-party extension, install the matching version 17 package before you upgrade.

# List what the old cluster actually uses, per database
sudo -u postgres /usr/pgsql-15/bin/psql -Atc 
  "SELECT extname, extversion FROM pg_extension ORDER BY 1"

# Install the matching version 17 binaries, e.g. PostGIS
sudo dnf install -y postgis34_17

Do not run CREATE EXTENSION on the new cluster by hand — pg_upgrade reproduces the extension objects from the old cluster’s schema, and creating them yourself will collide. After the upgrade you can move an extension to the newer version the 17 package ships with, per database, using ALTER EXTENSION postgis UPDATE;. Keep that as a deliberate, separate step; it is not part of the version migration itself.

The switch, then the connection cutover

With both servers stopped and --check clean, drop the --check flag and run the same command for real. On a hard-linked upgrade this completes in seconds to low minutes almost irrespective of database size.

/usr/pgsql-17/bin/pg_upgrade 
  --old-bindir=/usr/pgsql-15/bin 
  --new-bindir=/usr/pgsql-17/bin 
  --old-datadir=/var/lib/pgsql/15/data 
  --new-datadir=/var/lib/pgsql/17/data 
  --link

exit  # back to your admin user

pg_upgrade leaves a delete_old_cluster.sh script in the working directory. Do not run it yet. Start the new server, confirm the version, and only cut application traffic over once you are satisfied. Because the fresh version 17 cluster came up on the default port, the connection-string cutover is a config change plus an application restart, not a data operation.

sudo systemctl enable --now postgresql-17
sudo -u postgres /usr/pgsql-17/bin/psql -c "SELECT version();"

Rebuild statistics before you take real traffic

pg_upgrade does not carry optimiser statistics across to version 17. The new cluster starts with none, so the planner is effectively blind until you regenerate them — queries that were instant on 15 can crawl for the first few minutes on 17. This is the single most common “the upgrade broke performance” report, and it is entirely avoidable. Run vacuumdb --all --analyze-in-stages immediately after starting the new server; it produces usable statistics in three quick passes rather than one slow one, so the planner has something to work with almost straight away.

# Generate minimal stats fast, ignoring any vacuum cost delay
sudo -u postgres PGOPTIONS='-c vacuum_cost_delay=0' 
  /usr/pgsql-17/bin/vacuumdb --all --analyze-in-stages

A rollback you have actually tested

Here is where --link demands respect. Before you start the version 17 server, rollback is trivial: the old data directory is untouched, so you simply start the version 15 service again. After you have started the 17 cluster on hard-linked files, that door is closed — the shared data files have been modified for 17 and the old cluster can no longer be trusted. From that point, your only true rollback is a restore from backup.

So the discipline is: take a verified backup before you begin, keep the old data directory in place and never run delete_old_cluster.sh until the new cluster has served real traffic for long enough to trust it, and treat “we have started 17” as the point of no return for the fast path. If the change window is unforgiving, rehearse the whole sequence — including the restore — on a copy first, the same way you would gate any risky change through a pipeline you actually enforce rather than one that exists on paper.

The upgrade itself takes minutes. The confidence to press enter comes from the rehearsal and the tested rollback — which is exactly the part every rushed upgrade skips, and exactly the part a supervisor will ask you to evidence after the one that went wrong.