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

Mario_Petre · ‎2022-05-24

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 an existing Qlik Sense Repository Database
The Upgrade
Compatibility with PostgreSQL installers
Troubleshooting and FAQ
Video Walkthrough
Related Content

Supported Scenarios

Upgrades

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

Qlik Sense February 2022 to Qlik Sense November 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

Run the Qlik PostgreSQL Installer as an administrator
Click on Install
Accept the Qlik Customer Agreement
Set your Local database settings and click Next. You will use these details to connect other nodes to the same cluster.
Set your Database superuser password and click Next
Set the database installation folder, standard: C:\Program Files\PostgreSQL\14
Set the database data folder, standard: C:\Program Files\PostgreSQL\14\data
Review your settings and click Install, then click Finish
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.
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 November 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

Stop all services on rim nodes
On your Central Node, stop all services except the Qlik Sense Repository Database
Run the Qlik PostgreSQL Installer. An existing Database will be detected.
Highlight the database and click Upgrade
Read and confirm the (a) Installer Instructions as well as the Qlik Customer Agreement, then click (b) Next.
Provide your existing Database superuser password and click Next.
Define your Database backup path and click Next.
Define your Install Location (default is prefilled) and click Next.
Define your database data path (default is prefilled) and click Next.
Review all properties and click Upgrade.

The review screen lists the settings which will be migrated. No manual changes are required post-upgrade.
The upgrade is completed. Click Close.
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.
Start all services except the Qlik Sense Repository Database service.
Start all services on your rim nodes.
Validate that all services and nodes are operating as expected. The original database folder in C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\X.X_deprecated
Uninstall the old Qlik Sense Repository Database service.

This step is required. Failing to remove the old service will lead the upgrade or patching issues.
1. Open a Windows File Explorer and browse to C:\ProgramData\Package Cache
2. From there, search for the appropriate msi file.
  
  If you were running 9.6 before the upgrade, search PostgreSQL.msi
  
  If you were running 12.5 before the upgrade, search PostgreSQL125.msi
3. The msi will be revealed.
4. Right-click the msi file and select uninstall from the menu.
Re-install the PostgreSQL binaries. This step is optional if Qlik Sense is immediately upgraded following the use of QPI. The Sense upgrade will install the correct binaries automatically.

Failing to reinstall the binaries will lead to errors when executing any number of service configuration scripts.
1. Open a Windows Command prompt as administrator
2. Navigate to the location of the previously located .msi file
  
  Example:
  
  cd C:\ProgramData\Package Cache\{GUID}
3. Run the following command, replacing PostgreSQL125.msi with the correct .msi depending on your previous version used.
  
  Qlik Sense August 2023 and later: PostgreSQL14.msi
  Qlik Sense February 2022 to May 2023: PostgreSQL125.msi
  
  msiexec.exe /qb /i "PostgreSQL125.msi" SKIPINSTALLDBSERVICE="1" INSTALLDIR="C:\Program Files\Qlik\Sense"
  
  This will re-install the binaries without installing a database.

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

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 and FAQ

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

piotrek_w · ‎2022-10-11

Hi @Sonja_Bauernfeind

Thank you for your answer.

I tested it. Seems like after I installed the new postgreSQL I have a problem to do the upgrade. I am trying to upgrade from May 2022 to August 2022.

Apparently installation program cannot find some part of the database repository.

Any idea how to proceed?

BR,

Sonja_Bauernfeind · ‎2022-10-12

Hello @piotrek_w The screenshot you provided shows the postgresql-x64-12 service to not be started. Has it successfully started since you upgraded it?

piotrek_w · ‎2022-10-12

Hello @Sonja_Bauernfeind

Yes it was successfully started. I've checked whether everything is fine after the postgresql upgrade - in QMC and in Hub. After that I deleted the old postgresql db.

It is turned off on the screen as I did it for the upgrade purpose - usually services should not be running during the upgrade process... Should I turn it on and then do the upgrade?

BR,

Piotr

Sonja_Bauernfeind · ‎2022-10-13

Hello @piotrek_w

Based on the error messages we are missing specific DBs. Have you looked into this article? Unable to upgrade Qlik Sense with missing 'SenseServices', 'QSMQ', and 'Licenses' database for respe...

All the best,
Sonja

Sonja_Bauernfeind · ‎2022-10-13

Hello @piotrek_w

As a follow-up: In your previous comment you mentioned "After that I deleted the old postgresql db"

Do you mean the old service or did you remove the actual DB? The DB should not be removed.

You can also log a ticket with support to have this looked at directly.

All the best,
Sonja

piotrek_w · ‎2022-10-13

Hello @Sonja_Bauernfeind

I deleted the old db. This was a part of instruction:

c:\cd ProgramData\Package Cache
c:\ProgramData\Package Cache\dir /s PostgreSQL.msi
The folder containing PostgreSQL.msi will be revealed.
Right click on the PostgreSQL.msi file and select uninstall from the menu.

After that the old service disappeared from services list.

I can see that I have all necessary databases running under 12.5.

BR,

Piotr

mikaelsc · ‎2022-10-13

hey @piotrek_w (you have more luck getting reply's from support 😉 , got the same issue as yours a few posts above... )

what did the trick for me was to

- keep the postgresql-x64-12 service running

- open the C:\Program Files\Qlik\Sense\Repository\PostgreSQL\12.5\pgAdmin 4\bin\pgadmin.exe

and click on the "missing" databases: (they appear disconnected at first)

- then the installer of qs august 22 is ok.

(ensure you have a backup of everything, as i think it is not recommended to keep (postgresql-x64-12) service running during an install)...

ps: after that i got an issue with an old patch not getting uninstalled (may 22 patch 6)

piotrek_w · ‎2022-10-13

Thanks @Mikaelsc for your hints.

I am not a big fan of doing any unsupported things during upgrade or installation.

As Qlik released this new software to upgrade postgresql db then I my humble opinion it should be bullet proof. I am going to upgrade multinode environment with hundreds of users.

I am already registered a Qlik support case. I hope that I get some clear/supported solution.

BR,

Piotr

BoB_Qlik_Support · ‎2022-10-15

Hi ,

Which version of pg admin should be used with postgresql-x64-12 . I am having pgadmin 4 V3 and I am not able to query the public tables with right click. (with PostgreSQL 12 and PgAdmin 4.3)

I m getting this error-> ERROR: column rel.relhasoids does not exist"

Any suggestions on this.

Regards,

Jyoti

ifytra · ‎2022-10-16

Hi Jyoti,

In our case, we have successfully used the latest version of pgAdmin (v6.14) with PostgreSQL v12.5.

URL: https://www.pgadmin.org/download/pgadmin-4-windows/

Thanks,

iLiAS

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