Upgrading PostgreSQL¶

Note

Upgrading PostgreSQL is a standard administration task and is outside the scope of support provided by CheckSec. Clients that choose the self-hosted option are responsible for maintaining and upgrading their PostgreSQL installation. This guide is provided as a convenience.

This guide covers how to upgrade PostgreSQL on the operating systems supported by Canopy. Canopy requires PostgreSQL 14 or later (see Supported operating systems).

Canopy’s minimum required version of PostgreSQL follows the official PostgreSQL end-of-life schedule. When a PostgreSQL version reaches EOL, support for it will be removed in the next Canopy minor release. We recommend always upgrading to the latest available major version of PostgreSQL. This gives you the longest window before the next required upgrade, and newer major versions include performance improvements, security fixes, and features that benefit Canopy. Check https://www.postgresql.org/support/versioning/ for the current list of supported versions and EOL dates.

The version of PostgreSQL available depends on your distribution:

Ubuntu: https://packages.ubuntu.com/search?keywords=postgresql&searchon=names&exact=1
RHEL / Rocky / Alma 9: Run dnf module list postgresql to see available versions.
RHEL / Rocky / Alma 10: Ships PostgreSQL 16. Module streams are no longer available. See https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/10/html/configuring_and_using_database_servers/using-postgresql for details.

Warning

We recommend using the version of PostgreSQL packaged by your distribution. Distro packages are easier to manage and receive security updates through the standard system update process. Non-distro installations will have non-standard paths and service configurations, making them harder to manage and upgrade.

Minor version upgrades¶

Minor version upgrades (e.g. 16.1 to 16.4) are low risk. The on-disk data format does not change between minor releases so no data migration is required.

Ubuntu¶

apt update
apt upgrade postgresql
systemctl restart postgresql

RHEL / Rocky / Alma / Oracle Linux¶

dnf upgrade postgresql-server
systemctl restart postgresql

Major version upgrades¶

Major version upgrades (e.g. 14 to 16) change the on-disk data format and require a data migration step. There are two approaches:

pg_upgrade (recommended): Faster, performs an in-place upgrade.
Dump and restore: Simpler to reason about but slower on large databases.

Ubuntu¶

Note

Ubuntu typically only ships one major version of PostgreSQL per release. A new major version of PostgreSQL is generally only available after upgrading to a newer Ubuntu release.

Before starting, make a backup of the database using the Alternative: dump and restore steps below.

Ubuntu provides the pg_upgradecluster wrapper which handles the pg_upgrade process.

After upgrading Ubuntu to a new release, the new version of PostgreSQL will have been installed automatically. The version numbers below are for an Ubuntu 22.04 to 24.04 upgrade (PostgreSQL 14 to 16) — adjust as needed.

Stop Canopy and all PostgreSQL instances:

systemctl stop canopy canopy-celery
systemctl stop postgresql

The Ubuntu upgrade will have created an empty cluster for the new version. This empty cluster must be dropped before upgrading:
```
pg_dropcluster 16 main --stop
```
Upgrade the cluster. The first argument is the old version number:
```
pg_upgradecluster 14 main
```
This migrates data from the old cluster to the new one.

Verify the new cluster is running on port 5432:

pg_lsclusters

The output should show the new version as online on port 5432 and the old version as down:

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

Start PostgreSQL:
```
systemctl start postgresql
```
Follow the Post-upgrade steps steps.
Once the upgrade has been verified, remove the old cluster:
```
pg_dropcluster 14 main
```
If upgrading from PostgreSQL older than 14, follow the Migrating from md5 to scram-sha-256 steps.

RHEL / Rocky / Alma / Oracle Linux¶

Before starting, make a backup of the database using the Alternative: dump and restore steps below.

After an in-place OS upgrade (e.g. RHEL 9 to 10 via leapp), the new version of PostgreSQL will have been installed automatically but the data directory still contains the old format. The postgresql-setup --upgrade command uses pg_upgrade internally to migrate the data. See https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/10/html/configuring_and_using_database_servers/using-postgresql for full details.

Stop Canopy and PostgreSQL:

systemctl stop canopy canopy-celery
systemctl stop postgresql

If you have not yet upgraded the OS, install the new PostgreSQL version first:

On EL 9, switch to the next module stream. For example, to upgrade from PostgreSQL 13 to 15:
```
dnf module reset postgresql
dnf module enable postgresql:15
dnf install postgresql-server postgresql-upgrade
```
On EL 10, module streams are not available. Only the default version of PostgreSQL is available. A newer major version requires an OS upgrade.

Warning

postgresql-setup --upgrade only supports upgrading from the previous module stream (e.g. 13 to 15, or 15 to 16). You cannot skip streams (e.g. 13 to 16). If you need to skip streams, use the Alternative: dump and restore method instead.
Run the upgrade:
```
postgresql-setup --upgrade
```
This renames /var/lib/pgsql/data to /var/lib/pgsql/data-old and migrates the databases to a new data directory.
Review /var/lib/pgsql/data-old/pg_hba.conf and transfer any custom authentication rules to the new /var/lib/pgsql/data/pg_hba.conf. At a minimum, Canopy requires a localhost entry for the canopy user:
```
host    all             all             127.0.0.1/32            scram-sha-256
```
If the database is on a separate host, ensure the appropriate network entries are also present. See Database settings for details.
Start PostgreSQL:
```
systemctl start postgresql
```
Follow the Post-upgrade steps steps.
Once the upgrade has been verified, remove the old data directory:
```
rm -rf /var/lib/pgsql/data-old
```
If upgrading from PostgreSQL older than 14, follow the Migrating from md5 to scram-sha-256 steps.

Alternative: dump and restore¶

