Updating from PostgreSQL 11 to PostgreSQL 15

Knowledge Base Supported
, ,
#1

This article only applies to UCS 5.2.

Upgrade PostgreSQL from 11 to 15

UCS 5.2 ships PostgreSQL 15 by default, while UCS 5.0 shipped PostgreSQL 11 by default. When UCS 5.0 systems are updated, however, the installed PostgreSQL version is retained.

The following steps are necessary on a UCS-5.2 system for updating PostgreSQL 11 to PostgreSQL 15

[ -f /usr/sbin/univention-pkgdb-scan ] &&
  chmod -x /usr/sbin/univention-pkgdb-scan
service postgresql stop
rm -rf /etc/postgresql/15
apt-get install --reinstall postgresql-15
ucr set postgres15/autostart='yes'
systemctl unmask postgresql@15-main.service
pg_dropcluster 15 main --stop
service postgresql start
[ -e /var/lib/postgresql/15/main ] &&
  mv /var/lib/postgresql/15/main /var/lib/postgresql/15/main.old
pg_upgradecluster 11 main
univention-install --yes univention-postgresql-15
ucr commit /etc/postgresql/15/main/*
chown -R postgres:postgres /var/lib/postgresql/15
[ ! -e /etc/postgresql/15/main/conf.d/ ] &&
  mkdir /etc/postgresql/15/main/conf.d/ &&
  chown postgres:postgres /etc/postgresql/15/main/conf.d/
service postgresql restart
[ -f /usr/sbin/univention-pkgdb-scan ] &&
  chmod +x /usr/sbin/univention-pkgdb-scan

To check the actual used version, you may use pg_lsclusters -h, which should look like this:
15 main 5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

Once the new database / data inventory has been verified, the PostgreSQL 11 data can be removed / the software package uninstalled:

pg_dropcluster 11 main --stop
service postgresql restart
apt-get purge postgresql-11

Upgrade password encryption from md5 to sram-sha-256

UCS 5.2 ships with the new password encryption method scram-sha-256.
As md5 is considered insecure, it is recommended to switch to scram-sha-256.
Before switching to scram-sha-256, you have to be sure that all PostgreSQL clients support scram-sha-256.

Change the password_encryption parameter

To change the password_encryption parameter, you have to set the UCR variable postgres15/password-encryption to scram-sha-256:

ucr set postgres15/password-encryption=scram-sha-256

Afterwards, you have to restart the PostgreSQL service:

systemctl restart postgresql.service

After the restart, you can check the parameter with the following command:

sudo -u postgres psql -c "SHOW password_encryption;"

Set all passwords again

All password authenticated users have to change their password. In psql, a superuser can change any user’s password with 
\password user_name

You can get all users, you can use the following command:

sudo -u postgres psql -c "\du"

Change the authentication method

The authentication method has to be changed from ‘md5’ to ‘scram-sha-256’, you can do it setting the UCR variable ‘postgres15/pg_hba/password-encryption’ to ‘scram-sha-256’:

ucr set postgres15/pg_hba/password-encryption=scram-sha-256

Afterwards, you have to restart the PostgreSQL service:

systemctl restart postgresql.service
2 Likes
Mastodon