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.
When installing on Astra Linux, the pg_dump, dropdb, and psql commands must be executed by a user with the postgres role. For this, use the sudo -u postgres prefix.

Debian Astra Linux

Assign a directory for storing backups

sudo mkdir -p /var/backups/pg && sudo chown postgres:postgres /var/backups/pg

Create a solidpoint_backend database backup.

pg_dump -U postgres solidpoint_backend > /var/backups/pg/backend_dump.sql

Create a fuchsia database backup.

pg_dump -U postgres fuchsia > /var/backups/pg/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 USER backend WITH PASSWORD '${POSTGRESQL_PASSWORD}';
> GRANT ALL PRIVILEGES ON DATABASE solidpoint_backend TO backend;
> \c solidpoint_backend;
> GRANT ALL ON SCHEMA public TO backend;
> CREATE DATABASE fuchsia;
> CREATE USER fuchsia WITH PASSWORD '${POSTGRESQL_PASSWORD}';
> GRANT ALL PRIVILEGES ON DATABASE fuchsia TO fuchsia;
> \c fuchsia;
> GRANT ALL ON SCHEMA public TO fuchsia;
> 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
```

Assign a directory for storing backups

sudo mkdir -p /var/backups/pg && sudo chown postgres:postgres /var/backups/pg

Create a solidpoint_backend database backup.

sudo -u postgres pg_dump solidpoint_backend > /var/backups/pg/backend_dump.sql

Create a fuchsia database backup.

sudo -u postgres pg_dump fuchsia > /var/backups/pg/fuchsiad_dump.sql

Delete the solidpoint_backend database.

sudo -u postgres dropdb solidpoint_backend

Delete the fuchsia database.
```
sudo -u postgres dropdb fuchsia
```

Upgrade the PostgreSQL version to the required one.

Stop the old cluster.
```
systemctl stop postgresql
```
Update the packages.
```
apt update
apt install postgresql-16
```
Identify the old and new versions of PostgreSQL, as well as the cluster name.
```
pg_lsclusters
```

Use the pg_upgrade tool.

pg_upgradecluster <old_version> <cluster_name>

Comment out the unsupported configuration parameters.

sed -i '/^[[:space:]]*ac_/ s/^/#/' /etc/postgresql/16/main/postgresql.conf
sed -iE '/^[[:space:]]*MAC[[:space:]]/ s/^/-- /' backend_dump.sql
sed -iE '/^[[:space:]]*MAC[[:space:]]/ s/^/-- /' fuchsiad_dump.sql

Start a new cluster.
```
systemctl start postgresql
```

Delete the old cluster.

pg_dropcluster <old_version> <cluster_name>

Recreate the deleted databases.

su postgres -c psql
> CREATE DATABASE solidpoint_backend;
> CREATE USER backend WITH PASSWORD '${POSTGRESQL_PASSWORD}';
> GRANT ALL PRIVILEGES ON DATABASE solidpoint_backend TO backend;
> \c solidpoint_backend;
> GRANT ALL ON SCHEMA public TO backend;
> CREATE DATABASE fuchsia;
> CREATE USER fuchsia WITH PASSWORD '${POSTGRESQL_PASSWORD}';
> GRANT ALL PRIVILEGES ON DATABASE fuchsia TO fuchsia;
> \c fuchsia;
> GRANT ALL ON SCHEMA public TO fuchsia;
> quit

Import the solidpoint_backend backup.

sudo -u postgres psql -d solidpoint_backend < backend_dump.sql

Import the fuchsia backup.

sudo -u postgres psql -d fuchsia < fuchsiad_dump.sql

When installing on Astra Linux, ensure that the port number in the /etc/postgresql/16/main/postgresql.conf file is set to 5432 (if not, set it to 5432).
Restart PostgreSQL.
```
systemctl restart postgresql
```

Known issues¶

Error in the `pg_upgradecluster` command caused by locales¶

Example:

root@astra:~# pg_upgradecluster 15 main
Upgrading cluster 15/main to 16/main ...
Stopping old cluster...
Restarting old cluster with restricted connections...
Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LC_ADDRESS = "en_GB.UTF-8",
LC_NAME = "en_GB.UTF-8",
LC_MONETARY = "en_GB.UTF-8",
LC_PAPER = "en_GB.UTF-8",
LC_IDENTIFICATION = "en_GB.UTF-8",
LC_TELEPHONE = "en_GB.UTF-8",
LC_MEASUREMENT = "en_GB.UTF-8",
LC_TIME = "en_GB.UTF-8",
LC_NUMERIC = "en_GB.UTF-8",
LANG = "en_US.UTF-8"
are supported and installed on your system.
perl: warning: Falling back to a fallback locale ("en_US.UTF-8").
Error: The locale requested by the environment is invalid:
LANG: en_US.UTF-8
LC_ADDRESS: en_GB.UTF-8
LC_IDENTIFICATION: en_GB.UTF-8
LC_MEASUREMENT: en_GB.UTF-8
LC_MONETARY: en_GB.UTF-8
LC_NAME: en_GB.UTF-8
LC_NUMERIC: en_GB.UTF-8
LC_PAPER: en_GB.UTF-8
LC_TELEPHONE: en_GB.UTF-8
LC_TIME: en_GB.UTF-8
Error: Could not create target cluster

Solution:

Use the following commands:

apt install -y locales
dpkg-reconfigure locales

In the opened window, choose en_GB.UTF-8 UTF-8 using the Space key.

Error in the `pg_upgradecluster` command caused by configuration parameters¶

Example:

Error: /usr/lib/postgresql/16/bin/pg_ctl /usr/lib/postgresql/16/bin/pg_ctl start -D /var/lib/postgresql/16/main -l /var/log/postgresql/postgresql-16-main.log -s -o  -c config_file="/etc/postgresql/16/main/postgresql.conf" -c hba_file=/tmp/pg_hba.JThoYx.conf exited with status 1:
2025-09-06 16:07:18.871 GMT [335627] LOG:  unrecognized configuration parameter "ac_enable_maclabels_on_files" in file "/etc/postgresql/16/main/postgresql.conf" line 38
2025-09-06 16:07:18.871 GMT [335627] LOG:  unrecognized configuration parameter "ac_ignore_maclabel" in file "/etc/postgresql/16/main/postgresql.conf" line 39

Solution:

Use the following command:

nano /etc/postgresql/16/main/postgresql.conf

Comment out all parameters starting with ac_ by placing a # before them.
Save the file and exit.
Start the cluster
```
pg_ctlcluster 16 main start
```
Check the cluster status
```
pg_lsclusters
```