If pg_upgrade is not suitable, a full dump and restore can be used instead. This approach works on all distributions but is slower on large databases.

Stop Canopy:
```
systemctl stop canopy canopy-celery
```
Dump the database. This file is your backup — keep it safe until the upgrade is verified:
```
sudo -u postgres pg_dump -F c -d canopy > canopy_db.sqlc
```
If you are only taking a backup, stop here and restart the services. The remaining steps are only needed to restore from a backup or complete a major version upgrade.
Upgrade PostgreSQL using the steps for your distribution (install new version, remove old version).
Restore the database:
```
sudo -u postgres pg_restore -d postgres --clean --create --if-exists --no-owner --no-privileges < canopy_db.sqlc
```
Review the output for errors. A small number of errors relating to the public schema are expected and can be ignored. Any other errors should be investigated. See Backups and recovery for more detail on the restore process.
Follow the Post-upgrade steps steps.

Docker Compose deployments¶

The Docker Compose setup in docker-production/ uses the official postgres image. The PostgreSQL data is persisted in ./postgresql/. Since Docker is not tied to distribution packages, we recommend always using the latest major version of PostgreSQL available on https://hub.docker.com/_/postgres.

Warning

Switching between any PostgreSQL Docker images — even between variants of the same major version (e.g. postgres:16-bookworm to postgres:16) — requires a dump and restore. The data directory is not guaranteed to be compatible across different images.

Minor version upgrades¶

Minor version updates (e.g. 16.1 to 16.4) are picked up automatically when pulling the latest image tag:

docker compose pull postgres
docker compose up -d postgres

Major version upgrades¶

Major version upgrades require a dump and restore because the on-disk data format changes between major versions.

Warning

Starting with PostgreSQL 18, the official Docker image changed the data directory layout to use version-specific subdirectories under /var/lib/postgresql/ instead of /var/lib/postgresql/data/. When upgrading to 18+, the volume mount in docker-compose.yml must be updated:

# Before (17 and earlier), data was mounted directly:
- ${PWD}/postgresql-data/:/var/lib/postgresql/data/

# After (18+), the mount must be one level up:
- ${PWD}/postgresql/:/var/lib/postgresql/

Older versions of the Canopy docker-compose.yml used the /var/lib/postgresql/data/ mount. If your setup still uses this, update it when upgrading to 18+.

See https://github.com/docker-library/postgres/pull/1259 for details.

Dump the database. This file is your backup — keep it safe until the upgrade is verified:

docker compose exec postgres pg_dump -U canopy -F c -d canopy > canopy_db.sqlc

Stop all containers:
```
docker compose down
```

Move the old data directory. The directory name depends on your docker-compose.yml:

# 18+ layout:
mv postgresql postgresql-old

# Older layout:
mv postgresql-data postgresql-data-old

Update docker-compose.yml:
- Change the image tag to the new version (e.g. postgres:17 to postgres:18).
- If upgrading to 18+, update the volume mount from:
```
${PWD}/postgresql-data/:/var/lib/postgresql/data/
```
  to:
```
${PWD}/postgresql/:/var/lib/postgresql/
```
Start the new PostgreSQL container:
```
docker compose up -d postgres
```
Restore the database:
```
docker compose exec -T postgres pg_restore -U canopy -d postgres --clean --create --if-exists --no-owner --no-privileges < canopy_db.sqlc
```
Review the output for errors. A small number of errors relating to the public schema are expected and can be ignored. Any other errors should be investigated.
Start everything:
```
docker compose up -d
```
Log in to Canopy and verify that everything is working as expected.

Once the upgrade has been verified, remove the old data directory:

# 18+ layout:
rm -rf postgresql-old

# Older layout:
rm -rf postgresql-data-old

Post-upgrade steps¶

After any major version upgrade:

Verify Canopy can connect to the database:
```
canopy-manage testdb
```
Run the postinstall process to ensure the database is migrated and maintenance tasks are run:
```
canopy-manage postinstall
```
Restart the Canopy services:
```
systemctl restart canopy canopy-celery
```
Check the PostgreSQL logs for any warnings or errors:
```
journalctl -u postgresql --since today
```

Creating the Canopy user and database¶

During a fresh installation, canopy-setup postgresql handles the full setup of PostgreSQL: installing packages, initialising the database cluster, configuring authentication, starting the service, and creating the Canopy user and database. If you need to create the user and database manually (e.g. after setting up PostgreSQL on a new server):

CREATE ROLE canopy WITH LOGIN PASSWORD 'canopy' NOSUPERUSER NOCREATEDB;
CREATE DATABASE canopy WITH OWNER canopy ENCODING 'UTF-8';

Note

If the database will be exposed to the network, use a strong password instead of the default. Update DATABASE_URL in /etc/canopy/canopy.ini to match.

Migrating from md5 to scram-sha-256¶

When using pg_upgrade to upgrade from a PostgreSQL version older than 14, the existing md5-hashed passwords are carried over as-is. PostgreSQL 14+ defaults to scram-sha-256, but pg_upgrade does not re-hash existing passwords. The canopy user’s password must be reset so that it is stored using the scram-sha-256 algorithm.

Reset the canopy database user’s password:
```
ALTER ROLE canopy WITH PASSWORD 'the new password';
```
Update the password in DATABASE_URL in /etc/canopy/canopy.ini to match. See Database settings for details.

Update pg_hba.conf to use scram-sha-256 instead of md5 for all relevant entries. The localhost entry should read:

host    all             all             127.0.0.1/32            scram-sha-256

Restart PostgreSQL:
```
systemctl restart postgresql
```
Verify Canopy can connect:
```
canopy-manage testdb
```
Restart Canopy:
```
systemctl restart canopy canopy-celery
```