/
Upgrade and migrate to new version of PostgreSQL

Upgrade and migrate to new version of PostgreSQL

2022-04-01

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

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