How to run a PostgreSQL major upgrade workflow

A PostgreSQL major upgrade changes the database cluster that applications use, so the cutover has to be treated as a controlled migration instead of a routine package restart. The risky moment is the handoff from the old major version to the new one, where rollback depends on backups, the untouched old cluster, and proof that the upgraded server still serves the expected data.

On Debian and Ubuntu systems, postgresql-common manages versioned clusters with tools such as pg_lsclusters, pg_ctlcluster, pg_dropcluster, and pg_upgradecluster. The pg_upgradecluster –method=upgrade workflow uses the newer pg_upgrade binary under the wrapper, creates the target cluster, moves the original port to the new cluster, and keeps the old cluster stopped on another port until it is deliberately removed.

The example path upgrades a package-managed PostgreSQL 17 cluster named main to PostgreSQL 18 on Ubuntu 26.04 with the PostgreSQL Apt Repository available. Use the source and target versions that match the server being upgraded, install target-version extension packages before the cutover, and rehearse the same steps on a restored copy before production traffic is stopped.

Steps to run a PostgreSQL major upgrade workflow:

Freeze the source version, target version, cluster name, package source, maintenance window, rollback owner, and smoke-test query before changing packages.

List the existing clusters and record the source Ver and Cluster values.

$ sudo pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
17  main    5432 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log

Check free space on the filesystem that holds the data directory.
```
$ df -h /var/lib/postgresql
Filesystem      Size  Used Avail Use% Mounted on
overlay         118G   39G   74G  35% /
```
pg_upgradecluster –method=upgrade uses copy mode by default when it calls pg_upgrade, so the upgrade needs enough space for the new cluster data files unless a separately tested link, clone, or filesystem-specific mode is chosen.

Create a restricted backup directory for the pre-upgrade backup.

$ sudo install --directory --owner=postgres --group=postgres --mode=0700 /var/backups/postgresql

Create a full logical backup of cluster-wide roles and databases.
```
$ sudo -u postgres pg_dumpall --file=/var/backups/postgresql/pg_dumpall-2026-06-07.sql
```
pg_dumpall output contains database data, role definitions, and possible password hashes. Store it with the same care as production credentials.

Restrict the backup file permissions.

$ sudo chmod 0600 /var/backups/postgresql/pg_dumpall-2026-06-07.sql

Confirm the backup file exists and is readable only by its owner.
```
$ sudo ls -l /var/backups/postgresql/pg_dumpall-2026-06-07.sql
-rw------- 1 postgres postgres 4296 Jun  7 05:25 /var/backups/postgresql/pg_dumpall-2026-06-07.sql
```
A file listing does not prove recoverability. Restore the backup into a separate test target before relying on it for rollback. Related: How to restore a PostgreSQL database backup
Run the application smoke query on the source cluster before the cutover.
```
$ sudo -u postgres psql -d appdb -c "SELECT * FROM upgrade_check;"
 id |         note         
----+----------------------
  1 | before major upgrade
(1 row)
```
Use a real read-only query, migration marker, login check, or health endpoint that proves the application data expected after the upgrade.
List installed extensions before installing target packages.
```
$ sudo -u postgres psql -At -c "SELECT extname FROM pg_extension ORDER BY 1;"
plpgsql
```
Clusters that use extensions such as PostGIS need matching target-version packages before the upgrade, for example postgresql-18-postgis-3 when upgrading to PostgreSQL 18.

Install the target major-version server and client packages.

$ sudo apt-get install postgresql-18 postgresql-client-18
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following NEW packages will be installed:
  postgresql-18 postgresql-client-18
##### snipped #####
Setting up postgresql-client-18 (18.4-1.pgdg26.04+1) ...
Setting up postgresql-18 (18.4-1.pgdg26.04+1) ...

Use the package source already approved for the server. On Ubuntu 26.04, the distribution packages provide PostgreSQL 18; older source versions for a 17-to-18 rehearsal came from the PostgreSQL Apt Repository.

Remove an empty auto-created target cluster if the package install created one.
```
$ sudo pg_dropcluster --stop 18 main
```
Drop only a newly-created empty target cluster that was never used. Do not drop a target-version cluster that contains real data or already serves an application.
Stop writes from applications, jobs, and maintenance tasks before stopping the source cluster.

No clients should write to the old cluster while the upgrade is running. Pause application traffic, schedulers, ingestion workers, and manual sessions according to the maintenance plan.
Stop the source cluster.
```
$ sudo pg_ctlcluster 17 main stop
```

Run the major upgrade with pg_upgradecluster.

$ sudo pg_upgradecluster --method=upgrade 17 main
Upgrading cluster 17/main to 18/main ...
Restarting old cluster with restricted connections...
Stopping old cluster...
Creating new PostgreSQL cluster 18/main ...
##### snipped #####
Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok
Checking database connection settings                         ok
Checking for presence of required libraries                   ok
##### snipped #####
Upgrade Complete
----------------
Some statistics are not transferred by pg_upgrade.
##### snipped #####
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
    pg_dropcluster 17 main

Do not remove the old cluster from the completion message until application validation, monitoring, backup, and rollback decisions are finished.

Confirm the target cluster is online on the original port and the old cluster is stopped on a different port.

$ sudo pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
17  main    5433 down   postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

Check that PostgreSQL accepts local connections on the upgraded cluster.

$ sudo -u postgres pg_isready
/var/run/postgresql:5432 - accepting connections

Run the same smoke query against the upgraded cluster.

$ sudo -u postgres psql -d appdb -c "SELECT * FROM upgrade_check;"
 id |         note         
----+----------------------
  1 | before major upgrade
(1 row)

Confirm the upgraded server version.

$ sudo -u postgres psql -d appdb -c "SELECT current_setting('server_version') AS server_version;"
          server_version          
----------------------------------
 18.4 (Ubuntu 18.4-1.pgdg26.04+1)
(1 row)

Refresh planner statistics after the major upgrade.

$ sudo -u postgres vacuumdb --all --analyze-in-stages --verbose
vacuumdb: processing database "appdb": Generating minimal optimizer statistics (1 target)
INFO:  analyzing "public.upgrade_check"
INFO:  "upgrade_check": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
##### snipped #####

pg_upgrade transfers most optimizer statistics in recent releases, but some statistics are still missing after the upgrade. Rebuilding statistics gives the planner current information before normal traffic returns.

Check the upgraded cluster process status.

$ sudo pg_ctlcluster 18 main status
pg_ctl: server is running (PID: 7959)
/usr/lib/postgresql/18/bin/postgres "-D" "/var/lib/postgresql/18/main" "-c" "config_file=/etc/postgresql/18/main/postgresql.conf"

Reopen application traffic only after read, write, authentication, extension, job, monitoring, and backup checks pass.
Keep the old cluster stopped until the rollback window closes.

Rollback to the old cluster is simplest while copy mode has preserved the old cluster and the new cluster has not replaced the only valid recovery point. Link, clone, copy-file-range, and swap modes have different rollback boundaries and must be rehearsed before production use.
Remove the old cluster only after rollback is no longer needed.
```
$ sudo pg_dropcluster --stop 17 main
```
pg_dropcluster permanently deletes the old cluster data directory. Keep verified backups and post-upgrade monitoring evidence before running it.
Purge old major-version packages after the old cluster has been removed.
```
$ sudo apt-get purge postgresql-17 postgresql-client-17
```

Confirm that only the target cluster remains.

$ sudo pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log