Upgrading and unbundling the Qlik Sense Repository Database using the Qlik PostgreSQL Installer

[Mario_Petre]

In this article, we walk you through the requirements and process of how to upgrade and unbundle an existing Qlik Sense Repository Database (see supported scenarios) as well as how to install a brand new Repository based on PostgreSQL. We will use the Qlik PostgreSQL Installer (QPI).

For a manual method, see How to manually upgrade the bundled Qlik Sense PostgreSQL version to 12.5 version.

Using the Qlik Postgres Installer not only upgrades PostgreSQL; it also unbundles PostgreSQL from your Qlik Sense Enterprise on Windows install. This allows for direct control of your PostgreSQL instance and facilitates maintenance without a dependency on Qlik Sense. Further Database upgrades can then be performed independently and in accordance with your corporate security policy when needed, as long as you remain within the supported PostgreSQL versions. See How To Upgrade Standalone PostgreSQL.

Index

• Supported Scenarios
• Upgrades
• New installs
• Requirements
• Installing anew Qlik Sense Repository Database using PostgreSQL
• Qlik PostgreSQL Installer - Download Link
• Upgrading anexisting Qlik Sense Repository Database
• The Upgrade:
• Finalizing the Upgrade/Uninstalling the old Database Service:
• Compatibility with PostgreSQL installers
• Troubleshooting
• Video Walkthrough
• Related Content

Supported Scenarios

Upgrades

The following versions have been tested and verified to work with QPI 1.3.0 (May 2023): 

February 2022 to May 2023.

If you are on any version prior to these, upgrade to at least February 2022 before you begin.  

Note that November 2022 and later do not support 9.6, and a warning will be displayed during the Qlik Sense upgrade. 

New installs

The Qlik PostgreSQL Installer supports installing a new Qlik Sense Repository Database cluster on a new server, setting up a new environment or migrating an existing Database to a separate host.

Requirements

• Review the QPI Release Notes before you continue
• Using the Qlik PostgreSQL Installer on a patched Qlik Sense version can lead to unexpected results. If you have a patch installed, either:
  • Uninstall all patches before using QPI or
  • Upgrade to an IR release of Qlik Sense which supports QPI
• This installer will not detect a standalone PostgreSQL database installed by any other means, other than through an official Qlik installer package.
• The user who runs the installer must be an administrator.
• The backup destination must have sufficient free disk space to dump the existing database  
• The backup destination must not be a network path or virtual storage folder. It is recommended the backup is stored on the main drive. 
• The installer itself does not provide an automatic rollback feature.
• There will be downtime during this operation, please plan accordingly 

Installing a new Qlik Sense Repository Database using PostgreSQL

1. Run the Qlik PostgreSQL Installer as an administrator 
   
   
2. Click on Install 
   
   
3. Accept the Qlik Customer Agreement 
   
   
4. Set your Local database settings and click Next. You will use these details to connect other nodes to the same cluster. 
   
   
5. Set your Database superuser password and click Next
   
   
6. Set the database installation folder, standard: C:\Program Files\PostgreSQL\14
   
   
7. Set the database data folder, standard: C:\Program Files\PostgreSQL\14\data
   
   
8. Review your settings and click Install, then click Finish
   
   
9. Start installing Qlik Sense Enterprise Client Managed. Choose Join Cluster option.
   
   The Qlik PostgreSQL Installer has already seeded the databases for you and has created the users and permissions. No further configuration is needed.
   
   
10. The tool will display information on the actions being performed. Once installation is finished, you can close the installer.  
    
    If you are migrating your existing databases to a new host, please remember to reconfigure your nodes to connect to the correct host. How to configure Qlik Sense to use a dedicated PostgreSQL database 
    

Qlik PostgreSQL Installer - Download Link

Download the installer here.

Qlik PostgreSQL installer version 1.3.0 Release Notes

 

Upgrading an existing Qlik Sense Repository Database

