Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

Automating a Qlik Sense Site backup

driesman
New Contributor III

Automating a Qlik Sense Site backup

I recently had to back up a site and found that the documentation supplied for doing this did not fully meet my requirements, especially when it came to the step where it asks for the PostgreSQL superuser password.

To overcome this I did the following:

In C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.3\share\ I made sure that there was a file named pg_hba.conf with a at least the following fields:

# TYPE    DATABASE    USER    ADDRESS    METHOD

local          all                    all                              trust

Then you should create a file in C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.3\bin\ called pgpass.conf with the following contents:

localhost:4432:QSR:your_service_accountSmiley Tongueostgresql_superuser_password

where the last two fields are your service account username and postgresql superuser password. These fields are separated by a colon.

I then created a Powershell script to backup all the relevant data:

$PostGreSQLLocation = "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.3\bin"

$PostGresBackupTarget = "\\some_shared_backup_location"

$SenseProgramFiles = "C:\Program Files\Qlik\Sense"

$SenseProgramData = "C:\ProgramData\Qlik\Sense"

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

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

md $PostGresBackupTarget\$StartTime\Log

md $PostGresBackupTarget\$StartTime\Apps

md $PostGresBackupTarget\$StartTime\Repository\Content

md $PostGresBackupTarget\$StartTime\Repository\Extensions

write-host "Folders Created..."

& 'C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.3\bin\pg_dump.exe' --host localhost --port 4432 --username "postgres" --no-password  --format tar --blobs --verbose --file "$PostGresBackupTarget\$StartTime\QSR_backup_$Today.tar" QSR

write-host "PostgreSQL database backed up..."

copy $SenseProgramData\Log\*.* $PostGresBackupTarget\$StartTime\Log

copy $SenseProgramData\Apps\*.* $PostGresBackupTarget\$StartTime\Apps

copy $SenseProgramData\Repository\Content\*.* $PostGresBackupTarget\$StartTime\Repository\Content

copy $SenseProgramData\Repository\Extensions\*.* $PostGresBackupTarget\$StartTime\Repository\Extensions

write-host "Data Copied..."

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

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

#pause

You can add steps to stop and then start the relevant service by using the stop-service and start-service commands, or even the stop-process and start-process commands, in Powershell.

Comments
Not applicable

Thanks Andries ! great article and exactly what I was after

You're right, some of the existing docs are a little light on truly automating the whole backup process.

We've just gone to 3.2 with shared persistence which the existing RSM tool doesn't support (we tried it and realised that it runs the installer during the restore process without parameters for setting up shared persistance again !!). Good lesson learned.

Have gone back to using your approach in Powershell, thanks again for sharing

ricardo_tamagni
Contributor

Thanks Andries. Very nice work.

To copy the apps I had to change the script to
"Copy $SenseProgramData\Apps\* $PostGresBackupTarget\$ StartTime\Apps" so that I would copy all the apps (even those that have no extension). is correct ?

And the same to copy the log 's ?

Not applicable

Andries, we augmented your script a bit to stop then restart the qlik services and also to reference our shared persistance folder, otherwise it worked perfectly, thanks again !

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

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

$PostGreSQLLocation = "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.3\bin"

$PostGresBackupTarget = "C:\Qlik_Backups"

# Shared Persistance Folder

$SenseProgramData = "C:\QlikShare\"

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

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

write-host "Stopping Qlik Services ...."

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-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 ...."

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 "Restarting Qlik Services ...."

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-host "This backup process started at " $StartTime " and ended at " $EndTime

duffman1968
New Contributor III

Nigel nice modification! I began to do what you did also. Now I will adapt yours!

duffman1968
New Contributor III

Thanks Andries! I needed a backup before I ran some Qlik Sense Cleanup Scripts. This was exactly what I needed.

driesman
New Contributor III

No problem just glad someone found it useful.

This was written in the old days before Repository Snapshot Manager. I am yet to check it out, but I'm hoping it will simplify things.

driesman
New Contributor III

Hi

Thanks again for all the positive feedback.

I haven't looked at this post in a long time, but I have to say that I only use RSM now. It has really made life simpler.

I would love to hear everyone's thoughts on RSM?

kumarkp412
Contributor II

Hi driesman ,

Thanks for this post.

when i implemented the procedure of above mentioned.Every thing work fine expect QSR file creation.

i.e

I am facing the an error as "

pg_dump.exe : pg_dump: [archiver (db)] connection to database "QSR" failed: fe_sendauth: no password supplied

At C:\Backup_repository\Backupscript.ps1:16 char:1

"

Eventhough if a pass a user and password for this connection.NO luck

Can you please help me on this .

Thanks

Kumar KVP

rohitk1609
Valued Contributor II

HI Andries,

I read your comment and tried it and yes it is working fine but I have three concern.

1. Do I need to run your below code in POWERSHELL script every time

cd $env:userprofile\AppData\Roaming\postgresql\

if (Test-Path $env:userprofile\AppData\Roaming\postgresql\pgpass.conf) {

 

} else {

    "localhost:4432:$([char]42)Smiley Tongueostgres:INSERTYOURSUPERUSERPASSWORD" | set-content pgpass.conf -Encoding Ascii

}


2. After Implementing, If I am taking the backup manually,it is not asking me for superuserpassword, It is taking directly from pgpass.conf, how can i change it as it was before ?


3. The Doc which made me to land this page, it is asking for pg_hba.conf in "bin' folder but my backup is getting generated without it, how it is possible ?


Please add your inputs asap


Thanks,

Rohit


olivieresmieu
Visitor

hi kvp kumar, for your "QSR" failed: fe_sendauth: no password supplied", do as follows:

1)place pgpass.conf into %APPDATA%\postgresql\

2)restart the "Qlik Sense Repository Database" (may be not mandatory actually)

3) run  .\psql.exe --port=4432 --username=postgres --dbname=QSR --no-password

should pass through now

Reason:

i guess the pgpass.conf depends on the postgre version

https://www.postgresql.org/docs/9.3/static/libpq-pgpass.html

Olivier

Version history
Revision #:
1 of 1
Last update:
‎07-08-2016 02:19 AM
Updated by: