Page History

PostgreSQL details

Unimus 2.5 and older support only PostgreSQL 11 and belowolder. From Unimus 2.6 onward, all newer PostgreSQL versions are supported. If you're on PostgreSQL 11 and want to move added support for newer versions. This page explains how to move an existing Unimus database to a newer PostgreSQL version, follow the steps below.For background on upgrading PostgreSQL in generalwhether 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

Migrating from PostgreSQL 11 to 12+

There are two ways to do this:

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. Three , because three of the steps below depend on it:

The examples below use PostgreSQL upgrade from PostgreSQL 11 on port 5432 to PostgreSQL 18 on port 5433 as the target. Change the version versions and port 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, or otherwise the upgrade won't start:

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

...

Data page checksum version: 0 means disabled , and 1 means enabled. The values must match. Version 11 has 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 as it isunchanged, but the new cluster's default authentication rules reject it, so Unimus can't log in after the upgrade.

Find roles with that still have md5 passwords:

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

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

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

...

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 has can access to:

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:

...

Make a second backup after this, for example to /backup/unimus-no-oids.dump. The backup from step 5 still contains WITH OIDS and , 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 with while the PostgreSQL 11 old cluster is still running. This step is optional, but we recommend it.

...

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

...

9. Reapply your configuration. Only pg_upgrade migrates only the data directory is migrated, 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. Typically Usually that means a non-default listen_addresses, and plus the pg_hba.conf rules needed you need if Unimus runs on a different host.

...

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

Check that they 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');"

...

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

In the The log you should see 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 tests that confirms backups are reassembled correctly from backup_segment. Then run one manual backup. It , 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 do thisremove 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

...

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

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

...

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, and verify . 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.

You don't need to handle OIDs hereThis method needs no OID handling, even when you're coming from 11. pg_dump from version 12 onward drops them OIDs automatically.

Common errors