Upgrade Postgresql version 14 to 16 for Ubuntu 22.04

Based on https://medium.com/@gembit.soultan/how-to-upgrade-postgresql-15-to-postgresql-16-using-pg-upgradeclusters-in-ubuntu-22-04-c9f279c5d3ab

Superseded (2026-06-10): prod is now containerized PG18 on Kamal/Latitude,
not apt-installed PostgreSQL on Vultr. This pg_upgradecluster 14→16 walkthrough
targets the decommissioned Vultr apt-postgres clusters and is kept only as a
historical reference. The live 16→18 upgrade used an in-place pg_upgrade --link
against the containerized cluster — see
doc/tasks/202606061949_PG18_UPGRADE_STRATEGY.md and the postgres-replication
skill.

As root

apt -y install postgresql-16 postgresql-server-dev-16 postgresql-16-repack

Note that if the above fail, you need to probably add the repository, see appendix A

View the two clusters side by side, both should be online, notice the new one will have a different port
pg_lsclusters

Stop the new cluster
sudo pg_dropcluster 16 main --stop

Upgrade the old, using link method and upgrade, fastest, but not recoverable (ok if you have a backup or snapshot)

sudo pg_upgradecluster -m upgrade -k 14 main

The custom.conf file is not copied automatically, let's do it

cp /etc/postgresql/14/main/conf.d/custom.conf /etc/postgresql/16/main/conf.d/

Restart it because we copied the configuration file above

pg_ctlcluster restart 16 main

Check the new cluster is running

pg_lsclusters

Check you can connect to it

su -c 'psql -d heatwave' postgres

Run a query in psql. e.g select count(*) from parties;

Now that we're sure its good

/usr/lib/postgresql/16/bin/vacuumdb --all --analyze-in-stages

Drop the old cluster:

pg_dropcluster 14 main

Note, the following file is stored in a weird directory, we have to find where it is first before we can call it

find / -iname delete_old_cluster.sh

This will return a directory, e.g. /var/log/postgresql/pg_upgradecluster-14-16-main.ejP1
Switch to that dir then run the command

cd /var/log/postgresql/pg_upgradecluster-14-16-main.ejP1
./delete_old_cluster.sh

In the same directory we will have a file we can run to update extensions

su -c "psql f /var/log/postgresql/pg_upgradecluster-14-16-main.ejP1/update_extensions.sql" postgres

Get some basic analysis

/usr/lib/postgresql/16/bin/vacuumdb --all --analyze-in-stages

Uninstall remnants of the old install

sudo apt purge postgresql-14 postgresql-client-14 postgresql-14-repack

At this point. Everything should be back to normal. Monitor postgresql log for unexpected failures.

Finally check any packages that is an older version and uninstall them

apt list --installed | grep postgresql

Appendix A: Installing postgres repositories

If you cannot install postgresql 16 because the package is not found, you need to add the repositories

apt -y install gnupg2 wget
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add
apt -y update