7 Replies Latest reply: Jun 5, 2018 12:56 AM by Rohit Kumar RSS

    Qlik Sense Repository Backup Automation

    Shirajul 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

        • Re: Qlik Sense Repository Backup Automation
          Levi Turner

          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
              Shirajul 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

              • Re: Qlik Sense Repository Backup Automation
                Riccardo Zenere

                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
                  Rohit Kumar

                  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

                      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
                          Rohit Kumar

                          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
                            Rohit Kumar

                            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