Qlik Community

Knowledge

Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING

Qlik Sense Enterprise on Windows: How To Upgrade Standalone PostgreSQL

cancel
Showing results for 
Search instead for 
Did you mean: 
Sebastian_Linser

Qlik Sense Enterprise on Windows: How To Upgrade Standalone PostgreSQL

This document is not supported and is meant only as a guide.  Individual environments may require adjustments for things to work correctly. Standalone PostgreSQL database server instances are not covered by the Qlik Support Maintenace Agreement.  

 

Before we start:

  • For information on what version of PostgreSQL is supported by your version of Qlik Sense Enterprise on Windows, please review the System Requirements for your release. See. System Requirements for Qlik Sense Enterprise.
  • This guide covers deployments where PostgreSQL has been installed separately and not through the Qlik Sense installer. For deployments where PostgreSQL is installed by Qlik Sense installer no manual action is required at the time of update unless described in Release Notes or Upgrading and updating Qlik Sense Enterprise on Windows

At the outset 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.

1. Before beginning the upgrade

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

Notes

Depending on the version you might have more Databases to migrate, just follow the steps above for the databases below, by just changing the DB name in the commands above.

  • QSMQ
  • Licenses
  • SenseServices
  • QLogs

All the databases listed above are needed for the November 2019 edition. QLogs is optional if you are not using the feature. The rest can be created via script found on Installing and configuring PostgreSQL on Azure.

Make sure to only create those which did not exist prior to your DB move.

2. Upgrading PostgreSQL

Practically PostgreSQL 9.6 database server is not upgraded to PostgreSQL 11.x. The newer version is installed in parallel and the database is migrated from version 9.6 to 11.x. In this walk-through, the migration is done by using SQL Dump to export and then import the Qlik Sense Repository Database.

