Supported PostgreSQL versions can be found in System Recommendations and Supported Platforms
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:
It is recommended to take system backup. Please refer to Backup and restore SSO and Backup and restore CustomerID for more details. |
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 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 |
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.
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 |
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:
This ERROR is expected and can be ignored |
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 |
After you have completed the steps you should verify that the platform is working as expected:
If you are upgrading postgresql as a part of SSO or CustomerID upgrade finish the whole upgrade process before verifying the migration. |
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) |
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 |
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 |