topic Re: Qlik Sense Repository Backup Automation in Management & Governance

Qlik Sense Repository Backup Automation

s_kabir_rab — Fri, 16 Feb 2018 12:23:49 GMT

HI,

I have been looking to automate the QS repository backup process using PS. So far I have found some really helpful posts here (Automating a Qlik Sense Site backup ) that has worked for people. I have tried that approach but I am stuck at the authentication to the postgres. I get the following error -

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

Anyone else having or had this issue that you have managed to resolve?

I have created the "pgpass.conf" in "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.6\bin\" as per below -

localhost:4432:QSR:postgres:[superuserpassword]

I have also created "pg_hba.conf" in "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.6\share" as per below -

# Allow any user on the local system to connect to any database with

# any database user name using Unix-domain sockets (the default for local

# connections).

# TYPE DATABASE USER ADDRESS METHOD

local all all trust

My PS script look as per below -

$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.6\bin"

$PostGresBackupTarget = "C:\Qlik_Backups"

# Shared Persistance Folder

$SenseProgramData = "...."

$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\QSR_backup_$Today.tar" QSR

write-host "PostgreSQL backup Completed"

Any help with this would be much appreciated. or a solution as to how we can automate the repository backup process.

Thanks

Re: Qlik Sense Repository Backup Automation

Levi_Turner — Fri, 16 Feb 2018 20:22:38 GMT

So the .pgpass file needs to be in the user's profile doing the pg_dump command. Reference: https://www.postgresql.org/docs/9.6/static/libpq-pgpass.html

In PowerShell, this style of approach should work:

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

}

Re: Qlik Sense Repository Backup Automation

s_kabir_rab — Sat, 17 Feb 2018 23:37:53 GMT

Thank you for this. I looked at the %APPDATA% and since there were no postgres folder created there, I thought the postgres version used for Qlik perhaps been modified to use the folder structure confidence with-in the Qlik installation directory. This worked like a charm. Thank you again for the prompt response.

Thanks

Re: Qlik Sense Repository Backup Automation

rzenere_avvale — Sun, 06 May 2018 20:14:54 GMT

Thank you Levi, your answer solved my problem too.

I was having a similar issue using the QlikSenseUtil inside a powershell: even if I passed all the necessary parameter in the call, once I scheduled on Task Manager the execution of the script, it would stop waiting for the password (as a fact, running manually the script would work smoothly)...

Again many thanks,

Riccardo

Re: Qlik Sense Repository Backup Automation

rohitk1609 — Mon, 04 Jun 2018 11:58:47 GMT

HI Levi,

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

Re: Qlik Sense Repository Backup Automation

Levi_Turner — Mon, 04 Jun 2018 14:11:47 GMT

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

}

It's a conditional, so it checks whether it exists and creates it if it is needed. It would be wise to use that at the beginning but so long as the pgpass.conf file exists in the profile of the user executing the pg_dump, then you would not need a new pgpass.conf file.

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 ?

Remove $env:userprofile\AppData\Roaming\postgresql\pgpass.conf

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 ?

What doc are you referring to? The pg_hba.conf file specifies the following:

What user accounts are allowed to logon to PostgreSQL
From what network address are those accounts permitted to logon
What authentication mechanism those accounts are configured to use

It basically is the server side configuration which can necessitate the usage of the pgpass.conf to pass the password for a silent backup.

Re: Qlik Sense Repository Backup Automation

rohitk1609 — Mon, 04 Jun 2018 17:10:34 GMT

Few points below, Please correct me if I am wrong:

1. "userprofile\AppData\Roaming\postgresql\' doesn't exists, we need to create "postgresql" folder and then run the powershell script then it will create the pgpass.conf file.

2. Remove $env:userprofile\AppData\Roaming\postgresql\pgpass.conf means if I run above script with removal of it then it will again start asking password when I will create backup manually.

3. I do create pgpass.conf then put it to \bin folder and pg_bha.conf into 9.6/share folder.

4. My policy for running the power shell script is RemoteSigned.

Please correct me if I am wrong.

Re: Qlik Sense Repository Backup Automation

rohitk1609 — Tue, 05 Jun 2018 04:56:27 GMT

Hey Levi,

I removed the path from you code Remove $env:userprofile\AppData\Roaming\postgresql\pgpass.conf and my new code is :

if (Test-Path ) {

} else {

"localhost:4432:QSR:postgres:QlikSecure" | set-content pgpass.conf -Encoding Ascii

}

but still it is not becoming as it was, means it is still not asking the password when I am doing backup manually

Re: Qlik Sense Repository Backup Automation

insightbristol — Fri, 07 Jun 2019 15:31:15 GMT

An alternative method is to use Task Scheduler to run 2 batch files, eg:

DatabaseBackup.bat

ren "D:\Qlik\Backups\1st_Of_Month" "1st_Of_Month_bak"

mkdir "D:\Qlik\Backups\1st_Of_Month\Database"

C:\"Program Files"\Qlik\Sense\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe -backup -f -databaseHostname="Localhost" -databasePassword="CHANGEME" -path="D:\Qlik\Backups\1st_Of_Month\Database" -rootPath="\\SERVERNAME\SHAREDFOLDERNAME"

