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 

[fabdulazeez]

Thank @Sebastian_Linser .

We used QPI version  0.8.2 for the PostgreSQL version 12 installation. Later Qlik support had confirmed that the version had defect. During a Qlik version upgrade, they provided steps to move the PostgreSQL  folder which involved updating the registry. I suspect this might be cause for the error I encountered. Please let me know if I should raise a ticket for this , as it’s not just the service — the entire PostgreSQL 12 folder still remains on the system.

[Huberto_Computop]

Hi @Sonja_Bauernfeind,

I followed the documentation written by @Sebastian_Linser and it worked just fine. Great post!

I have one minor question. After uninstalling PostgreSQL 12 (step 11), the uninstaller displays the following "The data directory (C:\Program Files\PostgreSQL\12\data) has not been removed" message.

What is the right procedure here, delete the whole "12" folder and leave just the "14" folder in the "C:\Program Files\PostgreSQL" folder? I think the 12 folder is useless now, but I want to be sure.

As the documentation was so well written, could you please also update the post with this detail?

Thank you and regards,

Huberto Pereira Haidemann

[Sebastian_Linser]

Hello @Huberto_Computop 

the uninstall never deletes the data folder automatically. After all is working you can delete the 12 folder yourself.

If however something would not be working that is the fast role back option in installing back 12 and use the old data folder again.

best regards

Sebastian

[Huberto_Computop]

Thank you, @Sebastian_Linser.

[Huberto_Computop]

Hi @Sebastian_Linser,

I identified a different behavior when accessing the Qlik Sense Repository Database via pgAdmin 4.

I entered the password for the pgAdmin user and it worked, but when I enter the password for the postgres user, pgAdmin4 displays the message below.

Connect to Server

Please enter the password for the user 'postgres' to connect the server - "PostgreSQL 14"

Password: "password entered"

Error Message

could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

What should I do to access the database like before? I am running the PostgreSQL 14.12 version.

The password for the postgres user is right because the backup was successfully executed.

Before the database upgrade, I didn't have any issues accessing the database via pgAdmin4 (PostgreSQL 12.5).

Is this maybe an issue caused when the database config files were copied from the 12.5 version to the new 14.12 version folder?

Let me know if a support case is needed for further investigation.

Thank you and regards,

Huberto Pereira Haidemann

[Sebastian_Linser]

5432 -> you might want to test with 4432 as it was in the embedded DB.

[Huberto_Computop]

OK, I learned something new about the pgAdmin 4.

I changed the Port in the database connection parameters to 4432 and it worked. Thanks for the information.

But now, why do I still see PostgreSQL 12 in the pgAdmin 4 if I uninstalled version 12 and also deleted the 12\data folder? Should it be removed from there as well?

Regards,

Huberto Pereira Haidemann

[Sebastian_Linser]

Hello,

that was a connection which got generated when you installed Pgadmin initially. Since it found a local installation of the database. You can simply remove that connection in the connection list. 

best regards

Sebastian

[Pvaidyanathan]

Qlik Sense Enterprise on Windows 

Hello @Sebastian_Linser @Sonja_Bauernfeind ,

This is a little off topic, but I otherwise cannot find any other information for this: the encryption method specified for the pg_hba.conf file in the documentation is md5. Is there any way to use scram-sha-256 with QLIK? If I change the method to scram-sha-256, Qlik Sense stops working as it is supposedly unable to connect to the Postgres DB.  

Thanks.

Padmanabhan Vaidyanathan. 

[Sebastian_Linser]

Hello @Pvaidyanathan 

you can follow the steps under section Changing from MD5 to SCRAM encryption

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

best regards

Sebastian