HOWTO: PostgreSQL version upgrade

en | ru

This section describes the procedure for upgrading PostgreSQL between major versions (for example, from version 11 to version 17).

An upgrade within a version (for example, from version 17.1 to 17.2) happens automatically and does not require additional actions.

We recommend that you familiarize yourself with the official upgrade instructions on the postgresql.org website.

In the Debian distribution and those based on it, there is a postgresql-common package, which is installed automatically when the PostgreSQL package is installed. The postgresql-common package includes tools that simplify working with PostgreSQL database clusters, including upgrading DB clusters.

A database cluster (Database cluster) in PostgreSQL refers to a server instance along with all the databases it serves, and not a distributed system as one might think.

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

Upgrade procedure

1. Stop USERSIDE.
2. Upgrade the current version of the postgresql package within its major version (e.g., from 11.5 to 11.22).
3. Upgrade the postgis package version to the latest version (e.g., from 2.5 to 3.3.4).
4. Update the PostGIS extension in all databases of the cluster.
5. Install the new version of the postgresql package without removing the old one (e.g., 17.1).
6. Make a backup copy of the database and settings.
7. Delete the DB cluster that was created automatically by the script during the new version installation.
8. Perform the upgrade of the old version DB cluster to the new version cluster.
9. Check if USERSIDE is working correctly.
10. Delete the old version DB cluster, obsolete packages, and backup copies.

Set aside time for this operation. It can take quite a long time depending on the size of the database and the server's performance. Also, make sure you have enough free disk space, which will be needed for the cluster upgrade (at least as much as the current cluster occupies, but more is better).

Watch the video instruction on asciinema.

Step-by-step instructions

The SQL queries with the SELECT operator provided below are for reference to determine the currently installed versions.

Stop USERSIDE

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

The PHP version is given as an example. Use the one that is used on your system.

Comment out the startup of all modules and userside in crontab. If you are using satellites, stop the services on them as well.

Upgrade the current postgresql version within the major version

Check the current versions of the PostgreSQL and PostGIS system packages. Upgrade the currently installed versions of PostgreSQL and PostGIS to the latest versions within their major versions, and also, if the current version of PostGIS is below 3, additionally install the PostGIS 3 package for your version of PostgreSQL. If you already have PostGIS 3 installed, you only need to update it to the latest version.

# get the postgresql version and the postgis extension version for the userside database
sudo -u postgres psql -A -t -c "SELECT VERSION()"
sudo -u postgres psql -d userside -A -t -c "SELECT PostGIS_full_version()"

# update the current package versions
sudo apt update
sudo apt install --only-upgrade -y postgresql-11 postgresql-11-postgis-2.5
# only if the current postgis version is lower than 3, additionally install postgis-3 for the current postgres version
sudo apt install -y postgresql-11-postgis-3

Instead of 11 and 2.5, of course, specify the versions that are actually installed on your system.

Upgrade the postgis extension to the latest version

After updating the postgis package, you must accordingly update the postgis extension itself in the databases where this extension is present. You may have, for example, a copy of the userside database for testing - do not forget about it.

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()"

The second command will display the current version of the PostGIS extension after the update.

Upgrading from PostGIS 2.5 to PostGIS 3.x

If the previous SELECT PostGIS_full_version() query displays information like this:

POSTGIS="3.3.4 3.3.4" [EXTENSION] . . .
. . .
[UNPACKAGED!] (raster procs from "2.5.5 r0" need upgrade)

Then the following information is for you. Otherwise, skip this section.

There is some difference between PosGIS versions 2 and 3 that requires additional actions during the upgrade.

# the same query twice
sudo -u postgres psql -d userside -A -t -c "SELECT postgis_extensions_upgrade()"
sudo -u postgres psql -d userside -A -t -c "SELECT postgis_extensions_upgrade()"
# remove the raster extension, which was previously part of the postgis extension
sudo -u postgres psql -d userside -A -t -c "DROP EXTENSION postgis_raster"

Install new package versions

Also, remove the old postgis version 2 package if it was installed.

sudo apt install -y postgresql-17 postgresql-17-postgis-3
sudo apt purge -y postgresql-11-postgis-2.5

Make a backup copy of the database and configuration

The cluster migration tool does this absolutely safely, but extra caution will not be superfluous. Make temporary backup copies of the necessary databases and configuration, which can be safely deleted at the end of the update.

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

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

Delete the automatically created DB cluster

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

sudo pg_lsclusters

The command output 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
17  main    5433 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17.log

The 17/main cluster was created automatically by the postgresql installation script and uses the new port 5433. It needs to be stopped and deleted — it is not needed, as a cluster migration procedure will be performed, which will create a new cluster automatically.

sudo pg_dropcluster 17 main --stop

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

Perform the upgrade of the old version DB cluster to the new version cluster

Stop the postgresql service

sudo systemctl stop postgresql

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

sudo pg_upgradecluster 11 main

After the upgrade operation is complete, you will see the new state of the clusters (the old version should be in down mode and port 5432 is now assigned to the new cluster 17/main):

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
17  main    5432 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17.log

Start the postgres service

sudo systemctl start postgresql

Check USERSIDE operation

First, run the commands that display the versions to make sure that the database is now running on the new version DB cluster:

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

You should see postgresql version 17 and postgis version 3.x.x.

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

sudo systemctl start php8.3-fpm

Since 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 managed by supervisor, including on the satellite:

sudo supervisorctl start all
sudo supervisorctl status

Cleanup after a successful upgrade

If USERSIDE is working correctly and there are no database interaction errors, you can delete the old cluster and packages. You can also, if you wish, delete the emergency backups or wait a few days to be absolutely sure they will not be needed.

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