PostgreSQL pg_upgradecluster

PostgreSQL Logo

If the version of a PostgreSQL server changes, it is required to upgrade the version manually. This can e.g. happen, if you do a release upgrade of your Linux distribution. The below description explains how to use PostgreSQL pg_upgradecluster to upgrade PostgreSQL 9.6 from Debian Stretch (Debian 9) to PostgreSQL 11 of Debian Buster (Debian 10).

During the release upgrade, the distirbution installer installs a second version of PostgreSQL in parallel. To check which versions are available, just execute (as user postgres):


The result can look like as follows:

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

The already running PostgreSQL 11 cluster is empty, before you can upgrade the 9.6 cluster to 11, you need to stop and drop the 11 cluster.

pg_dropcluster --stop 11 main

No you can start PostgreSQL pg_upgradecluster

pg_upgradecluster 9.6 main

Once the upgrade has been executed, the 9.6 will be stopped but is still available. If you need to start it later on again, be aware the config has been changed and the cluster will start on port 5433 instead. The newly created cluster in version 11 will run on port 5432 as usual.

Once you have tested everything and the new cluster is running as wanted, you can drop the the 9.6 cluster.

pg_dropcluster --stop 9.6 main

You can then also uninstall the PostgreSQL 9.6 packages.

Side note for update of Debian Stretch: A change in glibc has effects on the indices of PostgresSQL. If you directly upgrade your cluster to PostgreSQL 11, there is nothing to do because the upgrade will reindex the new cluster automatically.

If you intent to run the 9.6 cluster for a while, make sure you reindex your database rightaway after you have upgraded glibc

sudo -u postgres reindexdb --all

Details can be found in the PostgreSQL Wiki.