PostgreSQL Upgrade

Prerequisites

dist-upgrade (or something similar) was executed and installed a new version of PostgreSQL side-by-side with the one from the previous operating system release. This articles assumes an upgrade from version 9.5 to 10 after an Ubuntu upgrade from version 16.04 to 18.04.

Upgrade

pg_lsclusters shows an online cluster for every version of PostgreSQL running on two different ports:

Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
10  main    5433 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

The SystemD service "postgresql" is responsible for both instances.

pg_dropcluster 10 main --stop

In the output of pg_lsclusters, the second entry would disappear now.

systemctl stop postgresql
pg_upgradecluster -m upgrade 9.5 main
pg_dropcluster 9.5 main
systemctl start postgresql

Now, pg_lsclusters would only show the new instance running version 10:

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

Cleaning up

apt purge postgresql-client-9.5 postgresql-doc-9.5
apt autoremove
apt autoclean

Credits

• Stackoverflow: Upgrade PostgreSQL from 9.6 to 10.0 on Ubuntu 16.10
• Documentation → PostgreSQL 12: pg_upgrade
• Ubuntu Manpage pg_upgradecluster