Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More

Automating a Qlik Sense Site backup

Partner
Partner

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_account:postgresql_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

0 Likes
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 ?

0 Likes
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!

0 Likes
duffman1968
New Contributor III

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

0 Likes
Partner
Partner

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.

0 Likes
Partner
Partner

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?

0 Likes
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

0 Likes
rohitk1609
Valued Contributor III

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):postgres: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


0 Likes
olivieresmieu
New Contributor

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

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