Time flies by…
…Time flies by, this certainly is true in every sense. I cannot believe it has already been over 2 months since my last blog. There are so many exciting things that have been happening at Tahola, we have been involved in some really interesting Qlik Sense projects, which has resulted in me completely losing track of my blog.
So, without any further ado, today, I am going to cover the Qlik Sense Repository backup automation process using PowerShell (Visit here if you are not familiar with PowerShell).
Qlik typically provides extensive help/guides for their products, however, the automation of the Qlik Sense repository backup process is not documented particularly well. Therefore, I felt it would be beneficial for me to write a blog that covers this automation process in details and I am going to use PowerShell to achieve the objective.
Step 1
Create “pgpass.conf” file with the credentials which will be used for the repository backup. You can find more information on “pgpass.conf” at PostgreSQL website. Navigate to –
“%APPDATA%\postgresql”
and create a new file named “pgpass.conf”.
NOTE:If the “postgresql” folder does not exist in that directory, then please create one. Please make sure this is being created under the account which will be executing the PowerShell script task.
Open the file in note pad (or any editor of your choice) and add the relevant information in the following format –
hostname:port:database:username:password It should look something like this –
localhost:4432:*:postgres:Superuser_PasswordStep 2
Create a new PowerShell script file (.ps1 ex- QSBackup.ps1) and add the following script. Please amend the environment variables to match your requirements, ex – backup location, shared folder location etc.
NOTE:This script will stop all the Qlik Sense services and back up the repository to a .tar file in the following location as well as backing up the shared folders. I have used 1 backup set per date to keep multiple backup points. You can always amend that to overwrite the same backup file(s) each time you run this process.
Repository Backup location –\\networkdrive\01_Backup\02_QSR\QSR_backup_[Date].tar
Shared Folders location –\\networkdrive\01_Backup\01_QS_Shared_Folder\[Date]\
Add the following script to the .ps1 file. Change the folder locations as per your Qlik Sense environment.
$Today = Get-Date -UFormat "%Y%m%d_%H%M"
$StartTime = Get-Date -UFormat "%Y%m%d_%H%M"
$TimeStamp = Get-Date -f HH:mm:ss_ffff
$LogDirectory = "C:\01_SenseApplication\2_BackupScript\01_Log"
# Drop all the restrictions on script execution
Set-ExecutionPolicy Unrestricted
# Starting log
Start-Transcript -path $LogDirectory\TC_QS_Backup_$Today.txt
# This will require updating on each Qlik Sense upgrade, provided the version of postgres changes..
$PostGreSQLLocation = "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.6\bin"
# This is the root shared folder UNC path
$PostGresBackupTarget = "\\networkdrive\01_Backup"
# This is the folder to backup. Qlik Sense Shared Persistence Folder
$SenseSharedData = "C:\02_SenseShare\"
write-Output "$TimeStamp - Stopping Qlik Services ...." # writing to console
# you can use -Force switch here. ex- stop-service QlikLoggingService -Force -WarningAction SilentlyContinue stop-service QlikSenseProxyService -WarningAction SilentlyContinue
stop-service QlikSenseEngineService -WarningAction SilentlyContinue
stop-service QlikSenseSchedulerService -WarningAction SilentlyContinue
stop-service QlikSensePrintingService -WarningAction SilentlyContinue
stop-service QlikSenseServiceDispatcher -WarningAction SilentlyContinue
stop-service QlikSenseRepositoryService -WarningAction SilentlyContinue
stop-service QlikLoggingService -WarningAction SilentlyContinue
$TimeStamp = Get-Date -f HH:mm:ss_ffff # updating the TimeStamp variable
write-Output "$TimeStamp - Backing up PostgreSQL Repository Database ...." # writing to console
cd $PostGreSQLLocation
.\pg_dump.exe -h localhost -p 4432 -U postgres -w -F t -f "$PostGresBackupTarget\02_QSR\QSR_backup_$Today.tar" QSR
$TimeStamp = Get-Date -f HH:mm:ss_ffff # updating the TimeStamp variable
write-Output "$TimeStamp - PostgreSQL backup Completed" # writing to console
write-Output "$TimeStamp - Restarting Qlik Services ...." # writing to console
# you can introduce sleep in between the service start command. start-service QlikLoggingService -WarningAction SilentlyContinue
start-service QlikSenseRepositoryService -WarningAction SilentlyContinue
start-service QlikSenseEngineService -WarningAction SilentlyContinue
start-service QlikSenseSchedulerService -WarningAction SilentlyContinue
start-service QlikSensePrintingService -WarningAction SilentlyContinue
start-service QlikSenseServiceDispatcher -WarningAction SilentlyContinue
start-service QlikSenseProxyService -WarningAction SilentlyContinue
$TimeStamp = Get-Date -f HH:mm:ss_ffff # updating the TimeStamp variable
write-Output "$TimeStamp - Backing up Shared persistence Data from $SenseSharedData ...." # writing to console
Copy-Item $SenseSharedData\ArchivedLogs -Destination $PostGresBackupTarget\01_QS_Shared_Folder\$StartTime\ArchivedLogs -Recurse
Copy-Item $SenseSharedData\Apps -Destination $PostGresBackupTarget\01_QS_Shared_Folder\$StartTime\Apps -Recurse
Copy-Item $SenseSharedData\StaticContent -Destination $PostGresBackupTarget\01_QS_Shared_Folder\$StartTime\StaticContent -Recurse
Copy-Item $SenseSharedData\CustomData -Destination $PostGresBackupTarget\01_QS_Shared_Folder\$StartTime\CustomData -Recurse
$TimeStamp = Get-Date -f HH:mm:ss_ffff # updating the TimeStamp variable
write-Output "$TimeStamp - File Backup Completed" # writing to console
$EndTime = Get-Date -UFormat "%Y%m%d_%H%M%S"
write-Output "$TimeStamp - This backup process started at " $StartTime " and ended at " $EndTime
Stop-Transcript
Step 3
If you decide to use multiple backup points as per the example above, you might want to consider your backup retention policy and delete the older backups once they are no longer relevant. For example, if you would like to keep only the last 30 days’ backups then you can achieve this by adding the following code to the end section your script (Before the service start command).
write-Output "Removing backup(s) older than 30 days"
# Defines the '30 days old' (today's date minus 30)
$age = (Get-Date).AddDays(-30)
# To remove the .tar file
# Get all the files in the folder and subfolders | foreach file
Get-ChildItem $PostGresBackupTarget\02_QSR -Recurse -File | foreach{
# if creationtime is 'le' (less or equal) than 30 days
if ($_.CreationTime -le $age){
#Write-Output "Older than 30 days - $($_.name)"
# remove the item
Remove-Item $_.fullname -Force -Verbose
}
}
# To remove the backed up shared folder
get-childitem -recurse -directory $PostGresBackupTarget\01_QS_Shared_Folder |
where { (get-date) - $_.lastwritetime -gt 30. } |
remove-item -recurse
Step 4
Now that you have an automation PowerShell script, you will need to schedule this to fully automate the process. Determine the best time that is suited to your environment as the services will be offline during the backup process. You can use any scheduler (ex-Windows Task Scheduler) to achieve this goal.
NOTE: There are instances when some of the services do not start properly. You can always write additional script to check the server’s availability and the QS service status and restart them if there are issues. I will cover that on another post in near future.
This is my second blog of this series, you can read the first blog ‘Sitting in the shadows no more’ here.
Watch this space and thank you for reading, should you wish to get in touch for further information regarding the solution then please use any of the contact details provided on the site.
Kabir's blog 