Advice on How to Automatically set the owners after restoring from Postgres Backups - via Powershell

[QlikMaster1]

Hi,

We have been having fun with getting our environment switched over from pg11 to pg12 - standalone install.

The backup of the individual tables are fine, and the restore process is 99 % there with the dropping, creation and restore of the databases.

We have used postgres as the login to all the drop, create and restore, but I cannot seem to get the last step of setting the owners to work via Powershell :

Can anyone advise what I maybe missing as the script triggers a window for each db and quickly flashes by, it seemed to update the QSR correctly, but refuses since, just wondered if anyone had got psql to just run the alter commands in Powershell, as like Postgres it seems not a lot of information if anyone has out there.

Script is below :

# First drop all the restrictions on script execution

$Today = Get-Date -UFormat “%Y%m%d”

$limit = (Get-date).AddDays(-7).ToString("yyyyMMdd")

$StartTime = Get-Date -UFormat “%Y%m%d_%H%M”
$PostGreSQLLocation = '\\ewokgcgmdb51\c$\Program Files\PostgreSQL\12\bin\psql.exe'
$PostGresRestoreName = "QlikDBBackup_"
$PostGresRestoreTarget = “D:\Restore\"
$PGPASSFILE = “\\ewokgcgmdb51\c$\Users\QlikSense_PS\AppData\Roaming\postgresql\pgrestore\v12\pgpass.conf”

#Begins backup of PostgresSQL DB

write-host “Restore PostgreSQL Repository Database ….”

Start-Process $PostGreSQLLocation -ArgumentList "-h 127.0.0.1 -p 3432 -U postgres -d QSR -f D:\data\Set Database Owners\SetQSROwner.sql"

Start-Process $PostGreSQLLocation -ArgumentList "-h 127.0.0.1 -p 3432 -U postgres -d QSMQ -f D:\data\Set Database Owners\SetQSMQOwner.sql"

Start-Process $PostGreSQLLocation -ArgumentList "-h 127.0.0.1 -p 3432 -U postgres -d SenseServices -f D:\data\Set Database Owners\SetSenseServicesOwner.sql" -Wait

Start-Process $PostGreSQLLocation -ArgumentList "-h 127.0.0.1 -p 3432 -U postgres -d Licenses -f D:\data\Set Database Owners\SetLicensesOwner.sql"

Start-Process $PostGreSQLLocation -ArgumentList "-h 127.0.0.1 -p 3432 -U postgres -d QLogs -f D:\data\Set Database Owners\SetQLogsOwner.sql"

Thanks

[Jay_Brown]

Hello @QlikMaster1 , did you get this sorted?

I save something similar to this into a file called ChangeOwners.sql:

DO
$$
DECLARE
row record;
BEGIN
FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
LOOP
EXECUTE 'ALTER TABLE public.' || quote_ident(row.tablename) || ' OWNER TO qliksenserepository;';
END LOOP;
END;
$$;

Then execute with

psql.exe -h 127.0.0.1 -p 3432 -U postgres -d QSR -e -f ChangeOwners.sql
psql.exe -h 127.0.0.1 -p 3432 -U postgres -d QSMQ -e -f ChangeOwners.sql

 etc.

Does that help?

--J

[QlikMaster1 · Accepted solution]

Hi @Jay_Brown ,

Yep got there in the end with a bit of powershell magic. (Not sure where we would be with Qlik without Powershell lol).

#Begins Restore of PostgresSQL DB

write-host “Restore PostgreSQL Repository Database ….”

cd $PostGreSQLLocation

#Drop all the tables first to have a clean empty DB

write-host “Dropping QSR? Db”
.\dropdb.exe -h 127.0.0.1 -p 3432 -U postgres QSR?
write-host “Drop completed of QSR?”

write-host “Dropping SenseServices”
.\dropdb.exe -h 127.0.0.1 -p 3432 -U postgres SenseServices
write-host “Drop completed of SenseServices”

write-host “Dropping QSMQ”
.\dropdb.exe -h 127.0.0.1 -p 3432 -U postgres QSMQ
write-host “Drop completed of QSMQ”

write-host “Dropping Licenses”
.\dropdb.exe -h 127.0.0.1 -p 3432 -U postgres Licenses
write-host “Drop completed of Licenses”

