Qlik Sense Repository Backup Automation

[s_kabir_rab]

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

K

[Levi_Turner · Accepted solution]

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

}

[s_kabir_rab]

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

K

[rzenere_avvale]

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

[rohitk1609]

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

[Levi_Turner]

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.

[rohitk1609]

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.

[rohitk1609]

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

[insightbristol]

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:/=-%

[mountaindude]

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
}