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:
- 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.
Configure old PostgreSQL
Disconnect old PostgreSQL from internet, add following line to pg_hba.conf:
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.
After this, move to /usr/local folder and run pg_dumpall as postgres user.
Restore dump file
Shutdown old PostgreSQL and start the new PostgreSQL
In the newly installed PostgreSQL, postgres user should have peer identification for all databases.
Restore command will output the following error statement:
This ERROR is expected and can be ignored
Configure new PostgreSQL
Remove postgres local peer authentication that was added earlier:
Transfer pg_hba.conf from old installation to new one:
And make same changes to postgresql.conf from old installation to new one.
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.
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:
|CID User||100 000|
|CID Custom attributes||600 000|
|CID User||500 000|
|CID Custom attributes||3 000 000|
|Events||1 000 000|