The following versions have been tested and verified to work with QPI 1.3.0 (May 2023): 

February 2022 to May 2023.

If you are on any version prior to these, upgrade to at least February 2022 before you begin.  

Note that November 2022 and later do not support 9.6, and a warning will be displayed during the Qlik Sense upgrade. 

The Upgrade:

1. Stop all services on rim nodes 
   
   
2. On your Central Node, stop all services except the Qlik Sense Repository Database 
   
   
   
   
3. Run the Qlik PostgreSQL Installer. An existing Database will be detected. 
   
   
   
   
4. Highlight the database and click Upgrade 
   
5. Read and confirm the (a) Installer Instructions as well as the Qlik Customer Agreement, then click (b) Next.
   
   
   
   
6. Provide your existing Database superuser password and click Next.
   
   
7. Define your Database backup path and click Next.
   
   
   
   
8. Define your Install Location (default is prefilled) and click Next.
   
   
   
   
9. Define your database data path (default is prefilled) and click Next.
   
   
    
10. Review all properties and click Upgrade.
    
    The review screen lists the settings which will be migrated. No manual changes are required post-upgrade.
    
    
    
    
11. The upgrade is completed. Click Close.
     
12. Open the Windows Services Console and locate the Qlik Sense Enterprise on Windows services.
    
    You will find that the Qlik Sense Repository Database service has been set to manual. Do not change the startup method. 
    
    
    
    You will also find a new postgresql-x64-12 service. Do not rename this service. 
    
    
    
    
13. Start all services except the Qlik Sense Repository Database service. 
    
    
14. Start all services on your rim nodes.
    
    
15. Validate that all services and nodes are operating as expected. The original database folder in C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\X.X_deprecated

If the upgrade was unsuccessful and you are missing data in the Qlik Management Console or elsewhere, contact Qlik Support.  

1.  

Finalizing the Upgrade/Uninstalling the old Database Service:

Before upgrading Qlik Sense Enterprise on Windows to the next major release

• for example: November 2022 or higher versions,

The old Qlik Sense Repository Database service must be removed from the server.

Perform the following steps to delete the Qlik Sense Repository Database Service:

1. Open a command prompt and run the command:
   cd "C:\ProgramData\Package Cache"
   
   
2. From there, run:
   
   If 9.6:
   From "c:\ProgramData\Package Cache\"
   Run following command: dir /s PostgreSQL.msi
   
   If 12.5:
   From "c:\ProgramData\Package Cache\"
   Run following command: dir /s PostgreSQL125.msi
   
   
3. The folder containing the msi will be revealed.
   
   
4. Right-click on the msi file and select uninstall from the menu.

Compatibility with PostgreSQL installers

This version of the upgrade tool is fully compatible with the official PostgreSQL installers from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads.

If you have previously used the Qlik Postgres Installer (version 1.2.1 or earlier), you can simply install the latest PostgreSQL version (within your major release).

Example: You have used the old QPI to upgrade to 12.5. You can now easily upgrade to a later version in the same major release, such as 12.15.

Troubleshooting

1. If the installation crashes, the server reboots unexpectedly during this process, or there is a power outage, the new database may not be in a serviceable state. Installation/upgrade logs are available in the location of your temporary files, for example:
   
   C:\Users\Username\AppData\Local\Temp\2
   
   A backup of the original database contents is available in your chosen location, or by default in:
   
   C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\backup\X.X
   
   The original database data folder has been renamed to:
   
   C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\X.X_deprecated
   
   
2. Upgrading Qlik Sense after upgrading PostgreSQL with the QPI tool fails with:
   
   This version of Qlik Sense requires a 'SenseServices' database for multi cloud capabilities. Ensure that you have created a 'SenseService' database in your cluster before upgrading. For more information see Installing and configuring PostgreSQL.
   
   See Qlik Sense Upgrade fails with: This version of Qlik Sense requires a _ database for _.
   
   To resolve this, start the postgresql-x64-XX service.

