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 (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!