Supported PostgreSQL versions can be found in System Recommendations and Supported Platforms

Introduction

Below we document how we have upgraded the external datastore, PostgreSQL, to a new major version for use within the Identity Platform installed on a Linux server. We encourage you to review the official documentation for PostgreSQL database upgrade, see https://www.postgresql.org/docs/12/upgrading.html.

Prerequisites: the following services are shutdown:

ubisecure-accounting
ubilogin-directory
ubilogin-server
wildfly

It is recommended to take system backup. Please refer to Backup and restore SSO and Backup and restore CustomerID for more details.

References

PostgreSQL official documentation for upgrading PostgreSQL Cluster: https://www.postgresql.org/docs/12/upgrading.html
PostgreSQL official documentation for pg_dumpall command: https://www.postgresql.org/docs/12/app-pg-dumpall.html
Resource configurations for PostgreSQL, see https://www.postgresql.org/docs/12/runtime-config-resource.html

Upgrading to a major version of PostgreSQL

Install new version of PostgreSQL

Instructions and PostgreSQL binaries on how to use package managers, can be found at the following URL: https://www.postgresql.org/download/. Note that many installation packages are available, please ensure you select the correct package installer for your operations platform.

Having obtained the desired PostgreSQL binaries, run the installation wizard or relevant binaries to install. The installation process is documented and maintained by your PostgreSQL vendor, so please follow their instructions to ensure the best possible configuration.

Create directory for storing dump file

Create folder with access rights for postgres user so that we can manage dump file.

mkdir -p /usr/local/ubisecure/backup
chmod -R ug+rw /usr/local/ubisecure/backup
chown -R ubilogin:postgres /usr/local/ubisecure/backup

Configure old PostgreSQL

Disconnect old PostgreSQL from internet, add following line to pg_hba.conf:

local    all    postgres    peer

This gives created postgres user right to connect to every database with peer authentication.

Make dump file

Make sure the new PostgreSQL is shutdown and old one is running.

systemctl stop postgresql-XX
systemctl start postgresql-9.6

After this, move to /usr/local folder and run pg_dumpall as postgres user.

cd /usr/local
su postgres
<new_sql_bin_folder>/pg_dumpall > /usr/local/ubisecure/backup/backup.pgsql
exit

Restore dump file

Shutdown old PostgreSQL and start the new PostgreSQL

systemctl stop postgresql-9.6
systemctl start postgresql-XX

In the newly installed PostgreSQL, postgres user should have peer identification for all databases.

cd /usr/local
su postgres
psql -f /usr/local/ubisecure/backup/backup.pgsql
exit

Restore command will output the following error statement:

psql:/usr/local/ubisecure/backup/backup.pgsql:18: ERROR:  role "postgres" already exists

This ERROR is expected and can be ignored

Configure new PostgreSQL

Remove postgres local peer authentication that was added earlier:

local    all    postgres    peer

Transfer pg_hba.conf from old installation to new one:

cp <pgsql_data_folder>/9.6/data/pg_hba.conf <pgsql_data_folder>/12/data/

And make same changes to postgresql.conf from old installation to new one.

Restart the new service to make the changes active.

systemctl restart postgresql-XX

Verify your migration

After you have completed the steps you should verify that the platform is working as expected:

Start PostgreSQL and all IDS services.
Verify Accounting through: Finalise Accounting Service installation
Verify CustomerID by logging in to the CustomerID admin UI and verify that expected data is available.

If you are upgrading postgresql as a part of SSO or CustomerID upgrade finish the whole upgrade process before verifying the migration.

Example migration times

To aid you in your migration planning, Ubisecure have performed some example migrations on test systems. Below are some examples on the duration of a migration, which is highly dependant on database size and complexity of user accounts within the database. The example times can be considered as base line for the required downtime to upgrade your environment.

Making dumpfile typically does not take a long time (with a low resources system and a large dataset, the dumpfile creation took few minutes). Most of the upgrade time is used by restoring the created dumpfile into new PostgreSQL server.

Here is some example row counts with time they took to be restored from dumpfile.

The following results were produced with this specific hardware:

2 x vCpu (AMD Ryzen 9 3900 12-Core Processor)
8GB RAM
20GB Disk (224k iops)

Dataset 1

CID User	100 000
CID Organization	100
CID Roles	500
CID Custom attributes	600 000
Events	100 000

$ time psql -o /dev/null -f /usr/local/ubisecure/backup/backup.pgsql
psql:/usr/local/ubisecure/backup/backup.pgsql:18: ERROR:  role "postgres" already exists
You are now connected to database "template1" as user "postgres".
You are now connected to database "accountingdb" as user "postgres".
You are now connected to database "customeriddb" as user "postgres".
You are now connected to database "postgres" as user "postgres".
real	4m55.576s
user	0m5.469s
sys	    0m3.202s

Dataset 2

CID User	500 000
CID Organization	100
CID Roles	500
CID Custom attributes	3 000 000
Events	1 000 000

$ time psql -o /dev/null -f /usr/local/ubisecure/backup/backup.pgsql
psql:/usr/local/ubisecure/backup/backup.pgsql:18: ERROR:  role "postgres" already exists
You are now connected to database "template1" as user "postgres".
You are now connected to database "accountingdb" as user "postgres".
You are now connected to database "customeriddb" as user "postgres".
You are now connected to database "postgres" as user "postgres".
real	52m58.215s
user	0m57.490s
sys	    0m36.338s

Related articles

Page:

Upgrade and migrate to new version of PostgreSQL
Page:

CustomerID SQL query troubleshooting

Browser not supported