Qlik Sense Enterprise on Windows: How To Upgrade Standalone PostgreSQL

[Sebastian_Linser]

This document is a general guide and is provided as is. Modifications to the process may be necessary depending on your individual database setup.

If you have installed a standalone PostgreSQL database, or if you have used the Qlik PostgreSQL Installer (QPI) to upgrade and decouple your previously bundled database, then you can upgrade PostgreSQL at any time. This means you control maintenance and can immediately react to potential PostgreSQL security concerns by upgrading to a later service release or a later major version.

Content

• Requirements:
• Preparation
• Upgrading to a later service release
• Upgrading to a new major PostgreSQL release

 

Requirements:

• Only upgrade to a supported version of PostgreSQL. See the Qlik Sense Enterprise on Windows System Requirements for details. 
• This guide covers deployments where PostgreSQL has been installed separately or previously unbundled using the Qlik PostgreSQL installer. 

This document covers the following scenario:

• Windows Server OS. The use of Linux, in managed cloud deployments or on-prem, is not covered. 
• Generic PostgreSQL deployment. Stream replication, SSL, or any other advanced configuration are not covered.

 

Preparation

Run a complete backup of Qlik Sense Enterprise on Windows site as described in Backup and restore Qlik Sense Enterprise on Windows.

• Qlik Sense Repository Database (from PostgreSQL database server)
• Qlik Sense share folder
• Qlik Sense CA-signed certificates

Upgrading to a later service release

These steps apply if you are upgrading within a major PostgreSQL release (example: 14.5 to 14.8). 

1. Download the PostgreSQL installer from them PostgreSQL site
2. Stop the Qlik Sense Enterprise on Windows services. Leave your standalone PostgreSQL database running.
3. Run the PostgreSQL installer and follow the instructions.
4. Start the Qlik Sense Enterprise on Windows services.

No further steps are required.

 

Upgrading to a new major PostgreSQL release

If you are moving to a higher major version, an in-place upgrade will not be possible. Instead, we will install the the new version in parallel, then then migrate the old database and eventually uninstall the old version. Our example is written using PostgreSQL 12 to 14. 

1. Download the PostgreSQL installer from them PostgreSQL site
   
   
2. Run the installer
   
   During install:
   
   
   • Install the database on the standard 5432 port. 
     
     This allows us to have 12 running on the Qlik Sense default 4432 port and the 14 instance on 5432.
     
     
   • Set the same password as the one currently in use with the version 12 PostgreSQL instance. 
     
     
3. Open a Windows File explorer and create a backup folder, such as C:\backup 
   
   
4. Backup your version 12 database 
   
   This step is crucial. You may have already backed up your site, but in this step we will create the specific backup we will use to migrate your database, using a modified command.
   
   To do so:
   
   
   1. Stop all Qlik Sense Enterprise on Windows services on all nodes. Leave postgresql-x64-12 service (your current repository) running
      
      
   2. Stop postgresql-x64-14 (your newly installed PostgreSQL instance)
      
      
   3. Open a Windows Command Line (CMD) as administrator
      
      
   4. Run:
      SET PGPASSWORD=postgrespassword (replace this with the actual password)
      
      
   5. Run:
      cd “C:\Program Files\PostgreSQL\12\bin”
      
      
   6. Run
      pg_dumpall.exe -h localhost -U postgres -p 4432 -f c:\backup\backup_db.sql
      
      
   7. Follow the onscreen instructions and wait until the backup is finished
      
      Note: If you are prompted to input the password (even if it was previously entered correctly), you will need to modify pg_hba.conf. 
      
      Location: C:\Program Files\PostgreSQL\12\data\pg_hba.conf
      
      Change md5 to trust and restart the postgresql-x64-12 service. See Changing the Database Superuser Password without Qlik Sense Installed for details.
      
      Once the backup has completed, revert back to md5.
      
      
5. Migrate from 12 to 14
   
   We are now moving our Qlik Sense repository from the earlier release of PostgreSQL into the new one.
   
   
   1. Start the postgresql-x64-14 service
      
      
   2. Stop postgresql-x64-12
      
      
   3. Open a Windows Command Line (CMD) as administrator
      
      
   4. Run:
      cd “C:\Program Files\PostgreSQL\14\bin”
      
      
   5. Run
      psql -h localhost -U postgres -p 5432 -f c:\backup\backup_db.sql
      
      
   6. Follow the onscreen instructions.
      
      Note: If you receive the error password authentication failed for user "postgres", (even if it is correct), you will need to modify pg_hba.conf. 
      
      Location: C:\Program Files\PostgreSQL\14\data\pg_hba.conf
      
      Change scram-sha-256 to md5 and restart the postgresql-x64-14 service  and redo step e). See Changing the Database Superuser Password without Qlik Sense Installed for details.
      
      
