How to upgrade PostgreSQL to a new major version safely

Why a major version needs a dump or pg_upgrade

Minor releases, such as 18.5 to 18.6, never change the storage format: install the new packages and restart. Major releases may change it, and a server refuses to start on a data directory from another major version. So the data has to move, by one of three routes: dump and restore, pg_upgrade, or logical replication to a new server (seconds of downtime, much more setup; not covered here).

Distributions move too: Ubuntu 24.04 ships PostgreSQL 16 and 26.04 ships 18; Debian 13 ships 17. You can jump several versions at once, but read the Migration section of the release notes for every version in between.

Pick a method

Link, clone and swap need the old and new data directories on the same filesystem. Worked example: du -sh /var/lib/postgresql/17/main says 40G and the disk has 30 GB free. Copy mode won't fit; link mode will, so the backup becomes your only way back once 18 starts.

Choose dump and restore when pg_upgrade can't do the job: columns of the reg* types it doesn't support (regproc, regoper and others), a --check failure you can't fix, or turning data checksums on as part of the move (pg_upgrade needs both sides to match). It also rewrites every table and index.

Install the new version and its extensions

Installing the new server doesn't touch the old cluster. On Ubuntu, the PostgreSQL project's apt repository has every supported version:

sudo apt install -y postgresql-common

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

sudo apt install -y postgresql-18

pg_lsclusters

Since postgresql-common 269, installing a new version creates no main cluster when other clusters exist. If you do see an empty 18 main, remove it with sudo pg_dropcluster 18 main --stop; pg_upgradecluster refuses to run while the target exists. Next, every extension package for 17 needs its 18 twin, for example postgresql-18-postgis-3 or postgresql-18-pgvector. Current postgresql-common checks this for you:

sudo pg_upgradecluster --check -v 18 17 main

It prints pg_upgradecluster pre-upgrade checks ok, or lines like postgresql-17-pgvector is installed, but postgresql-18-pgvector is missing.

Back up, then rehearse on the backup

Link and swap modes modify the old cluster, and any method can fail halfway. Dump the whole cluster with the new version's pg_dumpall (it reads servers back to 9.2):

sudo -u postgres /usr/lib/postgresql/18/bin/pg_dumpall -p 5432 -f /var/lib/postgresql/before-18.sql

Prove it restores by loading it into a throwaway 18 cluster on a spare port (it needs room for a full copy). That also rehearses the dump route and gives the application something to test against:

sudo pg_createcluster --port 5440 --start 18 rehearsal

sudo -u postgres /usr/lib/postgresql/18/bin/psql -X -p 5440 -d postgres -f /var/lib/postgresql/before-18.sql

One role "postgres" already exists error is expected and harmless. Compare row counts of key tables with production, point a staging copy of the app at port 5440, then remove it with sudo pg_dropcluster 18 rehearsal --stop. On a cloud VM, also take a snapshot just before the upgrade: the fastest way back for the whole machine. For per-database dumps and roles, see pg_dump and backing up roles.

Run pg_upgrade --check while the old server runs

pg_upgrade --check compares the clusters without changing data, even with the old server live. It needs an empty 18 cluster to compare against, and both must agree on data checksums, which 18's initdb turns on by default. Look first:

sudo -u postgres psql -p 5432 -Atc "SHOW data_checksums"

If it prints off, create the check cluster without them (if on, leave out everything after upgradecheck):

sudo pg_createcluster 18 upgradecheck -- --no-data-checksums

cd /var/lib/postgresql && sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade --check --link -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin -d /etc/postgresql/17/main -D /etc/postgresql/18/upgradecheck

• -b and -B are the old and new bin directories. Always run the new version's pg_upgrade.
• -d and -D are the configuration directories: under /etc/postgresql on Debian and Ubuntu, where pg_upgrade finds the data directories from them. Elsewhere, pass the data directories (SHOW data_directory).
• --link adds link mode's checks; use --clone, --copy-file-range or --swap if that is the mode you'll run.
• pg_upgrade writes scripts and temporary sockets in the current directory, so run it where the postgres user can write. It reads a live old server's port and socket from postmaster.pid.

Success ends with *Clusters are compatible*; a failure names the problem, often with a file listing the objects. Then remove the check cluster: sudo pg_dropcluster 18 upgradecheck.

Upgrade on Debian and Ubuntu with pg_upgradecluster

Stop the application or block its connections, then:

sudo pg_upgradecluster -v 18 -m upgrade 17 main

