Please be aware that this document is not supported and is meant only as a guide. Individual environments may require adjustments for things to work correctly. This will/may require altering and customizing code shipped with Qlik Sense Enterprise or outside of Qlik Sense. All customization is done at your own risk and is not covered by Qlik Support or Maintenance Agreements. Please backup any files prior to modification.
In Qlik Sense November 2019, we support for the first time officially Postgresql 11.5, if you use the internal Database shipped with Qliksense you do not have to act, but additional configuration steps are needed when doing an upgrade in an environment where PostgreSQL runs outside of Qlik Sense.
At the outset this document covers the following scenario:
- Windows OS
- Use of Linux, either in RDS / cloud or on-prem is not covered. There is a robust knowledge base on the web for PostgreSQL for Linux
- Generic PostgreSQL usage
- There will not be coverage of Stream replication, SSL, or any other advanced configuration options possible in PostgreSQL
- (Assumed), in order to point to PostgreSQL independent of Qlik Sense, you will need to be running Shared Persistence
Before beginning the upgrade
As with all upgrades, it is essential to have a backup that passes a sanity check. The key elements which are needed to restore an entire Qlik Sense site are as follows:
- The underlying Qlik Sense Repository Database (be it in PostgreSQL or the Qlik Sense Repository Database service)
- The apps
- The certificates
- The Repository Content used by Apps
Backing up the Qlik Sense Repository Database
- Stop Qlik Sense Services in the cluster
- On the server running PostgreSQL, prepare a .TAR file backup.
- Sample command: 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
- It is a good idea to rename this file so that it isn't over-written. a filename like QSR_backup_pre_nov_2019_upgrade.tar is clear enough
- Assumes that PostgreSQL is installed in the default path
- Sanity check this file to ensure that is has a non-zero filesize.
Backing up the Apps used by Qlik Sense
- Navigate to the Share path and copy the Apps sub-folder.
- This path is printed in the Service Cluster section of the QMC if this was recorded prior to stopping services
Backing up the certificates
Backing up the Repository Content used by Apps
- Similar to the section for apps, the path where these files live is in the StaticContent folder on the Share path
- Officially PostgreSQL should not be upgraded per se. In this walk-through, we will essentially backup a TAR from one version and restore to another.
- Download and install PostgreSQL 11.5 from PostgreSQL’s site (https://www.postgresql.org/download/).
- Remember to 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.5 and in use in 9.6, so deviation from this assumption will require additional configuration which will not be covered.
- After installation, ensure that both PostgreSQL 9.6 and 11.5 are shut down in the Windows services applet (Start > Run > services.msc)
- 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
- Once the 11.5 .conf files are configured to mirror the 9.6 versions, start the postgresql-x64-9.6 service
- If you have not already done so, install pgAdmin4, which is not included with PostgreSQL 11.5 (https://www.pgadmin.org/download/pgadmin-4-windows/)
- Launch pgAdmin4
- Create a connection to the database:
- Right click on Servers > Create > Server
- Provide a name for the connection on the General tab
- Provide the hostname (localhost), alter the port to 4432, and enter the password on the Connection tab
- Create the qliksenserepository user account
- Right click on Login/Group Roles > Create > Login/Group Role
- In the Name tab, enter the name for the account (qliksenserepository)
- In the Definitions tab, enter the Password for the account
- In the Privileges tab, the minimum permissions necessary is that the user can login, so slide the slide bar to allow this
- Save the new account with its settings
- Create the QSR database
- Right click on Databases > Create > Database
- In the General tab, name the database QSR and assign the owner to be qliksenserepository
- Save the new database
- Stop the postgresql-x64-9.6 service
Backing up the QSR database from 9.6
- Start the postgresql-x64-9.6 service in services.msc
- 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
- Stop the postgresql-x64-9.6 service
Restoring the QSR database to 11.5
- Start the postgresql-x64-11.5 service in services.msc
- Run the pg_restore command to restore the database.
- Sample: "C:\Program Files\PostgreSQL\11.5\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
- 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
- On the Central node for Qlik Sense, run the June 2017 installer as administrator
- There is a relatively exhaustive page on the step by step on help.qlik.com
- After upgrade, open the QMC to ensure that the apps migrated successfully
- Open the Hub to validate connectivity
- Run the installer on all RIM nodes
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.
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 the page below.https://help.qlik.com/en-US/sense-admin/November2019/Subsystems/DeployAdministerQSE/Content/Sense_DeployAdminister/QSEoW/Deploy_QSEoW/Installing-configuring-postgresql-on-Azure.htm
Make sure to only create thoose which did not exist prior to your DB move.