Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
Excellent thanks for sharing your powershell code! Glad you got it working.
Best,
--J