Azure Database: How to migrate PostgreSQL Database

• This document provides the steps on how to migrate data between two PostgreSQL database servers.
• This were were the steps used to complete the PEDScreen Migration.
• The migration will be completed on the RIS server.

Step 1:Log onto

• Before we start the migration, we will need to ssh into the RIS Server.

Step 2: Validate access to target Database

• Run the following query to validate you can access the target postgresql database server.

psql -h < hostname or IP > -W < Password > -U < Username > -d < database >

psql -h 10.237.5.37 -W -U i2admin -d postgres

# to exit, run the script below
\q

Step 3: Validate access to Source Database

• Run the following query to validate you can access the source postgresql database server.

psql -h 10.237.0.5 -W -U postgres -d postgres

Step 4: Backup source database

• A backup is needed t start the migration.
• I created a directory in RIS server for the pedscreen backups to be stored in, the directory location is /storage1/fs1/I2/Active/rdc2_etl/pedscreen
• The following completes the backup.
• addtionally, ensure that you are in the correct directory o backup database.

# optionally, you can time in front of the script.
pg_dump -h < hostname or IP > -W  < Password > -U  < Username > -d  < Password > -f backup/dumpfile.sql

pg_dump -h 10.237.0.5 -W -U postgres -d pedscreen -f pedscreen_122124.sql

pg_dump -h 10.237.0.5 -W -U postgres -d clarity -f clarity_122124.sql

Step 5: Restore database on target server

• Use the script below to restore the database on the target server.

psql -h 10.237.5.37 -W -U i2admin -d pedscreen -f pedscreen_122124.sql 

psql -h 10.237.5.37 -W -U i2admin -d clarity -f clarity_122124.sql

Step 6: Migrate User Account(roles) and permissions:

• Run the SQL script below to generate the create statements for the database roles users:

SELECT 'CREATE ROLE ' || rolname ||
CASE WHEN rolsuper THEN ' SUPERUSER' ELSE '' END ||
CASE WHEN rolinherit THEN ' INHERIT' ELSE ' NOINHERIT' END ||
CASE WHEN rolcreaterole THEN ' CREATEROLE' ELSE '' END ||
CASE WHEN rolcreatedb THEN ' CREATEDB' ELSE '' END ||
CASE WHEN rolcanlogin THEN ' LOGIN' ELSE '' END ||
CASE WHEN rolreplication THEN ' REPLICATION' ELSE '' END ||
' PASSWORD ''' || rolpassword || ''';' AS create_role FROM pg_authid WHERE rolname NOT LIKE 'pg_%';

-- script OutPut
CREATE ROLE pedscreen INHERIT LOGIN PASSWORD 'SCRAM-SHA-256$4096:ppU694zUW20Z/gLWixS0SQ==$/XPQeWrh5vK80Dx8obYGkcUZMXhFy/88CToHLF27Ne0=:pckc/aeMLkM/HeNPmAlEXpi3f95vqy0gEosRFtdi6Bs=';
CREATE ROLE clarity_user INHERIT LOGIN PASSWORD 'SCRAM-SHA-256$4096:HwapqXOgXxRdo6fMgCJo8A==$45jpG/eUxP8I7/WVtz+pWNJKvZG1xbYT9XiVU5baGt4=:cT6snFn0J8fgBkuYeel3pmyXVAcYNk65XPcdgDwyO/w=';
CREATE ROLE skhot INHERIT LOGIN PASSWORD 'SCRAM-SHA-256$4096:QiZZDy8guPIPCP4KJClhEQ==$CnA1gRLCCTJInVJa50MI5TjuUUrdGu1AhpM/VbtTO4I=:a7kFboWo7T0f8P/5JQ5rGLWVvlILYwc7IMzPHl3b0Xs=';
CREATE ROLE postgres SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION PASSWORD 'SCRAM-SHA-256$4096:Ks1kTv5prHeToexGhuYg7w==$JncCaSBVmb9j/Hpk/1nKup6eE2u4CIDoo3PBfdVO4CI=:S3Z/L4a+uFul4s9DdNobe1Gpr6O0KEt0poZPAgTBWjs=';

Step 7: Generate Role Permission script

• Run the SQL script below to generate the create statements for the database roles permissions:

SELECT 'GRANT ' || r.rolname || ' TO ' || m.rolname || ';'
FROM pg_auth_members am
JOIN pg_authid r ON r.oid = am.roleid
JOIN pg_authid m ON m.oid = am.member
WHERE r.rolname NOT LIKE 'pg_%' AND m.rolname NOT LIKE 'pg_%';

-- Script output
GRANT pedscreen TO skhot;
GRANT clarity_user TO skhot;