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
The following commands are one-liners. You can simply triple-click on a line to completely select it for easy copy-and-paste. But still: Always double-check your command prompt before issueing a command!
[ -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
Then check the actual version that is used:
$ pg_lsclusters -h
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