Time flies by.......

Time flies by...

Qlik Sense backup automation using PowerShell

By Kabir Rab, 29th May 2018

...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 here at Tahola, we have been involved in some really interesting Qlik Sense projects, which has resulted in me completely loosing 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 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 detail 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 back up. 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.



• 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_Password

Step 2

• Create a new PowerShell script file (.ps1 ex- QSBackup.ps1) and add the following script.
  

  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 each time you run the process.

  Repository Back up 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 location 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
  
  
  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
  
  
  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
  
  
  write-Output "$TimeStamp - PostgreSQL backup Completed" # writing to console
  
  
  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
  
  
  write-Output "$TimeStamp - File Backup Completed" # writing to console
  
  
  write-Output "$TimeStamp - Restarting Qlik Services ...." # writing to console
  
  
  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
  
  
  $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
  



• If you decide to use the multiple backup points as per the example above, you might want to consider your back up retention policy and delete the older backups once they are no longer relevant. For example, to 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 3

Now that you have an automation Power Shell 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.

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 solutions we provide to our customers then please contact:

Leigh Baillie.
Marketing Director
Tahola