Author: Hyper-Vine

A little off my usual subject matter – but was tasked with this recently and couldn’t find anything really detailing it all in one place.

Anyway, those of you familiar (or not) with this DB will be looking for some easy way to back it up. Well, there isn’t. It’s a good old fashioned dump out a flat file product.

Command lines to do this are detailed here: Qlik Sense Backup Guide

pg_dump.exe -h localhost -p 4432 -U postgres -b -F t -f “c:\QSR_backup.tar” QSR

As you will read there, when you backup you can be prompted for a password (???) or you can specify it in plain text (sigh). To store the password, you need to use a PGPASS file.

PGPASS files are detailed here: PostgreSQL docs

File format needs to be:

hostname:port:database:username:password

What it doesn’t clearly explain is that this is a lookup list (think HOSTS file). If you were to use the pg_dump command in the example above – your PGPASS file should read:

localhost:4432:QSR:postgres:YourPassword

For multiple databases across multiple hosts, then you need to list each entry in your file, when PG_dump.exe references this information, it will lookup based on the details in the command line of hostname, port, database and username to then select the password.

Security

I like to work with service accounts for applications, so it makes sense to put the PGpass.conf file in a folder with restricted permissions – so only that service account user or domain admin can get at it. Run your script using this account or one with permissions to read the file.

Scripting Backup

My preference here is to call the script on a schedule. I’m using poweshell as I have multiple nodes in the site and services need to be stopped on every node for the backup to take place.

$scriptblock1={
stop-service QlikSenseEngineService
stop-service QlikSensePrintingService
stop-service QlikSenseProxyService
stop-service QlikSenseSchedulerService
stop-service QlikSenseServiceDispatcher
get-service QlikSenseRepositoryService | stop-service
}
$scriptblock2={
get-service QlikSenseRepositoryService | start-service
get-service qlik* | ? status -eq 'stopped' | start-service
}

# Shutdown services on primary & secondary nodes
Invoke-Command -ScriptBlock $scriptblock1
Invoke-Command -ComputerName secondnode -ScriptBlock $scriptblock1

# Call Backup file
(start-process -FilePath "cmd.exe" -ArgumentList '/c c:\backup.bat' -Wait -PassThru).ExitCode

# Start Services up on primary & secondary nodes
Invoke-Command -ScriptBlock $scriptblock2
Invoke-Command -ComputerName secondnode -ScriptBlock $scriptblock2

# Move backup to another location 
$backupfile="d:\backups\QSR.tar"
$destination="\\locationofyourchoice\CONFIG\Backup"
Move-Item $backupfile -Destination $destination -Force

The script calls a backup.bat file which contains the following:

SET PGPASSFILE=C:\securefolder\pgpass.conf
"C:\Path to Qliksense\Repository\PostgreSQL\9.3\bin\pg_dump.exe" -U yourDBadminuser -h localhost -p 4432 QSR >"d:\backups\QSR.tar"

The file sets a path ref to your PGpass.conf file, then calls PG_Dump with the required parameters, so it can lookup the password and then output your backup to D:\backups.

Powershell picks the file up and moves it elsewhere (I suggest moving into the same file structure as your Root,Apps,Logs,CustomData & Static content is (specified on install) – so that you can use your prefered backup software (DPM etc…) to protect it.