How to backup or restore the PostgreSQL database in Qlik Sense

[Bjorn_Wedbratt]

For full instructions on how to backup and restore a Qlik Sense site, see the online help under Backing up a Qlik Sense site. For some illustrated documentation on backing up the Qlik Sense site see How To Take Backup And Restore Qlik Sense June 2017 or higher

Note: Backing up the database requires system downtime.

Environment:

• Qlik Sense Enterprise, all versions.

Backup

1. Stop all Qlik Sense Services except for the "Qlik Sense Repository Database" using the Windows Server manager (services.msc)
   •  Qlik Sense Logging Service
   • Qlik Sense Printing Service
   • Qlik Sense Proxy Service
   • Qlik Sense Repository Service
   • Qlik Sense Scheduler Service
   • Qlik Sense Service Dispatcher
2. Create a folder the backup should be stored to. Example: C:\Backup
3. Open a Command Prompt (CMD.exe) as Administrator (elevated permissions)
4. From the Command Prompt, navigate to the repository folder. 
   

   cd "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\<database version>\bin"​

   Note that <database version> reflects the version of your PostgreSQL install. 
5. Execute the backup command
   

   \pg_dump.exe -h localhost -p 4432 -U postgres -v -b -F t -f "c:\backup\QSR_backup.tar" QSR​

   h: Hostname
   p: Port number
   U: User ID
   v: verbosity (adds logging)
   Note: If postgres db password is unknown, see How-to reset forgotten PostgreSQL password in Qlik Sense 

6. Check the backup file side after the command finished. The file size has to not equal zero.
7. If not enough disk space was available and the command failed, add  -T _deletedentitylog to shrink the backup and provide a different drive.
   
   Example:
   

   -T _deletedentitylog -f "E:\backup\QSR_Backup.tar" QSR ​

Restore

For detailed information see Restoring a Qlik Sense site.

1. Place the backed-up repository database on the machine targeted for the restore.
2. Open a Command Prompt with administrator privileges in Microsoft Windows

3. Run the following commands to restore the repository database (adjust the paths as needed):

   cd "%ProgramFiles%\Qlik\Sense\Repository\PostgreSQL\<database version>\bin"

   Note that <database version> reflects the version of your PostgreSQL install. 
   

   createdb -h localhost -p 4432 -U postgres -T template0 QSR​

   If the command fails because a database already exists, run the following command and then repeat the createdb command:

   dropdb -h localhost -p 4432 -U postgres QSR

4. Run the restore command:
   

   pg_restore.exe -h localhost -p 4432 -U postgres -d QSR "c:\backup\QSR_backup.tar"​

 

Other Commands and Related Content:

Drop

"%Programfiles%\Qlik\Sense\Repository\PostgreSQL\9.6\bin\dropdb.exe" -h localhost -p 4432 -U postgres QSR

postgreSQL official help page references

• PG_DUMP https://www.postgresql.org/docs/9.6/app-pgdump.html
• PG_RESTORE https://www.postgresql.org/docs/9.6/app-pgrestore.html