HOWTO: PostgreSQL version upgrade

en | ru

This section describes how to upgrade PostgreSQL between major versions (e.g., from version 11 to version 16).

Upgrading within a version (e.g., from version 16.1 to version 16.2) is automatic and requires no additional steps.

We recommend reading the official upgrade instructions at postgresql.org.

In the Debian distribution and those based on it, there is a package postgresql-common that is installed automatically when the PostgreSQL package is installed. Postgresql-common includes tools to simplify working with PostgreSQL database clusters. This includes updating database clusters.

A database cluster in PostgreSQL is a server instance together with all the maintained databases, not a distributed system as it may seem.

The following example will show how to upgrade a database cluster from version 11 to version 16 using the tools in the postgresql-common package.

Upgrade order

1. Stop USERSIDE.
2. Upgrade the current version of postgresql within the major version (e.g., from 11.5 to 11.22).
3. Upgrade the postgis extension to the latest version (e.g. from 2.5 to 3.3.4). Also be sure to update the extension in the database itself.
4. Install a new version of postgresql without uninstalling the old one (e.g. 16.1).
5. Make a backup copy of the database and settings
6. Delete the database cluster created automatically by the script when installing the new version
7. Upgrade the database cluster of the old version to the cluster of the new version
8. Check USERSIDE operation
9. Remove the old version database cluster, obsolete packages and spare backups

Allocate time for this operation. It can take quite a long time depending on the size of the database and the performance of the server. Also, make sure you have the free space you will need to upgrade the cluster (at least as much as the current cluster, but more is better).

View video tutorial on asciinema.

Step-by-step instructions

Stop USERSIDE

sudo supervisorctl stop all
sudo systemctl stop php8.1-fpm

The php version is for example. Use the one you are using.

Comment out the startup of all modules and userside in crontab.

Upgrade the current version of postgresql within the major version

Check the current versions. Upgrade the current PostgreSQL and PostGIS packages:

sudo -u postgres psql -d userside -A -t -c "SELECT VERSION()"
sudo -u postgres psql -d userside -A -t -c "SELECT PostGIS_full_version()"

sudo apt update
sudo apt install --only-upgrade -y postgresql-11 postgresql-11-postgis-2.5

Instead of 11 and 2.5, specify the versions you actually have installed.

Upgrade the postgis extension to the latest version

Be sure to upgrade the extension in the database as well!

sudo apt install -y postgresql-11-postgis-3

sudo -u postgres psql -d userside -A -t -c "ALTER EXTENSION postgis UPDATE"
sudo -u postgres psql -d userside -A -t -c "SELECT PostGIS_full_version()"

Install a new version of postgresql without uninstalling the old one

sudo apt install -y postgresql-16 postgresql-16-postgis-3

Make a backup of the database and settings

The number 11 in the filename is the PostgreSQL version number to which this backup belongs.

sudo -u postgres pg_dump --no-acl -Fp -Z 5 userside --file=/backup/userside_emergency_11.sql.gz
sudo -u postgres pg_dump --no-acl -Fc userside --file=/backup/userside_emergency_11.dump
sudo tar czf /backup/postgres_config_emergency_11.tgz -C /etc/postgresql/11/main .

The /backup directory must exist and must be writable by all users.

Remove the database cluster created automatically by the script when installing a new version

Using the pg_lsclusters utility from postgresql-common, see what your database clusters look like:

sudo pg_lsclusters

The output of the command will be approximately as follows:

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

The 16/main cluster was created automatically by the postgresql installation script. It needs to be stopped and deleted.

sudo pg_dropcluster 16 main --stop

Running the pg_lsclusters command again should show that you have one 11/main cluster left.

Perform an upgrade of the old version database cluster to the new version database cluster

Stop the postgresql service

sudo systemctl stop postgresql

Start the 11/main database cluster upgrade process using the pg_upgradecluster utility from the postgresql-common package. This may take a lot of time and disc space.

sudo pg_upgradecluster -m upgrade 11 main

After the procedure is complete, start the postgres service

sudo systemctl start postgresql

Check what your database clusters look like

sudo pg_lsclusters

The old version must be in down mode. The output of the command will be approximately as follows:

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

Verify the operation of USERSIDE

First run the commands displaying the versions to verify that the database is now running on the new version database cluster:

sudo -u postgres psql -d userside -A -t -c "SELECT VERSION()"
sudo -u postgres psql -d userside -A -t -c "SELECT PostGIS_full_version()"

You should see version 16 of postgresql and version 3.3.4 of postgis. Maybe by the time you read these lines, the postgis version will be newer.

Now start the FPM service and go to USERSIDE through your browser.

sudo systemctl start php8.1-fpm

Since the other services are stopped, you will probably see errors related to them, but the USERSIDE interface should open without database connection errors. If everything is successful, then uncomment the previously commented lines in crontab and start the external services under supervisor control:

sudo supervisorctl start all
sudo supervisorctl status

Remove the old version database cluster, obsolete packages, and spare backups

If USERSIDE is running correctly, and there are no database communication errors, you can remove the old cluster and packages. You can also optionally delete the emergency backups or wait a few days to make sure they are not needed.

sudo pg_dropcluster 11 main --stop
sudo apt purge -y postgresql-11 postgresql-11-postgis-2.5 postgresql-11-postgis-3
sudo apt autoremove -y
sudo rm /backup/userside_emergency_11* /backup/postgres_config_emergency_11.tgz
sudo rm -rf /var/lib/postgresql/11/main