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
• 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
• Related Content

Video Walkthrough

Video chapters:

• 01:02 - Intro to PostgreSQL Repository
• 02:51 – Prerequisites
• 03:24 - What is the QPI tool?
• 05:09 - Using the QPI tool
• 09:27 - Removing the old Database Service
• 11:27 - Upgrading a stand-alone to the latest release
• 13:39 - How to roll-back to the previous version
• 14:46 - Troubleshooting upgrading a patched version
• 18:25 - Troubleshooting upgrade security error
• 21:15 - Additional config file settings

 

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

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, default: 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\.

7. Set the database data folder, default: 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\.

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

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

16. 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.
       
       
       
       
    
    
17. 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

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.

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 PostgreSQL Repository Troubleshooting
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 

[mountaindude]

@menal 

Good question, unfortunately without a clear and definitive answer.

I've done numerous (many dozens) upgrades, in most cases using the QPI.
The earlier QPI versions were a bit of gamble as they didn't cover all corner cases and variants.
The latest couple of versions have been stable enough, in my experience.

I've for sure run into problems using QPI, but they have mostly been related to the fact that Windows Server can be installed in so many ways, with respect to permissions, disks, networks, firewalls etc etc.

But even so I would recommend using the QPI as main option.
I usually do things something like this:

1. Make sure to do proper backups of the entire Sense server first (preferably a full snapshot, including OS and all disks).
2. Do a full backup of Sense itself. This includes dumping the repository db to disk, exporting data connections, custom properties, security rules etc. I documented my workflow in a couple of blog posts a while back:
   
   1. https://ptarmiganlabs.com/a-no-compromise-qlik-sense-backup-solution-part-1/
   2. https://ptarmiganlabs.com/a-no-compromise-qlik-sense-backup-solution-part-2/
3. Follow the QPI instructions carefully.
4. Sort out any permissions problems etc that may be present after the migration is done. In particular, quite often the Postgres network config/permissions must be tweaked a bit after moving from 12.x > 14.x. Not always though - not sure exactly what causes this. Easily solved if you know how Postgres works though.

The other option (re-installing Sense) is more risky and requires (a lot) more work, IMHO.

If the QPI option fails completely you may end up having to re-install from scratch, using the repo db export as input to the new install. That works... but is a lot more demanding compared to first option, IMHO.

[menal]

@mountaindude 

Thanks for your reply.

I also think that if the upgrade fails with the QPI tool it is still possible to recover the Sense server with the full backup and then do the upgrade via the reinstallation.

[fabdulazeez]

At the client site, I'm initiating a fresh installation of February 2024. What would be the recommended approach: installing a fresh Postgres using the QPI and then proceeding with the installation, or simply proceeding with the bundled version 14.8 along with Qlik Sense?

In our inhouse we have it unbundled as we had done multiple upgrades. Now we have February 2024 with unbundled postgres 12.5 . When I try to upgrade postgres I can find installation file for only 14.11.

Should I try to find 14.8 to make it same as client site, if yes where can I get the installation file?

[Sonja_Bauernfeind]

Hello @fabdulazeez 

Whether you want to use an unbundled or bundled instance of PostgreSQL is up to you in this instance. However, choosing a stand-alone (unbundled) instance puts you in control and allows you to upgrade PostgreSQL.

From the article:

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.

You will need to obtain the installation files from PostgreSQL or a similarly trusted source. Qlik cannot provide them to you.

All the best,
Sonja 

[jaishree_Qlik]

Its not able to detect Postgre SQL 12.5 installed on Drive D. Instead of Upgrade its asking for setting up New Postgre Database ... What could be approach in this case ?

[Sonja_Bauernfeind]

Hello @jaishree_Qlik 

QPI can detect custom install paths of Qlik Sense since its 1.3.0 release. It is now on 1.4.0. What version are you using?

Please note the limitations as well, in case one of them applies:

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

All the best,
Sonja 

[mbespartochnyy]

Step 17 in Upgrading an existing Qlik Sense Repository Database section mentions something called service configuration scripts. The exact sentence is:

Does anyone know what are service configuration scripts?

[mbespartochnyy]

Also, what exactly is the definition of "bundled" PostgreSQL database?

Or, in other words, how is bundled PostgreSQL different from unbundled PostgreSQL database other than the fact that unbundled version can more easily be upgraded?

[Sebastian_Linser]

Hello @mbespartochnyy 

The service configuration scripts are scripts used to configure the microservices belonging to the Qlik Sense Service Dispatcher service.

• C:\Program Files\Qlik\Sense\NotifierService\install\Configure-Service.ps1
• C:\Program Files\Qlik\Sense\AppDistributionService\Configure-Service.ps1
• C:\Program Files\Qlik\Sense\HybridDeploymentService\Configure-Service.ps1
• C:\Program Files\Qlik\Sense\MobilityRegistrarService\install\Configure-Service.ps1
• C:\Program Files\Qlik\Sense\PrecedentsService\install\Configure-Service.ps1
• C:\Program Files\Qlik\Sense\NLAppSearch\install\Configure-Service.ps1
• C:\Program Files\Qlik\Sense\NLBroker\Configure-Service.ps1
• C:\Program Files\Qlik\Sense\DataPrepService\Configure-Service.ps1
• C:\Program Files\Qlik\Sense\Licenses\Configure-Service.ps1

One database setup script

• C:\ProgramData\Qlik\Sense\QSMQ\scripts\SetupDatabase.ps1

A bundled database is per definition the embedded Database you choose to install with Qlik Sense Enterprise on Windows. The datafolder of that one is always C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\version. 

As version you could have 9.3,9.6,12.5 or 14.8 depending on which version of Qlik Sense you had installed initially. 

Your service name would be Qlik Sense Repository Database Service.

 An unbundled database one is a standalone database. You can get when you either install your database up front with the standalone PostgreSQL installer from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads or use a database in the cloud. The second option is the Qlik Postgres Installer to migrate the database as written in this article to migrate (unbundle) the internal database into an external one.

best regards

Sebastian

[mbespartochnyy]

Thanks Sebastian!

I did a bit of testing on my server and it looks like after step 16 is completed, the PostgreSQL folder is removed from this directory:

C:\Program Files\Qlik\Sense\Repository

Following step 17, brings the PostgreSQL folder back to that directory.

To confirm, if I paraphrase the warning note in step 17, it would say something like:

"Now that you've done removing the Qlik Sense Repository Database server, you have two things you can do - upgrade Qlik Sense to a newer version or stay on whatever version you are now.

If you decide to stay on your current version, then go ahead and follow step 17 to bring back PostgreSQL folder and its contents to C:\Program Files\Qlik\Sense\Repository directory as there are Qlik Sense service scripts that depend on PostgreSQL binaries being in that directory. Those services can fail if step 17 isn't followed.

However, if next steps for you is to proceed to upgrading Qlik Sense to a newer version, you may skip step 17 as the PostgreSQL folder will be recreated during the upgrade process."

Does that sound accurate?