QVD&QlikShareBackup.bat

mkdir "D:\Qlik\Backups\1st_Of_Month\QlikShare"

xcopy "D:\QlikShare" "D:\Qlik\Backups\1st_Of_Month\QlikShare" /Y /H /E /F /I

mkdir "D:\Qlik\Backups\1st_Of_Month\QVD"

xcopy "D:\Qlik\QVD" "D:\Qlik\Backups\1st_Of_Month\QVD" /Y /H /E /F /I

rmdir "D:\Qlik\Backups\1st_Of_Month_bak"

exit

*This method only keeps a record of the last month, but you can tweak the logic to match more/less frequent requirements, or use folder names linked to the date using:

%date:/=-%

Re: Qlik Sense Repository Backup Automation

mountaindude — Thu, 10 Sep 2020 17:40:40 GMT

Late to the party, but here's my contribution to this topic.

Basically the same as Kabir's, but extended for multi-node Sense clusters, as well as starting and stopping Sense services remotely on all the nodes. Also added the logging service to the list of services stopped/started.

Adapt to your specific situation, put the script in a .ps1 Powershell script and schedule using the Windows Scheduler, and you have a pretty solid backup solution for the QSEoW repository and Log databases.

# Script should be executed on the server where the Postgres repository database is running. # The script will connect to Sense servers as specified below, shutting down Sense services before doing the db backup. # Once backup is done the Sense services will be started again. # Automatic execution of this script assumes there is a pgpass.conf file present in the roaming profile of the user # executing the script. For user joewest this would mean 'C:\Users\joewest\AppData\Roaming\postgresql\pgpass.conf' # # To create that file, execute the following while in the C:\Users\joewest\AppData\Roaming\postgresql (in the case of user joewest) directory: # "localhost:4432:$([char]42):postgres:ENTER_POSTGRES_PASSWORD_HERE" | set-content pgpass.conf -Encoding Ascii # Regarding firewalls. The following ports must be allowed inbound on the various Sense servers where Qlik Sense services are running, to allow those services to be stopped/started: # TCP port: 80,139,443,445,5985,5986 # UDP port: 137,138 # Ephemeral ports: (TCP 1024-4999, 49152-65535) # # In Windows Server firewall two rules will be needed, one for TCP and one for UDP. Make sure not to open the firewall more than necessary! $Today = Get-Date -UFormat "%Y%m%d_%H%M" $StartTime = Get-Date -UFormat "%Y%m%d_%H%M" $PostgresLocation = "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.6\bin" $PostgresBackupTarget = "C:\Qlik_Backups" # Servers where QSEoW services are running. Only host names should be listed beloew, not FQDNs $servers = @( "senseserver1", "senseserver2" ) # Loop over all servers in the QSEoW cluster, shutting down all services on each. foreach($server in $servers) { write-host "" write-host "Stopping Qlik Services on $server...." # Suffix the Stop-Service comand with "-WarningAction SilentlyContinue" to suppress warning messages when a service takes long to stop Get-Service -ComputerName $server -Name QlikSenseProxyService | Stop-Service -Verbose Get-Service -ComputerName $server -Name QlikSenseEngineService | Stop-Service -Verbose Get-Service -ComputerName $server -Name QlikSenseSchedulerService | Stop-Service -Verbose Get-Service -ComputerName $server -Name QlikSensePrintingService | Stop-Service -Verbose Get-Service -ComputerName $server -Name QlikSenseServiceDispatcher | Stop-Service -Verbose Get-Service -ComputerName $server -Name QlikSenseRepositoryService | Stop-Service -Verbose Get-Service -ComputerName $server -Name QlikLoggingService | Stop-Service -Verbose } cd $PostgresLocation write-host "" write-host "Backing up PostgreSQL Repository Database ...." .\pg_dump.exe -h localhost -p 4432 -U postgres -b -F t -f "$PostgresBackupTarget\QSR_backup_$Today.tar" QSR write-host "Backing up PostgreSQL Log Database ...." .\pg_dump.exe -h localhost -p 4432 -U postgres -b -F t -f "$PostgresBackupTarget\QLogs_backup_$Today.tar" QLogs write-host "" write-host "PostgreSQL backup Completed" # Loop over all servers in the QSEoW cluster, shutting down all services on each. foreach($server in $servers) { write-host "" write-host "Starting Qlik Services on $server...." Get-Service -ComputerName $server -Name QlikSenseProxyService | Start-Service -Verbose Get-Service -ComputerName $server -Name QlikSenseEngineService | Start-Service -Verbose Get-Service -ComputerName $server -Name QlikSenseSchedulerService | Start-Service -Verbose Get-Service -ComputerName $server -Name QlikSensePrintingService | Start-Service -Verbose Get-Service -ComputerName $server -Name QlikSenseServiceDispatcher | Start-Service -Verbose Get-Service -ComputerName $server -Name QlikSenseRepositoryService | Start-Service -Verbose Get-Service -ComputerName $server -Name QlikLoggingService | Start-Service -Verbose }