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

Mario_Petre · Mar 7, 2024 5:53:56 AM

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
Known limitations
Installing anew Qlik Sense Repository Database using PostgreSQL
Qlik PostgreSQL Installer - Download Link
Upgrading an existing Qlik Sense Repository Database
The Upgrade
Next Steps and Compatibility with PostgreSQL installers
How do I upgrade PostgreSQL from here on?
Troubleshooting and FAQ
Video Walkthrough
Related Content

Supported Scenarios

Upgrades

The following versions have been tested and verified to work with QPI (1.4.0):

Qlik Sense February 2022 to Qlik Sense November 2023.

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

Qlik Sense November 2022 and later do not support 9.6, and a warning will be displayed during the upgrade. From Qlik Sense August 2023 a upgrade with a 9.6 database is blocked.

New installs

The Qlik PostgreSQL Installer supports installing a new standalone PostgreSQL database with the configurations required for connecting to a Qlik Sense server. This allows 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 (see Installing and Uninstalling Qlik Sense Patches) or
- Upgrade to an IR release of Qlik Sense which supports QPI
The PostgreSQL Installer can only upgrade bundled PostgreSQL database listening on the default port 4432.
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.
There will be downtime during this operation, please plan accordingly
If upgrading to PostgreSQL 14 and later, the Windows OS must be at least Server 2016

Known limitations

Cannot migrate a 14.8 embedded database to a standalone
Using QPI to upgrade a standalone database or a database previously unbundled with QPI is not supported.
The installer itself does not provide an automatic rollback feature.

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
Do not use the standard Qlik Sense folders, such as C:\Program Files\Qlik\Sense\Repository\PostgreSQL\ and C:\Programdata\Qlik\Sense\Repository\PostgreSQL\.
Set the database data folder, standard: C:\Program Files\PostgreSQL\14\data
Do not use the standard Qlik Sense folders, such as C:\Program Files\Qlik\Sense\Repository\PostgreSQL\ and C:\Programdata\Qlik\Sense\Repository\PostgreSQL\.
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 Release Notes

Upgrading an existing Qlik Sense Repository Database

The following versions have been tested and verified to work with QPI (1.4.0):

February 2022 to November 2023.

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

Qlik Sense November 2022 and later do not support 9.6, and a warning will be displayed during the upgrade. From Qlik Sense August 2023 a 9.6 update is blocked.

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-14 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.
If you do not immediately upgrade:
1. Open a Windows File Explorer and browse to C:\ProgramData\Package Cache
2. From there, search for the .msi file appropriate for your currently installed Qlik Sense version
  
  For Qlik Sense August 2023 and later: PostgreSQL14.msi
  Qlik Sense February 2022 to May 2023: PostgreSQL125.msi
3. Right-click the file
4. Click Open file location
5. Highlight the file path, right-click on the path, and click Copy
6. Open a Windows Command prompt as administrator
7. Navigate to the location of the folder you copied
  
  Example command line:
  
  cd C:\ProgramData\Package Cache\{GUID}
  
  Where GUID is the value of the folder name.
8. Run the following command depending on the version you have installed:
  
  Qlik Sense August 2023 and later
  
  msiexec.exe /qb /i "PostgreSQL14.msi" SKIPINSTALLDBSERVICE="1" INSTALLDIR="C:\Program Files\Qlik\Sense"
  
  Qlik Sense February 2022 to May 2023
  
  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 you installed with a custom directory adjust the INSTALLDIR parameter accordingly. E.g. you installed in D:\Qlik\Sense then the parameter would be INSTALLDIR="D:\Qlik\Sense".

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

Next Steps and Compatibility with PostgreSQL installers

Now that your PostgreSQL instance is no longer connected to the Qlik Sense Enterprise on Windows services, all future updates of PostgreSQL are performed independently of Qlik Sense. This allows you to act in accordance with your corporate security policy when needed, as long as you remain within the supported PostgreSQL versions.

Your PostgreSQL database is fully compatible with the official PostgreSQL installers from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads.

How do I upgrade PostgreSQL from here on?

See How To Upgrade Standalone PostgreSQL, which documents the upgrade procedure for either a minor version upgrade (example: 14.5 to 14.8) or a major version upgrade (example: 12 to 14). Further information on PostgreSQL upgrades or updates can be obtained from Postgre directly.

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

Video chapters:

01:00 - What is PostgreSQL used for?
01:19 - Different versions of PostgreSQL
02:38 - Prerequisites for upgrading
03:21 - QS Feb 2022 threshold
04:33 - Downloading the QPI Installer
06:00 - Upgrade process
09:54 - Verifying a successful upgrade
12:08 - Removing the old Database Service
14:16 - Resolving common issues
17:28 - Q&A: When is best time to upgrade?
18:05 - Q&A: Does the QPI Tool use the latest version?
18:27 - Q&A: Which versions should you be careful with?
18:55 - Q&A: What are the Do's and Don'ts?
19:44 - Q&A: What should we upgrade first?
20:28 - Q&A: How to we avoid having to restart the upgrade?
21:22 - Q&A: Can we upgrade the database only?
21:47 - Q&A: Is it possible to lose any custom settings?
22:31 - Q&A: How to tell which version QS is using?
23:35 - Q&A: What is the command to remove the old service?
24:12 - Q&A: What if the database is on the D drive?
24:50 - Q&A: Can the back-up be on ANY local drive?
25:05 - Q&A: Are old version of QS compatible with new PostgreSQL?

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

