Qlik Community

Qlik Sense Deployment & Management

Discussion board where members learn more about Qlik Sense Installation, Deployment and Management.

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

Qlik Sense Repository Backup Automation

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

Kabir
Please do not forget to the mark the post if you find it useful or provides you the solutions 🙂
1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Re: Qlik Sense Repository Backup Automation

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

}

View solution in original post

8 Replies
Highlighted
Employee
Employee

Re: Qlik Sense Repository Backup Automation

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

}

View solution in original post

Partner
Partner

Re: Qlik Sense Repository Backup Automation

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

Kabir
Please do not forget to the mark the post if you find it useful or provides you the solutions 🙂
Partner
Partner

Re: Qlik Sense Repository Backup Automation

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
Valued Contributor III

Re: Qlik Sense Repository Backup Automation

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

Employee
Employee

Re: Qlik Sense Repository Backup Automation

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
Valued Contributor III

Re: Qlik Sense Repository Backup Automation

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
Valued Contributor III

Re: Qlik Sense Repository Backup Automation

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
New Contributor

Re: Qlik Sense Repository Backup Automation

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