Follow Tahola

Time flies by.......

Blog post   •   May 30, 2018 08:42 BST

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

Comments (0)

Add comment

Comment