2.1 Pre-work

  1. Download and install latest PostgreSQL 11.x release from PostgreSQL’s site (https://www.postgresql.org/download/).
    • Install PostgreSQL to port 4432
    • This guide will assume that you are using the same password for the postgres (and qliksenserepository) user accounts in 11.x and in use in 9.6, so deviation from this assumption will require additional configuration which will not be covered.
  2. After installation, ensure that both PostgreSQL 9.6 and 11.x are shut down in the Windows services applet (Start > Run > services.msc)
  3. Ensure that the pg_hba.conf and postgres.conf files are configured the same between the builds.
    • Default path for the 9.6 files C:\Program Files\PostgreSQL\9.6\data
  4. Once the 11.x .conf files are configured to mirror the 9.6 versions, start the postgresql-x64-11.8 service
  5. If you have not already done so, install pgAdmin4, which is not included with PostgreSQL 11.x (https://www.pgadmin.org/download/pgadmin-4-windows/)
  6. Launch pgAdmin4
  7. Create a connection to the database:
    1. Right-click on Servers > Create > Server
    2. Provide a name for the connection on the General tab
    3. Provide the hostname (localhost), alter the port to 4432, and enter the password on the Connection tab
    4. Save
  8. Create the qliksenserepository user account
    1. Right-click on Login/Group Roles > Create > Login/Group Role
    2. In the Name tab, enter the name for the account (qliksenserepository)
    3. In the Definitions tab, enter the Password for the account
    4. In the Privileges tab, the minimum permissions necessary is that the user can login, so slide the slide bar to allow this
    5. Save the new account with its settings
  9. Create the QSR database
    1. Right-click on Databases > Create > Database
    2. In the General tab, name the database QSR and assign the owner to be qliksenserepository
    3. Save the new database
  10. Stop the postgresql-x64-9.6 service

Backing up the QSR database from 9.6

  1. Start the postgresql-x64-9.6 service in services.msc
  2. Run a backup command (if not already done above).
    • Sample: cd / & mkdir Temp & "C:\Program Files\PostgreSQL\9.6\bin\pg_dump.exe" -h localhost -p 4432 -U postgres -b -F t -f "c:\Temp\QSR_backup_%date:~-4,4%%date:~-10,2%%date:~-7,2%.tar" QSR
  3. Stop the postgresql-x64-9.6 service

Restoring the QSR database to 11.x

  1. Start the postgresql-x64-11.x service in services.msc
  2. Run the pg_restore command to restore the database.
    • Sample: "C:\Program Files\PostgreSQL\11.8\bin\pg_restore.exe" -h localhost -p 4432 -U qliksenserepository -d QSR "c:\Temp\QSR_backup.tar"
    • Notice that we are specifying the user to be qliksenserepository and not postgres since we want qliksenserepository to own all the tables in the QSR database
    • You will create and restore for the following DB's 
    • QSMQ
    • Licenses
    • SenseServices
    • QLogs
  3. To create DB follow this link Creating a PostgreSQL database 
  4. At this point, you will want to either uninstall PostgreSQL or disable the service so that it does not start after a server reboot. Disabling is ideal for the short-term to validate the rest of the upgrade goes smoothly

 

Upgrading Qlik Sense

  1. On the Central node for Qlik Sense, run the installer as administrator
    • There is a relatively exhaustive page on the step by step on help.qlik.com 
  2. After upgrade, open the QMC to ensure that the apps migrated successfully
  3. Open the Hub to validate connectivity
  4. Run the installer on all RIM nodes

 

Related Content:

Qlik Sense May 2021 - Upgrade bundled PostgreSQL to 12.5 version 



Labels (1)
Comments
Sivapriya_d
Contributor III
Contributor III

Hi @Sebastian_Linser ,

This is helpful. I am checking on Pg_Upgrade, Instead of taking dumps and restoring it.  Is there any known impact for Qlik Sense if we use this method?

 

Thanks,
Siva

Sonja_Bauernfeind
Digital Support
Digital Support

Hello  @Sivapriya_d 

Using the method described above is the only verified method by Qlik Support. 

Sivapriya_d
Contributor III
Contributor III

Hi @Sonja_Bauernfeind ,

I tried migrating our postgre SQL DB Server from 9.6 to 11.12 using Pg_dump  and Pg_Restore method suggested above. Post migration when i checked the Individual DB size (QSR,License,etc) its bit smaller from the size in OLD version.

Have you faced this kind of scenario?

 

Thanks,

Siva

Sonja_Bauernfeind
Digital Support
Digital Support

Hello @Sivapriya_d !

This is caused by the backup not including temporary/cache information. 

 

-Sonja

Sivapriya_d
Contributor III
Contributor III

Thanks @Sonja_Bauernfeind . This is the first time we are doing Postgre SQL upgrade in our platform. I was anxious due to the small DB size and thinking of its impact on Qlik Sense. 

Is possible to share  your experience with the upgrade ? the things which we can expect like the above DB size.

This would help people who do new upgrades like us. 

Lastly Can you please share the roll back steps to be performed in both Postgre SQL and Qlik Sense end jus in case.

 

Thanks for all your inputs and guidance till now

 

Thansk,

Siva

 

 

Sonja_Bauernfeind
Digital Support
Digital Support

Hello @Sivapriya_d 

Next to the difference in sizes, we cannot share any additional information.

As for the rollback: Do you mean a rollback from the Postgre SQL upgrade or a Qlik Sense upgrade? 

In short, what you would need is a backup of your previous QSR on the 9.6 database (which you have taken already for the upgrade step) and restore it to your Qlik Sense System as described in: Backup and Restore Qlik Sense .

 

Also please note that the instructions in this article are for if you have a stand-alone Postgre SQL. Not the bundled one. If you are looking for instructions for the bundled one, see Qlik Sense May 2021 - Upgrade bundled PostgreSQL to 12.5 version .

 

Sivapriya_d
Contributor III
Contributor III

Thanks @Sonja_Bauernfeind . I am upgrading Standalone postgre SQL . 

 

Thanks,

Siva

pablodelcerro
Partner
Partner

Hi There,

We have a QS cluster with a standalone Postgresql and will like to get it back to the bundle version so that we dont have to worry about Postgresql updates.

Will a clean new install and a restore DB procedure work or we need to worry about anything else?

What about rim nodes? Need to do anything there?

Thanks!

Pablo

Sonja_Bauernfeind
Digital Support
Digital Support

Hey @pablodelcerro See our reply here for an answer 🙂

Version history
Last update:
‎2021-12-14 03:03 PM
Updated by: