Upgrading the PostgreSQL version for GNU/Linux¶

This page is an addition to the installation on GNU/Linux instructions and will help you upgrade the PostgreSQL database management system used in the scanner to version 16. This version includes the TimescaleDB extension necessary for correct operation of the service. If your PostgreSQL version matches the required one, you will only need to install the extension, if it is not installed yet.

List of affected versions¶

Follow these instructions when upgrading the scanner from version v25.9 and lower to version v25.10 and higher.

Migration¶

Notes

In the instructions, migration is performed using the pg_dump built-in PostgreSQL command. Other upgrade methods are also permitted.
All commands in the instructions are run by a user with the “administrator” role.

Create a solidpoint_backend database backup.

pg_dump -U postgres solidpoint_backend > backend_dump.sql

Create a fuchsia database backup.

pg_dump -U postgres fuchsia > fuchsiad_dump.sql

Delete the solidpoint_backend database.
```
dropdb -U postgres solidpoint_backend
```
Delete the fuchsia database.
```
dropdb -U postgres fuchsia
```
Upgrade the PostgreSQL version to the required one.
1. Stop the old cluster.
```
systemctl stop postgresql
```
2. Update the packages.
```
apt update
apt upgrade
apt install postgresql-16
```
3. Identify the old and new versions of PostgreSQL, as well as the cluster name.
```
pg_lsclusters
```
4. Use the pg_upgrade tool.
```
pg_upgradecluster <old_version> <cluster_name>
```
5. Start a new cluster.
```
systemctl start postgresql
```
6. Delete the old cluster.
```
pg_dropcluster <old_version> <cluster_name>
```

Recreate the deleted databases.

su postgres -c psql
> CREATE DATABASE solidpoint_backend;
> CREATE DATABASE fuchsia;
> GRANT ALL PRIVILEGES ON DATABASE fuchsia TO fuchsia;
> GRANT ALL PRIVILEGES ON DATABASE solidpoint_backend TO backend;
> quit

Import the solidpoint_backend backup.

psql -U postgres -d solidpoint_backend < backend_dump.sql

Import the fuchsia backup.

psql -U postgres -d fuchsia < fuchsiad_dump.sql

Restart PostgreSQL.
```
systemctl restart postgresql
```