#Create the empty DB to be populated in the next step

write-host “Creating QSR DB”
.\createdb.exe -h 127.0.0.1 -p 3432 -U postgres -T template0 QSR​
write-host “QSR Created”

write-host “Creating SenseServices DB”
.\createdb.exe -h 127.0.0.1 -p 3432 -U postgres -T template0 SenseServices
write-host “SenseServices Created”

write-host “Creating QSMQ DB”
.\createdb.exe -h 127.0.0.1 -p 3432 -U postgres -T template0 QSMQ
write-host “QSMQ Created”

write-host “Creating Licenses DB”
.\createdb.exe -h 127.0.0.1 -p 3432 -U postgres -T template0 Licenses
write-host “Licenses Created”

#Restore the database from latest backup

write-host “Restoring QSR DB”
.\pg_restore.exe -h 127.0.0.1 -p 3432 -U postgres -d QSR? "$PostGresRestoreTarget\$PostGresRestoreName$Today\QSR_backup_$Today.tar"
write-host “Restore completed of QSR DB”

write-host “Restoring SenseServices DB”
.\pg_restore.exe -h 127.0.0.1 -p 3432 -U postgres -d SenseServices "$PostGresRestoreTarget\$PostGresRestoreName$Today\SenseServices_backup_$Today.tar"
write-host “Restore completed of SenseServices DB”

write-host “Restoring QSMQ DB”
.\pg_restore.exe -h 127.0.0.1 -p 3432 -U postgres -d QSMQ "$PostGresRestoreTarget\$PostGresRestoreName$Today\QSMQ_backup_$Today.tar"
write-host “Restore completed of QSMQ DB”

write-host “Restoring Licenses DB”
.\pg_restore.exe -h 127.0.0.1 -p 3432 -U postgres -d Licenses "$PostGresRestoreTarget\$PostGresRestoreName$Today\Licenses_backup_$Today.tar"
write-host “Restore completed of Licenses DB”

#Change to correct DB names

write-host "Change to correct QSR name"
Start-Process $PostGresPSQLLocation -ArgumentList "-h 127.0.0.1 -p 3432 -U postgres -d QSR? -f D:\data\SetDatabaseOwners\SetToCorrectQSRName.sql"
write-host “Successfully Changed to Correct DB Name for QSR”

#Assign correct permissions to Tables

write-host “Change to Correct QSR DB Owner”
Start-Process $PostGresPSQLLocation -ArgumentList "-h 127.0.0.1 -p 3432 -U postgres -d QSR -f D:\data\SetDatabaseOwners\SetQSROwner.sql"
write-host “Successfully Changed to Correct QSR DB Owner”

write-host “Change to Correct QSMQ DB Owner”
Start-Process $PostGresPSQLLocation -ArgumentList "-h 127.0.0.1 -p 3432 -U postgres -d QSMQ -f D:\data\SetDatabaseOwners\SetQSMQOwner.sql"
write-host “Successfully Changed to Correct QSMQ DB Owner”

write-host “Change to Correct SenseServices DB Owner”
Start-Process $PostGresPSQLLocation -ArgumentList "-h 127.0.0.1 -p 3432 -U postgres -d SenseServices -f D:\data\SetDatabaseOwners\SetSenseServicesOwner.sql"
write-host “Successfully Changed to Correct SenseServices DB Owner”

write-host “Change to Correct Licenses DB Owner”
Start-Process $PostGresPSQLLocation -ArgumentList "-h 127.0.0.1 -p 3432 -U postgres -d Licenses -f D:\data\SetDatabaseOwners\SetLicensesOwner.sql"
write-host “Succesfully Changed to Correct Licenses DB Owner”

write-host “PostgreSQL restore Completed”

Stop-Transcript

$EndTime = Get-Date

$ElapsedTime = $EndTime-$StartTime
$TotalTime = 'duration: {0:mm} min {0:ss} sec {0:ff} millisec' -f $ElapsedTime

On the last step to set the permissions found triggering the relevant sql command to switch over the permissions worked and took similar approach to yourself.

[Jay_Brown]

Excellent thanks for sharing your powershell code!  Glad you got it working.

Best,

--J