Select Page

 

I’m Thomas and I’m new to the OptimalBI team. Instead of writing the usual “Hello, I’m new here” blog I thought I’d write something useful, so here goes.

Recently I was assisting with a deployment for Qlik Sense 3.2 with shared persistence, and it became apparent that the Repository Snapshot Manager (RSM) utility was unsuitable – something a number of users online are also observing.

The result of this means a daily laborious task of manually completing backups, and in the event of a disaster having to manually restore functionality and reports.

I was unable to find any functioning scripts online to complete this so I replicated the functionality of each respective process (except the initial backup of certificates which it’s expected should happen during the install process) in PowerShell based on the Qlik documentation here:

Backup & Restore

It’s worth noting that I followed the process which required the pg_pass.conf file to be constructed and placed in the correct location – which can be established by running the following command in CMD:

echo %APPDATA%

I also found, contrary to what was specified in the documentation, that the pg_pass.conf file needs to be in the following format (note the *):

hostname:port:*:postgres:password

Following are the scripts successfully used for the processes, obviously, the parameters need to be changed to suit each individual installation/environment:

Script to backup:

# First drop all the restrictions on script execution

Set-ExecutionPolicy Unrestricted

$Today = Get-Date -UFormat “%Y%m%d_%H%M”

$StartTime = Get-Date -UFormat “%Y%m%d_%H%M”

$PostGreSQLLocation = “D:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.3\bin”
$PostGresBackupTarget = “D:\Backups\Qlik”
$SenseProgramData = “D:\QlikShared\” # Shared Persistance Folder

$Today = Get-Date -UFormat “%Y%m%d_%H%M”

$StartTime = Get-Date -UFormat “%Y%m%d_%H%M”

# –blobs , exclude for now

write-host “Stopping Qlik Services ….”

stop-service QlikSenseProxyService -WarningAction SilentlyContinue
Start-Sleep -s 10
stop-service QlikSenseEngineService -WarningAction SilentlyContinue
Start-Sleep -s 10
stop-service QlikSenseSchedulerService -WarningAction SilentlyContinue
Start-Sleep -s 10
stop-service QlikSensePrintingService -WarningAction SilentlyContinue
Start-Sleep -s 10
stop-service QlikSenseServiceDispatcher -WarningAction SilentlyContinue
Start-Sleep -s 10
stop-service QlikSenseRepositoryService -WarningAction SilentlyContinue

Copy-Item $SenseProgramData\ArchivedLogs -Destination $PostGresBackupTarget\$StartTime\ArchivedLogs -Recurse
Copy-Item $SenseProgramData\Apps -Destination $PostGresBackupTarget\$StartTime\Apps -Recurse
Copy-Item $SenseProgramData\StaticContent -Destination $PostGresBackupTarget\$StartTime\StaticContent -Recurse
Copy-Item $SenseProgramData\CustomData -Destination $PostGresBackupTarget\$StartTime\CustomData -Recurse

write-host “File Backup Completed”

write-host “Backing up PostgreSQL Repository Database ….”

cd $PostGreSQLLocation
.\pg_dump.exe -h localhost -p 4432 -U postgres -w -F t -f “$PostGresBackupTarget\$StartTime\QSR_backup_$Today.tar” QSR

write-host “PostgreSQL backup Completed”

write-host “Backing up Shared Persistance Data from $SenseProgramData ….”

write-host “Restarting Qlik Services ….”

start-service QlikSenseRepositoryService -WarningAction SilentlyContinue
Start-Sleep -s 10
start-service QlikSenseEngineService -WarningAction SilentlyContinue
Start-Sleep -s 10
start-service QlikSenseSchedulerService -WarningAction SilentlyContinue
Start-Sleep -s 10
start-service QlikSensePrintingService -WarningAction SilentlyContinue
Start-Sleep -s 10
start-service QlikSenseServiceDispatcher -WarningAction SilentlyContinue
Start-Sleep -s 10
start-service QlikSenseProxyService -WarningAction SilentlyContinue