Sonja_Bauernfeind · ‎2023-02-15

Hello @agigliotti

November 2022 already does not support 9.6 for production (only for upgrade purposes). It will have warned you during the upgrade process. See System Requirements: 9.6x (for upgrade scenarios from previous versions only—no longer supported after February 2023)

What'll happen when you upgrade:

As you mentioned yourself, if you upgrade a Sense system running PostGRE 9.6 to November 2022, it will alert you with this message:

You can continue though! The same will happen for February 2023. There is no hard stop yet for the upgrade itself.

Here are the February 2023 System Requirements: 9.6x (no longer supported after February 2023)

Does this help?

All the best,
Sonja

agigliotti · ‎2023-02-15

@Sonja_Bauernfeind Ok thanks for your support.

Is there any warning about using PostgreSQL 9.6 with Feb '23 version? Maybe something related to the overall performance?
Version Nov' 22 with PostgreSQL 9.6 is working fine.
Anyway do you suggest to upgrade PostgreSQL to 12.5 version?

Best Regards

Sonja_Bauernfeind · ‎2023-02-16

Hello @agigliotti

Anytime! Let's see if I can answer your remaining questions too. I will go into as much detail as possible so that we can also help other people with similar questions.

If by warning you mean the alert that is displayed when you upgrade with PostgreSQL 9.6 installed, then, yes. February 2023 will alert you just like November 2022 did. As for why we no longer support 9.6 with Qlik Sense: It has reached end-of-life and is not being supported by Postgre any more.

Yes, please upgrade PostgreSQL to 12.5.

And on November 2022 working fine with 9.6: While we expect no issues running it while a customer prepares for an upgrade to 12.5, we cannot guarantee it in a production environment. Please see the release notes for supported PostgreSQL versions with any of our Sense releases.

jchoucq · ‎2023-02-17

Hi and thank you very much for this article !

If i understand correctly, after using QPI for upgrading postgreSQL from 9.6 to 12.5, at the end Qlik Sense Repository Database service is removed (replaced by postgresql-x64-12 service)
But if you run a brand new installation of Qlik Sense November 2022 for example, the service is still there, is it correct ?

Even after using QPI, there won't be any problem for future upgrade regarding the lack of Qlik Sense Repository Database service ?
Have a nice day.

Johann

Sebastian_Linser · ‎2023-02-17

@jchoucq

Yes that is correct, you would get the embedded Postgres in version 12.5 when you reinstall fresh.

But the second option is using QPI, before reinstalling fresh and user the option to install a fresh database, then you can install Qlik Sense in the same external instance. The advantage here is the possibility to quickly upgrade postrges from 12.5 to e.g. 12.11 if you would need that.

upgrades will work in both scenarios.

best regards

Sebastian

khartmann2 · ‎2023-02-22

Hello,
if Qlik is not installed under C:, then the upgrade of the postgres installation works if you copy the directory "...\Qlik\Sense\Repository\PostgreSQL\9.6" to C:
But the article says: "Qlik Sense Enterprise on Windows installations other than in C:\Program Files\Qlik\Sense are currently not supported."
When will this be supported? Because there are several customers whose Qlik installation is not under C:.

Sebastian_Linser · ‎2023-02-23

@khartmann2 this feature is on the roadmap but there is no ETA at the moment.

shaan007 · ‎2023-02-26

Hi There,

Im in the middle of the Feb 2023 upgrade process. We are currently running QS May 2022 Version and I am using the Qlik PostgresSQL installer to upgrade our 9.6 to 12.

My setup was fine and I logged in as admin and I am running the Qlik PostgresSQL installer setup. See my Screenshot 1.

PostgreSQL update setup.jpg

I follow the instruction in the documentation. At the end of the process, I am getting this 'Backup Fail' Error.

See my attached Screenshot 2.

PostgreSQL update Backup fail error .jpg

I could see a backup in the allocated location C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\backup\9.6

and Also I see the PostgreSQL v12 files in

I can't find the 'postgresql-x64-12' service

Now I am confused. Is my PostgreSQL server upgraded to Version 12?

The Log file install-postgresql.log says >>

[01:36:29] Removing the existing ldconfig setting - set during the previous installation.
[01:36:29] Running the post-installation/upgrade actions:
[01:36:29] Write the base directory to the ini file...
[01:36:29] Write the version number to the ini file...
Initialising the database cluster (this may take a few minutes)...
Executing C:\Windows\System32\cscript //NoLogo "C:\Program Files\PostgreSQL\12/installer/server/initcluster.vbs" "NT AUTHORITY\NetworkService" "postgres" "****" "C:\Users\BI Admin\AppData\Local\Temp/postgresql_installer_3c1317201f" "C:\Program Files\PostgreSQL\12" "C:\Program Files\PostgreSQL\12\data" 4432 "DEFAULT" 1
Script exit code: 1