Video Walkthrough

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support. The video in this article was recorded in a earlier version of QPI, some screens might differ a little bit.

Related Content

Qlik PostgreSQL installer version 1.3.0 Release Notes  
Techspert Talks - Upgrading Qlik Sense Repository Service
Backup and Restore Qlik Sense Enterprise documentation
Migrating Like a Boss
Optimizing Performance for Qlik Sense Enterprise
Qlik Sense Enterprise on Windows: How To Upgrade Standalone PostgreSQL
How-to reset forgotten PostgreSQL password in Qlik Sense
How to configure Qlik Sense to use a dedicated PostgreSQL database
Troubleshooting Qlik Sense Upgrades 

[Lauri]

Please ignore my last question about converting a QSR from 9.6 to 12.5. It ended up working as is.

In case this info is helpful to someone else, here's a quick rundown of the (ugly) sequence of events I experienced when trying to upgrade Qlik Sense as well as Postgres:

1. On Dec 17, I successfully upgraded QS Enterprise Nov 2021 to May 2022 r10. 
2. I immediately ran the Qlik Postgres Installer, successfully going from 9.6 to 12.5. Qlik Sense ran as expected, apps looked good, QMC looked good. I didn't yet remove the old Repository Database, just in case...
3. I then attempted to upgrade QS to Nov 2022. I hadn't triple-checked that the old Repository Database service was stopped, and the installation got stuck (see screenshot above). I wonder if it would have succeeded, had I simply stopped the Repository Database service...
4. I quit the upgrade, rebooted, and tried again. The installer let me enter all the credentials and other parameters and then failed  with "Upgrade failed - an error has occured." After various iterations with no improvement, I contacted support.
5. With Qlik Support's help, I did this:
   1. Uninstalled Postgres 
   2. Uninstalled QS May 2022
   3. Deleted the old Repository Database service (here's how)
   4. Tried to install QS May 2022 fresh, including silently; it kept failing, and the logs showed that it was because Postgres was already installed (even though we'd uninstalled it).
6. I finally found a way to remove the remnants of Postgres: 
   1. The QS installation log provided the file name and temp folder name of the PostgresSQL.msi that it found so offensive. It wasn't in the C:\Package Cache folder like you'd expect.
   2. I searched the Windows Registry for the folder name (one of those long GUID numbers) and voila, there was what I needed -- its location on C, and even the uninstall syntax to make it go away. Running that from a command prompt launched a handy Qlik utility that removed the msi and a few other files from the c:windows\Installer folder.

After that, the silent install of QS May 2022 went flawlessly. (Well, not entirely, but that was my fault. I forgot to define the hostname, and Qlik came up with something creative. But there's a fix for that.) I'm now a fan of the silent install; it seems a lot faster than the noisy way.

I then ran the restore command on the QSR backup I'd taken after upgrading to May 2022 (but before upgrading Postgres to 12.5). After recreating the local security certificates, all is well.

I guess if I had one request for Qlik, it'd be to have the QS installer automatically stop all QS services. We humans are fallible.

[JPT]

I just tried updating Postgres to 12.5 using the installer on February 2022 Patch 2, I got the error at the end: Error restoring backup. In the log

Qlik_PostgreSQL_Installer_20221220033128.log:

2022-12-20 15:38:22.678 +01:00 [ERR] PostgreSQL installation failed with Exit code: 1. See installer logs for more details.
2022-12-20 15:38:22.678 +01:00 [ERR] '*$ZV82' is not recognized as an internal or external command,
2022-12-20 15:38:22.678 +01:00 [ERR] operable program or batch file.
2022-12-20 15:38:22.679 +01:00 [ERR] Installation failed, exception when trying to install. See installation log for details.
2022-12-20 15:38:22.683 +01:00 [INF] Task:InstallPostgreSqlExecutableTask completed with exception: Qlik.PostgreSQL.Installer.Utility.Exceptions.InstallPostgreSqlExecutableTaskFailedException: Installation failed, exception when trying to install. See installation log for details.
---> Qlik.PostgreSQL.Installer.Utility.Exceptions.InstallPostgreSqlExecutableTaskFailedException: PostgreSQL installation failed with Exit code: 1. See installer logs for more details.
at Qlik.PostgreSQL.Installer.Utility.InstallerTask.InstallPostgreSqlExecutableTask.SilentInstallExecutable(String pathToExecutable, String baseDirectory, InstallerFlowContext context)
--- End of inner exception stack trace ---
at Qlik.PostgreSQL.Installer.Utility.InstallerTask.InstallPostgreSqlExecutableTask.SilentInstallExecutable(String pathToExecutable, String baseDirectory, InstallerFlowContext context)
at Qlik.PostgreSQL.Installer.Utility.InstallerTask.InstallPostgreSqlExecutableTask.Execute(InstallerFlowContext context)
at Qlik.PostgreSQL.Installer.Utility.InstallerTasksFlow.InstallerTaskExecutor.Execute(Queue`1 tasks, InstallerFlowContext context)
2022-12-20 15:40:03.037 +01:00 [INF] Installation cancelled by user

So I would assume I should now check the install-postgresql.log but when I scroll through that it does not return me any error, as in I would assume it would say Failed processing 1 files or something along those lines but everywhere it says Failed processing 0 files..

If I check services.msc I can see that there is now a postgresql-x64-12 service and the old Qlik Sense Repository Database is set to manual. Any advice on how to continue?

I have installled Patch 13 and set the postgresql-x64-12 service to manual and the Qlik Sense Repository Database to automatic for now but would like to upgrade Qlik Sense to November 2022 Patch 2.

[Sebastian_Linser]

Hello @JPT 

could you please open a case with the Qlik Support for that.

best regards

Sebastian

[Serkan]

Hi,

after succeeded upgradae we noticed that the Qlik Sense Repository Database server startup type changed to manual due to the upgrade and the new "own service" in the PostgreSQL Version 12.5.

the question what I would like to ask is if there will be occur an issue during Qlik Sense Upgrades or what happens when e.g. some user will start this "deactivated service" - QSRD?

Thanks

[Sebastian_Linser]

Hello @Serkan the startup type manual is working as designed, it is built in so you can roll it back in case you need to.

Starting the service would normally not have any effect since the second service is already bound to the port 4432. At the end if you validated that all is fine you should remove the old Qlik Sense Repository Database service from the system.

[diwakarreddy]

Hello @Sebastian_Linser @Sonja_Bauernfeind

Is there any plan from Qlik to include PostgreSQL upgrade with the QlikSense upgrade ?

Like upgrading Qliksense to latest version will upgrade QlikSense and PostgreSQL 9.6 to Postgres 12.x  in single step. Instead of first upgrading PostgreSQL and then upgrading QlikSense .

Regards,

Diwakar.

[Sebastian_Linser]

Hello @diwakarreddy  the short answer is no. It need to be done outside of the Qlik Sense installation, a rollback in the Qlik Sense Setup normally means you need to install from scratch for that node. (with the backups at the end to replace) Which is the save way to use the tool provided here before the Sense upgrade, or to uninstall Qlik Sense, install it again (which will trigger the 12.5 bundled PostgreSQL DB) and restore the DB.

[Serkan]

@Sebastian_Linser Thanks for your reply. Could you give me some input how to remove the old QSRDS from the system?

[Sonja_Bauernfeind]

Hello @Serkan 

See the Upgrade/Uninstalling the old Database Service section of this article. Is this what you're looking for?

I renamed the header to make it easier to identify on a glance.

All the best,
Sonja 

[ifytra]

@Serkan Have a look at the following article

How to remove the Qlik Sense Repository Database S... - Qlik Community - 1711195

and especially the section about the deletion of the Database Service.

Notes

1. Alter the command accordingly if needed.

2. Keep a backup before performing the deletion.