• It stops 17/main, creates 18/main with the old cluster's locale, encoding and checksum setting, and copies the configuration across, adjusted for 18.
• It runs pg_upgrade, moves 17/main to a free port with manual startup and gives 18/main the old port, so clients reconnect unchanged.
• It starts 18/main and runs vacuumdb through its analyze hook (for 18, the two commands below).
• -m upgrade copies files; -m link and -m clone mean --link and --clone; -j sets parallel jobs.
• Without -m, the default is dump: pg_dumpall into the new cluster while a temporary pg_hba.conf locks clients out of the old one. Slow but safe; for 18 it turns data checksums on.

It ends with Success. Please check that the upgraded cluster works. and a pg_lsclusters line per version. If pg_upgrade left output scripts, it prints their directory under /var/log/postgresql/.

Upgrade with plain pg_upgrade

Without postgresql-common, follow the same shape by hand:

1. initdb the new cluster with the old one's locale, encoding and checksum setting.
2. Install the new version's extension libraries, but don't run CREATE EXTENSION: pg_upgrade brings the definitions.
3. Copy over pg_hba.conf and postgresql.conf changes and any custom full-text search files.
4. Stop both servers and run pg_upgrade with the check's flags, minus --check.
5. Start the new server and follow the next section.

pg_upgrade ends with Upgrade Complete and writes delete_old_cluster.sh in the current directory. Run it only once the new cluster has proved itself.

After the upgrade

From 18, pg_upgrade keeps most optimizer statistics, but not extended statistics or the counters that drive autovacuum. Its final message asks for two commands (pg_upgradecluster runs them for you):

sudo -u postgres /usr/lib/postgresql/18/bin/vacuumdb -p 5432 --all --analyze-in-stages --missing-stats-only

sudo -u postgres /usr/lib/postgresql/18/bin/vacuumdb -p 5432 --all --analyze-only

Upgrading to 17 or older, nothing carries over: run vacuumdb --all --analyze-in-stages, which builds rough statistics fast and full ones after. Do the same after a dump and restore. --jobs parallelises either.

If extensions have newer versions, pg_upgrade writes update_extensions.sql (ALTER EXTENSION ... UPDATE for each) in the directory it ran from, which pg_upgradecluster prints. Run it as the superuser:

sudo -u postgres /usr/lib/postgresql/18/bin/psql -X -p 5432 -f update_extensions.sql

PostgreSQL 18 Migration notes to act on (read them beforehand; the first can stop an upgrade):

• Primary and foreign keys must use deterministic collations or the same nondeterministic one, or the schema restore (in pg_upgrade or a dump) fails.
• MD5 passwords are deprecated and setting one now warns. Find them with SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%'; and set those passwords again; the default password_encryption is scram-sha-256.
• If the cluster's default collation provider is ICU or builtin, reindex full-text search and pg_trgm indexes after pg_upgrade.
• AFTER triggers now run as the role that was active when the event was queued.
• VACUUM and ANALYZE now process inheritance children; add ONLY for the old behaviour.

When you are satisfied, remove the old cluster: sudo pg_dropcluster 17 main on Debian and Ubuntu, or delete_old_cluster.sh elsewhere.

Test the application

• Run the application's test suite against the rehearsal cluster, then smoke-test production before reopening it.
• Note SELECT count(*) for a few key tables before the upgrade and compare afterwards.
• Watch /var/log/postgresql/postgresql-18-main.log as traffic returns, and run your slowest queries with EXPLAIN ANALYZE once statistics are rebuilt.
• Update every machine that dumps this database. pg_dump refuses a server newer than itself, so nightly backups fail with aborting because of server version mismatch until they use an 18 client.
• Upgrade client tools too: the release notes warn that psql older than 18 may have \copy problems against an 18 server.

Rollback plan

With pg_upgradecluster, rolling back means giving 17 its port back:

sudo pg_ctlcluster 18 main stop

sudo pg_conftool 17 main set port 5432

sudo pg_ctlcluster 17 main start

Then set /etc/postgresql/17/main/start.conf back to auto, set 18's to manual (or drop the 18 cluster) and run sudo systemctl daemon-reload, so the right cluster starts at boot. Agree in advance how long rollback stays an option: writes made on 18 would be lost.

Common errors

Frequently asked questions

Can I upgrade from PostgreSQL 14 straight to 18?

Yes. pg_upgrade and pg_dumpall both handle servers back to 9.2. Read the Migration notes for 15, 16, 17 and 18 first.

How long does pg_upgrade take?

With --link or --clone, usually minutes. Copy mode takes as long as copying the data directory. Rehearse on a copy for your real number.

Does pg_upgrade enable data checksums?

No. Both clusters must match, so you keep what you had. A dump and restore into a new 18 cluster gets checksums by default.

Is a minor version upgrade the same process?

No. 18.5 to 18.6 needs only the new packages and a restart.