Databases

Upgrade PostgreSQL with pg_upgrade: A Safe Major Version Upgrade

Move a PostgreSQL cluster to a new major version with pg_upgrade, including the compatibility check, the upgrade run and the post-upgrade analyze.

AdvancedLinux server6 min read

Upgrading PostgreSQL with pg_upgrade moves an existing cluster to a new major version without a dump and restore, so downtime is measured in minutes rather than hours. pg_upgrade rewrites the system catalogs and then copies or hard-links the data files, which means you install the new server binaries first, run a compatibility check, and only then stop the old cluster. This guide covers a major version upgrade on a Linux VPS, from the pre-flight check to the final analyze.

Before you start

You need the following in place:

  • A Linux VPS running PostgreSQL on Ubuntu 22.04/24.04, Debian 12, AlmaLinux 8–9 or Rocky 8–9.
  • Root or sudo access, and the ability to run commands as the postgres user.
  • The new PostgreSQL major version installed from the official PGDG packages. If you have not done this yet, follow Install the latest PostgreSQL from the official PGDG repository.
  • Enough free disk space for a full copy of the cluster, unless you use --link.
  • A recent logical backup, which you will take in step 3.

Warning: pg_upgrade is a one-way operation. Once the new cluster is running and you have written to it, you cannot roll back to the old binaries without restoring from backup. Take the backup in step 3 and keep it until the new cluster has been stable for a few days.

Step 1: Record the current cluster

Write down what you are upgrading from before anything changes. On Debian and Ubuntu, pg_lsclusters shows every cluster, its version, port and status:

Bash
pg_lsclusters

Then confirm the data directory, port and installed extensions:

Bash
sudo -u postgres psql -c "SELECT version();"
sudo -u postgres psql -c "SHOW data_directory;"
sudo -u postgres psql -c "SHOW port;"
sudo -u postgres psql -c "\dx"

The extension list matters. pg_upgrade does not upgrade extension binaries for you, so every extension shown here must be installed for the new major version before you run the upgrade. Check each vendor's documentation for the correct package name on your distribution.

Step 2: Install the new server and stop its default cluster

Install the new major version alongside the old one. On Debian and Ubuntu:

Bash
sudo apt update
sudo apt install postgresql-16

On AlmaLinux and Rocky:

Bash
sudo dnf install postgresql16-server

The Debian and Ubuntu packages create a fresh, empty cluster and start it on a different port, usually 5433. pg_upgrade needs that new cluster stopped, so shut it down:

Bash
sudo pg_ctlcluster 16 main stop

Do not delete the new data directory. pg_upgrade expects an initialized but empty cluster.

Step 3: Take a backup

A logical backup is your rollback path. Dump every database, roles and globals:

Bash
sudo -u postgres pg_dumpall -f /var/backups/pg-preupgrade.sql

For large clusters, a per-database pg_dump in custom format restores faster and lets you parallelise. See Back up and restore PostgreSQL with pg_dump and pg_restore for the full workflow. Copy the dump off the VPS as well, so a disk problem cannot take both copies.

Step 4: Run the pg_upgrade compatibility check

pg_upgrade ships a --check mode that inspects both clusters and reports problems without changing anything. Run it as the postgres user from a directory that user can write to:

Bash
cd /var/lib/postgresql
sudo -u postgres /usr/lib/postgresql/16/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/15/main \
  --new-datadir=/var/lib/postgresql/16/main \
  --old-bindir=/usr/lib/postgresql/15/bin \
  --new-bindir=/usr/lib/postgresql/16/bin \
  --old-port=5432 \
  --new-port=5433 \
  --check

Adjust the version numbers and paths to match your setup. Expect output ending in *Clusters are compatible*. If it reports missing libraries, incompatible locale or collation settings, or a non-empty new cluster, fix those before continuing — the same checks run again during the real upgrade and will abort it.

Tip: Run the check twice. The first pass can report issues that only appear after it has inspected the old cluster's catalog.

Step 5: Stop the old cluster and upgrade

Stop PostgreSQL cleanly so the data files are consistent:

Bash
sudo pg_ctlcluster 15 main stop

Confirm nothing is listening on the old port, then run the upgrade without --check. Add --link to hard-link the data files instead of copying them, which is much faster and needs almost no extra disk space:

Bash
cd /var/lib/postgresql
sudo -u postgres /usr/lib/postgresql/16/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/15/main \
  --new-datadir=/var/lib/postgresql/16/main \
  --old-bindir=/usr/lib/postgresql/15/bin \
  --new-bindir=/usr/lib/postgresql/16/bin \
  --old-port=5432 \
  --new-port=5433 \
  --link

Warning: With --link, the old and new clusters share the same data files. If the upgrade fails partway through, the old cluster is no longer usable and you must restore from the backup taken in step 3. Only use --link when you have a verified dump.

When it finishes, pg_upgrade prints the exact commands for the next step, including ./analyze_new_cluster.sh and ./delete_old_cluster.sh.

Step 6: Start the new cluster and finish

The new cluster still has the temporary port from the package default. Edit /etc/postgresql/16/main/postgresql.conf and set port = 5432, then start it:

Bash
sudo pg_ctlcluster 16 main start
sudo pg_lsclusters

Rebuild planner statistics so queries do not run on stale estimates:

Bash
sudo -u postgres vacuumdb --all --analyze-in-stages

Finally, update every extension to its new version:

SQL
ALTER EXTENSION postgres_fdw UPDATE;

Run that for each extension listed in step 1. Leave the old cluster's data directory in place until you are confident, then remove it with the generated delete_old_cluster.sh script.

Check it works

Connect and confirm the version and a few real queries:

Bash
sudo -u postgres psql -c "SELECT version();"
sudo -u postgres psql -d yourdb -c "SELECT count(*) FROM your_table;"

Then check the log for errors:

Bash
sudo tail -n 50 /var/log/postgresql/postgresql-16-main.log

Point your application at the database and exercise the main read and write paths. If your app connects remotely, confirm the rules in Configure PostgreSQL Remote Access with pg_hba.conf still match, because a new cluster starts with a fresh pg_hba.conf copied from the package defaults.

Troubleshooting

  • could not load library... No such file or directory — an extension is missing for the new version. Install the matching package for the new major version, then rerun the --check pass.
  • The source cluster was not shut down cleanly — the old cluster was still running or crashed. Start it with the old binaries, stop it with pg_ctlcluster 15 main stop, and rerun.
  • New cluster is not empty — you pointed --new-datadir at a cluster that already holds data. Recreate it with initdb or reinstall the package so it is initialized but empty.
  • could not open file... Permission denied — pg_upgrade was run as root or from a directory the postgres user cannot write. Run it with sudo -u postgres from /var/lib/postgresql.
  • Connections fail after the upgrade — the new cluster is still on port 5433, or pg_hba.conf differs from the old one. Set the port to 5432 and copy the old authentication rules across.

Next steps

Was this guide helpful?

Still stuck?

Ask MdmHost AI for a quick answer, or open a ticket and our team will take a look. We are here 24/7.

Ask MdmHost AIOpen a ticket

Related guides

Databases

Create a MySQL database and user in cPanel

Create a MySQL database, a database user and the right privileges in cPanel with the Database Wizard or Manage My Databases, then connect your app with the correct details.

2 min read