6. With the import finished, copy the following two files from your old 12 data folder into your new 14 data folder:
   
   
   • pg_hba.conf 
   • postgresql.conf 
     
     
7. Open postgresql.conf and verify that the port is set to 4432 (the standard Qlik Sense port)
   
   
8. (If not done already) stop the postgres-x64-12 service
   
   
9. Restart the postgres-x64-14 service
   
   
10. Start all Qlik Sense services
    
    
11. Once you have confirmed all functionality and tested, uninstall PostgreSQL 12. This can be done directly from the Windows Control Panel and Programs and Features

Related Content:

Upgrading and unbundling the Qlik Sense Repository Database using the Qlik PostgreSQL Installer 
How to manually upgrade the bundled Qlik Sense PostgreSQL version to 12.5 version 
Changing the Database Superuser Password without Qlik Sense Installed 

[thkarner]

Hi @Sebastian_Linser, 

I´ve QS Nov 2022 with an unbundled PostgreSQL DB on 12.5.

Now I want to upgrade to QS May 2024. The recommendation is to upgrade the PostgreSQL DB to 14.8 beforehand according to your procedure above.

In the first step you write: "Download the PostgreSQL installer from them PostgreSQL site".

The site mentioned should be this one: PostgreSQL: Windows installers

Here I can click on "Download the installer" and will be redirected to this page:

EDB: Open-Source, Enterprise Postgres Database Management

Here I can find the version 14.15 but not 14.8.

I´ve found some unoffiical sites to download 14.8, but can I trust them?

Could you please let me know the link where I can download the official, trusted version of 14.8 which is supported by QS May 2024?

Thanks,

Thomas

[Huberto_Computop]

Hi @thkarner,

I also asked myself the same question. I looked for the PostgreSQL version that comes with the installation package (only installed for fresh installations), but I didn't find it as well in the link you mentioned. They only keep the latest version of each PostgreSQL version.

I upgraded our environment using the same version (e.g., 14) but with a greater patch (e.g., 15).

However, it is important to check the Qlik Sense system requirements of the new version to keep your PostgreSQL database installation compatible with Qlik. You can find this information in the link below (check the Qlik Sense version):

https://help.qlik.com/en-US/sense-admin/May2024/Subsystems/DeployAdministerQSE/Content/Sense_DeployA...

Regards,

Huberto

[thkarner]

Thank you @Huberto_Computop . I´ll install latest 14.x version, which is also supported according your link.

Regards,

Thomas

[Huberto_Computop]

Great, @thkarner. I am happy I could help you.

Regards,

Huberto

[Sebastian_Linser]

@thkarner and in addition to Huberto's answer you can now also use 15 and 16 with May 2024.

https://help.qlik.com/en-US/sense-admin/May2024/Subsystems/DeployAdministerQSE/Content/Sense_DeployA...

[aathi33]

Hi,
I have upgraded postgresql to 14. I'm planning to update the qlik to nov 2024.

In which order do i need to stop the services. 
I would be so happy for the timely response.

Thanks

[mab_koz]

Hello community,

In a case where I want to upgrade Qlik from February 2023 to November 2024, knowing that I already have a Postgres 12 service running, should I do the postgres upgrade before the Qlik upgrade ? 

Because I know that when I will install the Qlik upgrade, it will algo install a bin folder for postgres 14 under the folder repository under the main Qlik folder. In this case, how to tell Qlik to point to the newly upgraded standalone database and not to the bundled one installed during the Qlik upgrade process ? 

Thank you!

[Sebastian_Linser]

Hello @mab_koz you can do that after the upgrade to November 2024. 12.5 is still inside the installation as a package and will let you upgrade from February 2023 with a small warning message.

For the second question https://community.qlik.com/t5/Official-Support-Articles/Upgrading-and-unbundling-the-Qlik-Sense-Repo... point 16/17/18 in the upgrade section.

You will always have the embedded folder inside Program Files\Qlik\Repository\Postgresql\... because this one is a dependency for many other configuration scripts.

best regards

Sebastian

[mab_koz]

Hi @Sebastian_Linser ,

Thank you for your answer. At the end, I contacted the Qlik support since it was urgent, and they suggested to upgrade the postgres database before upgrading Qlik. This is what I did following this article and then upgrading Qlik to November 2024, and it worked ! 

Just for my understanding, could you please tell me more about how that folder under Repo\Postgres is a dependency for many other configuration scripts ? Is there any documentation detailing this subject ? Thank you in advance!

Best regards!