Windows Server: Postgresql 9.5 to 12.5 Migration

As part of the 18.8 PEM Administrator release, the PEM Administrator is moving to support PGSQL (Postgresql) version 12.5 on both the appliance and Windows. While we believe the product will continue to function on PGSQL 9.5, but PGSQL 9.5 is end of life on February 11, 2021. This guide will assist with how to migrate a database running on Windows PGSQL 9.5 to Windows PGSQL 12.5

1. Shutdown PKWARE PEM Administrator

It's important to shutdown all live instances of the PEM Administrator when making a database backup. The backup needs to be clean and taking when none of the data in the environment is possibly changing to ensure a point in which we can recover from in-case of a disaster.

2. Install PGAdmin / Run PGAdmin

If you do not have it installed yet, install PGAdmin on the database server. This utility will provide a GUI for managing the PGSQL database engines and databases.

3. Create Database Backup

4. Uninstall Postgresl 9.5

From the app/remove programs feature in windows, uninstall PGSQL 9.5)

5. Install Postgresql 12.5

Since the older PGSQL instance is still installed and running on the server, when we install PGSQL we need to ensure we define a new port for PGSQL to run on. Alternatively, you an uninstall PGSQL 9.5 and use the same port. In this example we are uninstalling 9.5 before installing 12.5.

Open a command window and browse to the PGSQL-12.5 exe and run the following command (note the password(s) at the end, it can be changed):

CODE

postgresql-12.5-1-windows-x64.exe --unattendedmodeui minimal --mode unattended --servicename "postgreSQL" --prefix "C:\postgres\12.5" --datadir "C:\postgres\12.5\data" --superpassword "pkP@ssw0rd!"
cd C:\postgres\12.5\bin\
psql.exe -U postgres -c "CREATE ROLE smds LOGIN PASSWORD 'pkP@ssw0rd!' SUPERUSER INHERIT CREATEDB CREATEROLE"
Password for user postgres:
CREATE ROLE
psql.exe -U postgres -c "CREATE DATABASE smds OWNER smds"
Password for user postgres:
CREATE DATABASE

6. Restore database backup

Using PGAdmin, login and find the newly created database and select restore. Browse to the database backup location to create the backup job

7. Modify AppSettings.json

If the database connection string was modified in any way (ports, database names, or passwords) the AppSettings.json file needs to be updated with the updated values. After, you can verify the connectivity with the SmartcryptDB.dll