$EndTime = Get-Date -UFormat “%Y%m%d_%H%M%S”

write-host “This backup process started at ” $StartTime ” and ended at ” $EndTime

Script to restore:

# First drop all the restrictions on script execution

Set-ExecutionPolicy Unrestricted

$StartTime = Get-Date -UFormat “%Y%m%d_%H%M”
$EndTime = Get-Date -UFormat “%Y%m%d_%H%M%S”
$BackupFile = Read-Host -Prompt ‘Input the backup string you wish to restore’
Start-Sleep -s 60
$PostGreSQLLocation = “D:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.3\bin”
$PostGresBackupLocaton = “D:\Backups\Qlik\$BackupFile”
$SenseProgramData = “D:\QlikShared\” # Shared Persistance Folder
$Today = Get-Date -UFormat “%Y%m%d_%H%M”
$StartTime = Get-Date -UFormat “%Y%m%d_%H%M”
$PostGresDBFile = “D:\Backups\Qlik\$BackupFile\QSR_backup_$BackupFile.tar”
# kill off all of the servives except the qlik sense repository service.

write-host “Stopping Qlik Services ….”

stop-service QlikSenseProxyService -WarningAction SilentlyContinue
Start-Sleep -s 10
stop-service QlikSenseEngineService -WarningAction SilentlyContinue
Start-Sleep -s 10
stop-service QlikSenseSchedulerService -WarningAction SilentlyContinue
Start-Sleep -s 10
stop-service QlikSensePrintingService -WarningAction SilentlyContinue
Start-Sleep -s 10
stop-service QlikSenseServiceDispatcher -WarningAction SilentlyContinue
Start-Sleep -s 10
stop-service QlikSenseRepositoryService -WarningAction SilentlyContinue

# navigate to the database directory

cd $PostGreSQLLocation

write-host “Restoring PostgreSQL Repository Database ….”

# drop the database and restore from the backup

.\dropdb.exe -e -h localhost -p 4432 -U postgres -w QSR

.\createdb.exe -h localhost -p 4432 -U postgres -T template0 QSR

.\pg_restore.exe -h localhost -p 4432 -U postgres -d QSR $PostGresDBFile

write-host “PostgreSQL Restore Completed”

# restore the appliction folders

write-host “Resoring Shared Persistance Data from $PostGresBackupLocaton ….”

#Restore log and application data to the file share used for storage of log and application data.

Copy-Item $PostGresBackupLocaton\ArchivedLogs -Destination $SenseProgramData\ArchivedLogs -Recurse -Force
Copy-Item $PostGresBackupLocaton\Apps -Destination $SenseProgramData\Apps -Recurse -Force
Copy-Item $PostGresBackupLocaton\StaticContent -Destination $SenseProgramData\StaticContent -Recurse -Force
Copy-Item $PostGresBackupLocaton\CustomData -Destination $SenseProgramData\CustomData -Recurse -Force

write-host “File Restore Completed”

write-host “Restarting Qlik Services ….”

# restart the services

start-service QlikSenseRepositoryService -WarningAction SilentlyContinue
Start-Sleep -s 10
start-service QlikSenseEngineService -WarningAction SilentlyContinue
Start-Sleep -s 10
start-service QlikSenseSchedulerService -WarningAction SilentlyContinue
Start-Sleep -s 10
start-service QlikSensePrintingService -WarningAction SilentlyContinue
Start-Sleep -s 10
start-service QlikSenseServiceDispatcher -WarningAction SilentlyContinue
Start-Sleep -s 10
start-service QlikSenseProxyService -WarningAction SilentlyContinue

write-host “This restore process started at ” $StartTime ” and ended at ” $EndTime

Hope that helps you as much as it helped me.

Thomas – MacGyver of code

Thomas blogs about reporting platforms, data warehousing and the systems behind them.

This is Thomas’s first blog but if you would like to know more about Qlik check out Barry’s blog Qlik Sense – Change in Architecture

We run regular business intelligence courses in both Wellington and Auckland.

%d bloggers like this: