Moving the database to a newer PostgreSQL version

Unimus 2.5 and older support only PostgreSQL 11 and older. Unimus 2.6 added support for newer versions. This page explains how to move an existing Unimus database to a newer PostgreSQL version, whether you're coming from 11 or from a later release.

Before you start, upgrade Unimus to 2.6 or newer. Older versions can't connect to PostgreSQL 12+.

For general background on PostgreSQL upgrades, see the official documentation: https://www.postgresql.org/docs/current/pgupgrade.html

Choosing a method

• pg_upgrade: use this when the old and new PostgreSQL run on the same host.
• Dump and restore: use this when the new database is on a different host or is a managed instance. pg_upgrade can't move data between hosts.

Check your target version before you start, because three of the steps below depend on it:

The examples below upgrade from PostgreSQL 11 on port 5432 to PostgreSQL 18 on port 5433. Change the versions and ports to match your setup.

Same host (pg_upgrade)

1. Install the new PostgreSQL version alongside the old one.

2. Create the new cluster and leave it empty and stopped. For a PostgreSQL 18 target, create it without data checksums, otherwise the upgrade won't start:

sudo pg_createcluster 18 main --port 5433 -- --no-data-checksums

Then check that both clusters use the same checksum setting:

sudo -u postgres /usr/lib/postgresql/11/bin/pg_controldata -D /var/lib/postgresql/11/main | grep -i checksum
sudo -u postgres /usr/lib/postgresql/18/bin/pg_controldata -D /var/lib/postgresql/18/main | grep -i checksum

Data page checksum version: 0 means disabled and 1 means enabled. The values must match. Versions up to 17 have checksums off by default, which is why an 18 target needs --no-data-checksums. If you enabled checksums on your old cluster, create the new one without that flag.

3. Convert md5 passwords (targets 14 and above). Run this on the old server. The md5 password hash migrates unchanged, but the new cluster's default authentication rules reject it, so Unimus can't log in after the upgrade.

Find roles that still have md5 passwords:

SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';

If the query lists unimus, set the password again so it's stored as SCRAM:

SET password_encryption = 'scram-sha-256';
ALTER USER unimus WITH PASSWORD '<same password>';

4. Stop Unimus. Leave PostgreSQL running.

sudo systemctl stop unimus

5. Back up the database. Use the new version's pg_dump with its full path, and write the dump to a directory the postgres user can access:

sudo mkdir -p /backup
sudo chown postgres:postgres /backup
sudo -u postgres /usr/lib/postgresql/18/bin/pg_dump -p 5432 -Fc -f /backup/unimus.dump unimus
sudo -u postgres /usr/lib/postgresql/18/bin/pg_dumpall -p 5432 --globals-only -f /backup/globals.sql

6. Remove OIDs (upgrading from 11 only). PostgreSQL 12 dropped support for WITH OIDS. Connect to the unimus database on the old cluster and run:

ALTER TABLE backup SET WITHOUT OIDS;
ALTER TABLE push_output_group SET WITHOUT OIDS;

Make a second backup after this, for example to /backup/unimus-no-oids.dump. The backup from step 5 still contains WITH OIDS, so it can only be restored into PostgreSQL 11.

If you're upgrading from 12 or later, skip this step.

7. Run the pre-check while the old cluster is still running. This step is optional, but we recommend it.

cd /tmp
sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
  -b /usr/lib/postgresql/11/bin -B /usr/lib/postgresql/18/bin \
  -d /var/lib/postgresql/11/main -D /var/lib/postgresql/18/main \
  -o "-c config_file=/etc/postgresql/11/main/postgresql.conf" \
  -O "-c config_file=/etc/postgresql/18/main/postgresql.conf" \
  -p 5432 -P 5433 --check

Run it as the postgres user from a directory that user can write to, which is why the command starts with cd /tmp. On Debian and Ubuntu you need the -o and -O options, because the configuration lives in /etc/postgresql/ and not in the data directory. The check stops at the first problem it finds, so fix that problem and run the check again until you see Clusters are compatible.

8. Run the upgrade. Stop the old cluster (the new one was never started):

sudo pg_ctlcluster 11 main stop

Then run the command from step 7 again without --check.

9. Reapply your configuration. pg_upgrade migrates only the data directory, not the config files. The new cluster's defaults are fine for a local Unimus, so copy over only the settings you changed yourself on the old cluster. Usually that means a non-default listen_addresses, plus the pg_hba.conf rules you need if Unimus runs on a different host.

If you want the new cluster to use the old port so nothing else needs changing, set port in /etc/postgresql/18/main/postgresql.conf now, while the old cluster is stopped.

10. Refresh the optimizer statistics.

sudo pg_ctlcluster 18 main start
sudo -u postgres /usr/lib/postgresql/18/bin/vacuumdb -p <new port> --all --analyze-in-stages

Check that the statistics were gathered:

sudo -u postgres psql -p <new port> -d unimus -c \
"SELECT relname, n_live_tup, last_analyze FROM pg_stat_user_tables WHERE relname IN ('backup','backup_segment','device');"

last_analyze should show a timestamp. If it's empty, the statistics weren't gathered.

11. Point Unimus at the new cluster and start it. If you didn't give the new cluster the old port in step 9, set database.port in /etc/unimus/unimus.properties to the new port. Then run:

sudo systemctl start unimus
tail -f /var/log/unimus/unimus.log

The log should show Database connection test passed, then Database schema updated successfully, Validating database data integrity and Unimus is ready.

12. Verify the data. Open a device's config history and diff two revisions. This is the only check that confirms backups are reassembled correctly from backup_segment. Then run one manual backup, which should produce no diff.

13. Remove the old cluster, but only after you've verified everything. Either run ./delete_old_cluster.sh, or run pg_dropcluster 11 main followed by apt purge postgresql-11.

Until you remove it, you can roll back by starting the old cluster again and pointing Unimus back at it. This works only if pg_upgrade ran in the default copy mode.

Different host or managed instance (dump and restore)

1. Stop Unimus.

sudo systemctl stop unimus

2. Dump the database with the target version's pg_dump. Run this from a machine that has the new version's client tools installed:

sudo mkdir -p /backup
sudo chown postgres:postgres /backup
sudo -u postgres /usr/lib/postgresql/<new version>/bin/pg_dump -h <old host> -p <old port> -U unimus -Fc -f /backup/unimus.dump unimus

3. Create the role and database on the target, then restore. Restore as the unimus role so object ownership is set correctly.

CREATE USER unimus WITH PASSWORD '<password>';
CREATE DATABASE unimus OWNER unimus;

sudo -u postgres /usr/lib/postgresql/<new version>/bin/pg_restore --no-owner -h <new host> -p <new port> -U unimus -d unimus /backup/unimus.dump

The --no-owner option is required. Without it, the restore fails on the dump's ALTER SCHEMA public OWNER TO postgres.

4. Refresh the optimizer statistics on the new database:

sudo -u postgres /usr/lib/postgresql/<new version>/bin/vacuumdb -h <new host> -p <new port> -U unimus --analyze-in-stages -d unimus

When run as unimus, this prints permission denied to analyze warnings for about a dozen shared system catalogs. You can ignore them. Only a superuser can analyze those catalogs, and the Unimus tables get analyzed anyway.

5. Point Unimus at the new database and start it. Set database.host and database.port in /etc/unimus/unimus.properties to the new server, then start Unimus. Verify it the same way as in the same-host procedure: a clean boot log, a config-history diff between two revisions, and one manual backup that produces no diff.

This method needs no OID handling, even when you're coming from 11. pg_dump from version 12 onward drops OIDs automatically.

Common errors