Script output:
WScript.Shell Initialized...
Scripting.FileSystemObject initialized...

Called CreateDirectory(C:\Program Files\PostgreSQL\12\data)...
Called CreateDirectory(C:\Program Files\PostgreSQL\12)...
Called ClearAcl (C:\Program Files\PostgreSQL\12\data)...
Executing batch file 'radBC2A9.bat'...
Called Die(Failed to reset the ACL (C:\Program Files\PostgreSQL\12\data))...
Failed to reset the ACL (C:\Program Files\PostgreSQL\12\data)

Script stderr:
Program ended with an error exit code

Error running C:\Windows\System32\cscript //NoLogo "C:\Program Files\PostgreSQL\12/installer/server/initcluster.vbs" "NT AUTHORITY\NetworkService" "postgres" "****" "C:\Users\BI Admin\AppData\Local\Temp/postgresql_installer_3c1317201f" "C:\Program Files\PostgreSQL\12" "C:\Program Files\PostgreSQL\12\data" 4432 "DEFAULT" 1: Program ended with an error exit code
Problem running post-install step. Installation may not complete correctly
The database cluster initialisation failed.
Problem running post-install step. Installation may not complete correctly
The database cluster initialisation failed.
Setting variable whoami from C:\Windows\System32\whoami
Script exit code: 0

The Log file install-postgresql.log says >>

[01:36:29] Removing the existing ldconfig setting - set during the previous installation.
[01:36:29] Running the post-installation/upgrade actions:
[01:36:29] Write the base directory to the ini file...
[01:36:29] Write the version number to the ini file...
Initialising the database cluster (this may take a few minutes)...
Executing C:\Windows\System32\cscript //NoLogo "C:\Program Files\PostgreSQL\12/installer/server/initcluster.vbs" "NT AUTHORITY\NetworkService" "postgres" "****" "C:\Users\BI Admin\AppData\Local\Temp/postgresql_installer_3c1317201f" "C:\Program Files\PostgreSQL\12" "C:\Program Files\PostgreSQL\12\data" 4432 "DEFAULT" 1
Script exit code: 1

Script output:
WScript.Shell Initialized...
Scripting.FileSystemObject initialized...

Called CreateDirectory(C:\Program Files\PostgreSQL\12\data)...
Called CreateDirectory(C:\Program Files\PostgreSQL\12)...
Called ClearAcl (C:\Program Files\PostgreSQL\12\data)...
Executing batch file 'radBC2A9.bat'...
Called Die(Failed to reset the ACL (C:\Program Files\PostgreSQL\12\data))...
Failed to reset the ACL (C:\Program Files\PostgreSQL\12\data)

Script stderr:
Program ended with an error exit code

Error running C:\Windows\System32\cscript //NoLogo "C:\Program Files\PostgreSQL\12/installer/server/initcluster.vbs" "NT AUTHORITY\NetworkService" "postgres" "****" "C:\Users\BI Admin\AppData\Local\Temp/postgresql_installer_3c1317201f" "C:\Program Files\PostgreSQL\12" "C:\Program Files\PostgreSQL\12\data" 4432 "DEFAULT" 1: Program ended with an error exit code
Problem running post-install step. Installation may not complete correctly
The database cluster initialisation failed.
Problem running post-install step. Installation may not complete correctly
The database cluster initialisation failed.
Setting variable whoami from C:\Windows\System32\whoami
Script exit code: 0

Also At the end, it says...

Creating menu shortcuts...
Executing C:\Windows\System32\cscript //NoLogo "C:\Program Files\PostgreSQL\12\installer\server\createshortcuts_clt.vbs" "PostgreSQL 12" "C:\Program Files\PostgreSQL\12"
Script exit code: 0

Script output:
Start FixupFile(C:\Program Files\PostgreSQL\12\scripts\runpsql.bat)...
Opening file for reading...
Closing file (reading)...
Replacing placeholders...
Opening file for writing...
Closing file...
End FixupFile()...
createshortcuts_clt.vbs ran to completion

Script stderr:

[01:36:35] Write the server description to the ini file...
[01:36:35] Write the server branding to the ini file...
Creating Uninstaller
Creating uninstaller 25%
Creating uninstaller 50%
Creating uninstaller 75%
Creating uninstaller 100%
Installation completed
Log finished 02/27/2023 at 01:36:48
Exiting with code 1

Can anyone help here?

Sebastian_Linser · ‎2023-02-26

@shaan007 please open a case with the Qlik Support so we can help you through it. The VB scripts which fail in your case are the internal ones form the official postgresql installer. That said, could you test to install the postgresql 12.14 version from here https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

You will most likely see the same error code that will be a valid data point to find a fast solution.

best regards

Sebastian

shaan007 · ‎2023-02-26

@Sebastian_Linser Thank you for the reply.

Instead of trying "install PostgreSQL 12.14", Shall I try this ?>> "manually upgrade the bundled Qlik Sense PostgreSQL version to 12.5 version"

Will that still fail?

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