This article describes how Qlik Application Automation can be used to perform and review app evaluations and take action accordingly.

Environment

• Qlik Application Automation
• Qlik Cloud Services

Example

In this example, we'll create an automation that evaluates an app before publishing it to a managed space.

Before you continue, create a new automation.

1. Search for the Qlik Cloud Services connector in the Block Library and add the Do Evaluation block to the automation. Configure an app ID by using the do lookup function. The Do Evaluation block will analyze the app and return metrics regarding the app's performance.
   
   

2. Add a Variable block to create a new variable of type list with the name vCacheOverrun. We will use this variable to store all objects from the evaluated app that take too long to cache.
   
   

3. The Do Evaluation block will analyze the app and return metrics regarding the app's performance. One metric we want to look at is the "ObjSlowCached" metric which contains slow objects.
   
   
4. We want to filter all objects in the ObjSlowCached list and add each item that takes too long to load to the list variable vCacheOverrun. To do this, add a Filter List block to the automation and use another variable block inside the loop from the Filter List block to add these items to the list variable. Configure the Filter List block to return all objects with a responseTimeSeconds greater than 0.3.
   
   
5. In the Variable block, add the object's id and responseTimeSeconds as a formatted string to the list.
   
   

6. Add a Condition block that verifies whether the vCacheOverrun variable is empty. If it's empty, it means there are no (too) slow cached objects and the app can be published. Add a Publish App To Managed Space block to the Yes outcome of the Condition block and add a Send Mail block from the Outlook connector to notify your team about the published app.

   

7. If the vCacheOverrun variable is not empty, the app should not be published. Add another Variable block vStringCache of type string to the No outcome of the Condition block. Set the value of this variable to the output from the vCacheOverrun variable and add the implode formula to transform the list variable into a string.

   
8. Add a Send Mail block from the Outlook connector to notify your team that the app wasn't published. Make sure to include the vStringCache variable in the body of the email.

Bonus

• Feel free to replace the Send Mail blocks in this automation with blocks from other messaging connectors like Microsoft Teams or Slack.

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.

Question

What Files Should I whitelist in my Antivirus Software for Talend Applications like Talend Administration Center and Job Server?

For Talend Administration Center, the folders that include jar files are recommended to be whitelisted.
For JobServer, the cache folder <JobServerInstallationFolder>/jobserver/agent/TalendJobServerFiles are recommended to be whitelisted.
The above are files that may have an impact on the performance. 

Windows Antivirus Software might cause file locks that can impact the cleanup of JobServer caches and temporary files created by jobs.
So it can be advisable to

• Make sure that jobs use a distinct temp folder that can also be excluded from scanning.
• The same apply for Talend Administration Center with generatedJobs folder and Logs folder.

 Also, as mentioned in the reference: Optimizing Talend Studio performance, the application installation folder is also recommended to whitelist for all applications, not just Talend Studio.

Environment

Talend Administration Center 

After installing TAC 8.0.1 and attempting to access the DB Config page in TAC, an error occurs:

500 The call failed on the server; see server log for details

The Oracle Database is utilized as TAC's database, and the compatible ODBC jar has been installed. The server log displays the primary error message as follows:

Error: caused by java.sql.SQLException: Non-supported character set (add orai18n.jar to your classpath).

Cause

The dependent orai18n.jar file is missing from the lib folder.

Resolution

Download the orai18n.jar file and ensure it is placed in both of the following folders:

<apache-tomcat>/lib

<apache-tomcat>/webapps/org.talend.administrator/WEB-INF/lib

Once completed, please restart the TAC server.

Environment

• Talend Administration Center 8.0.1

The Qlik Sense Engine allows for a Hard Max Limit to be set on memory consumption. 

To be able to enforce "Hard max limit" the engine requires access to a specific endpoint in the Windows operating system API.

If the operating system blocks calls to this endpoint, then an attempt to use the configuration "Hard max limit" will fail, and the engine will instead operate according to the "Soft max limit" configuration.

Refer to the documentation provided by Microsoft fur forther information: "SetProcessWorkingSetSizeEx"

How to review your memory settings in Qlik Sense or configure the Hard Max Limit:

1. Open the Qlik Sense Management Console in a supported browser
2. Navigate to Engines in the Management Console and select your engine
   
   
   
   
3. Expand the Advanced settings from the menu to the right
   
   
   
   
4. Review your memory settings and verify that Max Max Limit is selected
   
   

See Editing an engine - Qlik Sense for administrators for details.

To note:

Even with the hard limit set, it may still be possible for the host operating system to report memory spikes above the Max memory usage (%).

This is down to how the Qlik Sense Engine memory limit will be defined based on the total memory available.

Example:

• A server has 10 GB of memory in total, and the Max memory usage (%) is set to 90%.
• This will allow the process engine.exe to use 9 GB of memory. 
• Another application or process may at that point already be consuming 5 GB memory, and this will cause an overload of the system if the engine is set to use 9 GB. 
• The engine.exe will not be able to respect other services

The memory working setting limit is not a hard limit to set on the engine. This is a setting which determines how much we allocate and how far we are allowed to go before we start alarming on the working set beyond parameters. 

Internal Investigation IDs:

QLIK-96872, HLP-17517

If you have a web application server that is deployed on Talend Runtime, and need to change the timeout values, it is located in the file below: <RuntimeInstallationFolder>/etc/jetty.xml 

The following is a part of the code found in the jetty.xml file.
Please change the value for "http.timeout" in order to change the timeout value.

The default setting for the timeout value is 30000 milliseconds which equals to 30 seconds.

<Call name="addConnector">
　　<Arg>
　　　　<New class="org.eclipse.jetty.server.ServerConnector">
　　　　　　　<Set name="idleTimeout">
　　　　　　　　　<Property name="http.timeout" default="30000"/>
　　　　　　　</Set>
　　　　</New>
　　</Arg>
</Call>

Environment

Talend ESB 

Manually restart Qlik Sense services in right order is important because the Qlik Sense Repository Service is dependent on the Qlik Sense Repository Database (QRD) and the rest of the services are dependent on the Qlik Sense Repository Service.

Please review the Qlik Sense Online help for the relevant Sense version to find the Service Dependencies for your specific version. 

In a Multi-node environment, start the central node first and stop the rim nodes first.

Content:

• Correct Start Order:
• Central node
• Rim node
• Correct Stop Order:
• Central node
• Rim node
• Sample PS Script below for stopping and starting the Qlik Sense services:
• Optional monitoring of start process:

Correct Start Order:

Central node

1. Qlik Sense Repository Database (may also be called postgresql-x64-12)
2. (if installed) Qlik Sense Logging Service 
3. Qlik Sense Service Dispatcher
4. Qlik Sense Repository Service 
   Note: Delay for up to five seconds before starting the Qlik Sense Repository Service to allow the Dispatcher time to start its microservices. 
5. Qlik Sense Proxy Service, Qlik Sense Engine Service, Qlik Sense Scheduler Service, Qlik Sense Printing Service

Rim node

We are unable to provide a comprehensive start or stop order as rim nodes can have a number of different combinations of services installed. Please skip any services you do not have installed.

1. (if installed) Qlik Sense Logging Service 
2. Qlik Sense Service Dispatcher
3. Qlik Sense Repository Service 
   Note: Delay for up to five seconds before starting the Qlik Sense Repository Service to allow the Dispatcher time to start its microservices. 
4. Qlik Sense Proxy Service, Qlik Sense Engine Service, Qlik Sense Scheduler Service, Qlik Sense Printing Service

Correct Stop Order:

Central node

1. Qlik Sense Repository Database (QRD)
   This will first terminate all services with a dependency to the QRD (see Service dependencies on the Qlik Sense Online Help)
2. (if installed and not stopped already by step 1.) Qlik Sense Logging Service (If feature was enabled during installation)
3. Qlik Sense Service Dispatcher (QSD)

Rim node

We are unable to provide a comprehensive start or stop order as rim nodes can have a number of different combinations of services installed. Please skip any services you do not have installed.

1. Qlik Sense Proxy Service, Qlik Sense Engine Service, Qlik Sense Scheduler Service, Qlik Sense Printing Service
2. (if installed) Qlik Sense Logging Service 
3. Qlik Sense Repository Service
4. Qlik Sense Service Dispatcher (QSD)

Sample PS Script below for stopping and starting the Qlik Sense services:

Get-Service "Qlik*" | Where-Object {($_.Name -like "QlikSense*" -and $_.Name -notlike "QlikSenseRepositoryDatabase") -or ($_.Name -eq "QlikLoggingService")} | Stop-Service -Force 
Start-Service -Name "Qlik Sense Service Dispatcher" 
Start-Service -Name "QlikSenseServiceDispatcher" 
Start-Service "Qlik*"

Optional monitoring of start process:

Open the following location: C:\ProgramData\Qlik\Sense\Log\Repository\Trace
Monitor the folder after starting the Qlik Sense Repository Service (QRS). Files with appended time/date will disappear.
 

This article provides the necessary steps to back up an existing Qlik Enterprise Manager PostgreSQL database before restoring it to a new major version of PostgreSQL. We will use pgAdmin for the process.

For Qlik Enterprise Mangaer's System Requirements, including PostgreSQL compatibility, see Additional software requirements.

If you are upgrading within a major PostgreSQL release (example: 14.5 to 14.8), no manual backup and restore is necessary. Simply download the PostgreSQL installer and upgrade your current instance. 

If you are upgrading to a new major PostgreSQL release (example: 12.x to 14.x):

1. Backup the existing PostgreSQL database of your Qlk Enterprise Manager instance using pgAdmin:

   1. Right-click on the existing PostgreSQL database (in our example: postgres)

   2. Select Backup
      
      

   3. Fill in the required information:
      
      File Name: Your backup file name
      Format: TAR
      Role:  Current role name
      
      
   4. Click Backup
      
      
      
      
      
2. Download the new major version of PostgreSQL you want to upgrade to. For example, you may be moving from 12.x to 14.x.
3. Install the new PostgreSQL instance on the Qlik Enterprise Manager host
   
   for PostgreSQL-specific installation instructions, see PostgreSQL's manuals. 
   
   
4. In the new PostgreSQL instance, create a New User identical to the one used in the previously backed-up database 
   
   
5. In the new PostgreSQL instance, create a New Database with an identical name to the previously backed-up database
   
   
6. Restore your backup to the new database using pgAdmin:
   
   
   1. Right-click on the newly created database (in our example: postgres)
      
      
   2. Select Restore 
      
      
      
      
   3. Fill in the required information:
      
      Format: Custom or Tar
      File Name: Your backup file name
      Role: Previous role name
      
      
   4. Click Restore
      
      
      
      
7. Point Qlik Enterprise Manager to the new PostgreSQL database by following the steps in Unable to start collector in analytics module of Qlik Enterprise Manager when changing the database
   
   
8. Verify you can see the old analytics information

Environment

• Qlik Enterprise Manager 

There are environments where the cryptographic protocols available to the Windows Operating System need to be restricted for security or compliance reasons. This article will outline where various TLS versions are supported. This article will not have full coverage of the impact of TLS changes to other software installed on the Qlik Sense server. For examples of potential impacts:

• SQL connection not working with TLS 1.2
• OLEDB Connection not working with TLS 1.2

Resolution

• Full compatibility with TLS 1.2 in all current and supported versions of Qlik Sense Enterprise on Windows
• TLS 1.3 is not yet fully compatible [Internal Reference ID: SHEND-1269]

Implementation

To enable strong TLS implementation make sure to have all your servers updated to a version of both the operating system and the Qlik software, which explicitly details they support the required version of TLS.

If you have a clustered environment with multiple nodes spread across different machines, please make sure to enable the same subset of protocols on all Sense machines, otherwise the services will not be able to successfully communicate.

Using a third-party toolset

Third-party tools such as IIS Crypto can be used to enable and disable SSL or TLS. Consult your Windows administrator or network security team for what tools are usually used in your organization. 

Manual

The correct protocols and ciphers can then be applied using the PowerShell (PS) scripts and making changes to the Windows Registry. Consult Microsoft or your Windows administrator for details. 

Related Content

IIS Crypto is an example 3rd party tool that can be used to achieve this. IIS Crypto is not supported by Qlik, but by its respective vendor, NARTAC Software. To obtain IIS Crypto, visit https://www.nartac.com/Products/IISCrypto. 

Accessing and managing your Data Spaces varies depending on your active subscription.

Without an active Qlik Cloud Data Integration subscription:

Data Spaces cannot be managed from the Management Console or the Analytics Services hub. To manage your Data Spaces, use the Data Integration section.

Note: A professional license is required.

1. Click the main Qlik Cloud menu button next to your profile icon
2. Open Data Integration
   
   Note: If you cannot see Data Integration, add /qdi to your tenant URL. Example: https://TENANTNAME.REGION.qlikcloud.com/qdi
   
   
   

To add a new space:

1. Select the Getting started or Home tab
2. Click Add new
3. Click Create space
   
   
   

To edit a space:

1. While still in Data Integration select Data projects
2. Open the Spaces drop down
3. Select your space
   
   
   
   
4. Click Manage space
   
   

With an active Qlik Cloud Data Integration subscription:

You can manage your Data Spaces directly from the Management Console and through Qlik Data Integration.

See Managing data spaces for details on how to manage spaces using the Management Console. For steps on how to use Qlik Data Integrations, see the instructions above.

Environment

Qlik Cloud 

Related Content:

Users previously assigned a Basic User entitlement have unexpectedly been promoted to Full User entitlement. 

This is working as expected and will happen when a Basic User is granted any additional permissions. Source: Types of user entitlements.

An exception is the Collaboration Platform role, see Qlik Cloud Analytics: Assigning the Collaboration Platform User role to a Basic User does not promote it to full.

Resolution

To avoid users being promoted to Full Users, remove any assignment, both in single spaces and at a global level.

Spaces

Verify the users are not assigned any roles other than than Has restricted view. 

Be careful when using the Anyone group, since assigning any other role to this group will promote all users to Full.

Global

Turn off Auto assign for all Security roles in the Administration Center.

1. Open the Administration Center
2. Open Users
3. Switch to the Permissions tab
4. Switch Auto assign to Off on all Security roles:
   
   

Specific users

If a user was manually assigned a role, the role has to be removed again manually.

1. Open the Administration Center
2. Open Users
3. Switch All Users tab if not already selected
4. Locate the user and click the ... menu (a), then choose Edit roles (b) and remove all previously assigned roles
   
   

Environment

• Qlik Cloud Analytics

By default, assigning a role to a Basic user in Qlik Cloud Analytics will promote it to Full. See Basic User entitlement.

That doesn't happen with the Collaboration Platform User role. Assigning the Collaboration Platform User role will not automatically upgrade the entitlement of a basic user to Full User.

Resolution

This is working as designed.

Qlik has added an exception for the Collaboration Platform User role to allow Basic users access to the Microsoft Teams Chatbot. 

Environment

• Qlik Cloud Analytics

The Qlik Cloud certificate is issued to the us region even for tenants not based in the us region, such as eu or ap. This may be perceived as a mismatch or as if the certificate was issued to the wrong region. 

This is working as expected. 

While the certificate's display name refers to the *us region, the certificate is extended to all Qlik Cloud regions as Subject Alternative Names (SAN):

This is a standard best practice to bind different DNS names together, and there is therefore no need to change the certificate.

See DigiCert's knowledge base for more details about certificate extensions and SANs.

Environment

• Qlik Cloud Analytics

Qlik Data Gateway erroring out with "Invalid metadata response (DirectAccess-2201) " message. The error could be due to a different issues. 

Resolution

• Verify Privileges on the space where the Gateway was created and the Space where user is attempting to create the connection, our documentation describes the details  here
• If privileges are correct, try to restart Qlik Data Gateway services on the Server where it resides.
• Verify if the issue is specific to a connection, if one user was able to successfully create a connection, attempt to create a new connection with the user that is getting the error.
• If nobody is able to create a connection for an specific Data Source, attempt to configure a DSN ODBC connection from the Data Gateway Server to verify it succeeds. 

Cause

The cause of the issue is usually configuration, if the suggestions above didn't fix the issue it will be necessary to report the issue for further analysis. 

Related Content

Qlik Data Gateway: Direct Access Data Gateway failing with Internal Error 

Environment

• Qlik Cloud 
• Qlik Data Gateway 

A Qlik Replicate task is suspended due to the following error:

Line 142049: 00024400: 2024-12-10T12:15:45:607996 [TARGET_APPLY ]E: RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column [1022502] (ar_odbc_stmt.c:5090)

Resolution

There are different approaches to resolving the issue.

Add a Null Check coalesce transformation with the same default value.

Example:

The column SalePrice decimal(18, 2) default 0 not null was added to a table with existing data.

To fix this, add the transformation below to the column:

coalesce($SalePrice,'0')

Alternatively, rewrite every row of the table in the source database.

Cause 

This is a known issue with SQL servers when a not null column with a default value is added to a table with existing data with null values.

In SQL Server 2012 and later, the metadata is added, but the default value is not added until the column is modified.

SQL Server will store the default value in metadata and use that to retrieve a value for old rows until the modification occurs.

New rows however will get the default value written out to the row immediately.

Environment

• Qlik Replicate
• SQL Server as the source

The worksheet URL is used on a customized dashboard. After migration from Talend Data Catalog

 8.0 to Talend Data Catalog 8.1, worksheet ID is changed and the old worksheet URL became invalid.

Resolution

As worksheet ID will be changed with product upgrade/migration and it is by design. Please update the worksheet URL link.

Cause

The Objects may change their internal Object ID that you are using as part of the URL, especially when there is a major version upgrade/migration and a worksheet migration is required.

The Object ID is not guaranteed to be stable. While migration from Talend Data Catalog 8.0 to Talend Data Catalog 8.1, the location where Talend Data Catalog stores worksheets to the repository objects table is moved. As a result, their IDs are incompatible with the previous ones. This requires all bookmarks to worksheets must be updated.

Environment

 Talend Data Integration 

Environment

• Qlik Replicate November 2021 (Version 2021.11 and later)

What is Replicate log file encryption?

Starting from Qlik Replicate version "November 2021", when the logging level is set to Verbose, sections in a Replicate log file that might contain customer data will be encrypted while the rest of the log will remain in clear text, this is implemented by the enable_data_logging configuration parameter set to true by default in order to prevent customer data snippets from appearing in verbose logs.

The sample line of the task log file looks like this:

2021-11-05T15:45:11:390848 [AT_GLOBAL ]I: Logging of database data is disabled (at_logger.c:2702)

However, in some support cases, R&D may require the ability to decrypt the log in order to analyze the problem. In such cases, Qlik Support will ask you to provide the task or environment-level encryption key and corresponding task log files:

• Replicate task diagnostics package file, and
• log.key file for decrypting a task's log file, location is <REPLICATE_INSTALL_DIR>\data\tasks\<task-name>
• log.key file for decrypting non-task log files (e.g. server logs), location is <REPLICATE_INSTALL_DIR>\data

Below is the detailed steps how to enable the database data logging and decrypt a task log file with corresponding log.key files.

Steps

1. Stop the Replicate Server service.
2. Open <REPLICATE_INSTALL_DIR>\bin\repctl.cfg and set the enable_data_logging parameter to true. (add the line if it does not exist). This is set to true by default in version 2021.11 and later, even if you do not see this line in your configuration file.
   {
   "port": 3552,
   "plugins_load_list": "repui",
   ... ...
   "enable_data_logging": true
   }
   
3. Save the repctl.cfg file and start the Replicate Server service.
4. Now the task log files contain encrypted database data, sample lines like:
   
   

   2021-11-05T15:59:07:969146 [AT_GLOBAL ]I: Log file encryption base "755opF/9oGw7YOx+2ma0dA==" (at_logger.c:2695)
   2021-11-05T15:59:07:969146 [INFRASTRUCTURE ]I: The log level for 'TARGET_APPLY' has been changed from 'INFO' to 'VERBOSE'. (at_logger.c:2927)
   2021-11-05T15:59:07:969146 [INFRASTRUCTURE ]I: The log level for 'SOURCE_CAPTURE' has been changed from 'INFO' to 'VERBOSE'. (at_logger.c:2927)

   ... ...

   2021-11-05T16:02:21:346746 [SOURCE_CAPTURE ]V: Event of captured table 'SCOTT.KIT' (data object id '93719', object id '93719') (oradcdc_parse.c:303)
   2021-11-05T16:02:21:346746 [SOURCE_CAPTURE ]V: xid [000008fb00100007] (oradcdc_parse.c:166)
   2021-11-05T16:02:21:346746 [SOURCE_CAPTURE ]V: BI col 2 [5]
   ~uAAAABvF/duclauaOYlVdTbsCpbAiSZGsq3DIp8XyImAZjY984KVgc62Lir20Mnw1VU1V/54j/vXa4BXpyQ7rjcEPR1MtjoYnpPSeu4QSwfiUx3l}~ (oradcdc_parse.c:340)

   The latest line is the encrypted database data (BeforeImage of an update).
   
   
5. Run "Qlik Replicate Command Line" prompt "as administrator", and change to directory <REPLICATE_INSTALL_DIR>\bin
6. Run the following command to decrypt task logs:
   
   repctl dumplog <log-file-path> <log.key-path> [> <path-to-output-file>]
   
   A sample command:
   
   repctl dumplog "C:\Program Files\Attunity\Replicate\data\logs\reptask_Ora-to-SQL-2.log" log_key="C:\Program Files\Attunity\Replicate\data\tasks\Ora-to-SQL-2\log.key" > "C:\Program Files\Attunity\Replicate\data\logs\reptask_Ora-to-SQL-2-dump.log"
   
   Now we see the decrypted data in the task log files. The latest line is the BeforeImage of an update in clear text (in this sample it is text string "xxxxx"):
   
   2021-11-05T16:02:21:346746 [SOURCE_CAPTURE ]V: Event of captured table 'SCOTT.KIT' (data object id '93719', object id '93719') (oradcdc_parse.c:303)
   2021-11-05T16:02:21:346746 [SOURCE_CAPTURE ]V: xid [000008fb00100007] (oradcdc_parse.c:166)
   2021-11-05T16:02:21:346746 [SOURCE_CAPTURE ]V: BI col 2 [5]
   25a058b66d8: 7878787878 | xxxxx
   (oradcdc_parse.c:340)

Related Instructions

The encryption key file (log.key file) can be used to decrypt any log that was encrypted with it, not just the log for which Qlik Support requested the encryption key file. If this is a concern, you can generate a log.key file that will only be valid for the specific log requested by Qlik Support.

To do this:

1. Delete the log.key file.
2. Restart the task or Replicate Server service (depending on the log type required) to generate a new log.key file.
3. Send the requested log file and the log.key file to Qlik Support.
4. Delete the log.key file.
5. Repeat steps 2-4 if you need to provide additional encrypted logs.

If you do not want verbose logs encrypted, open <REPLICATE_INSTALL_DIR>\bin\repctl.cfg and set the enable_data_logging parameter to false then restart the Replicate server service. Add the line if you do not see it in the file.

Products: Qlik Replicate 

Affected versions:

• Qlik Sense Enterprise on Windows all versions, including November 2024

CVE-2024-7348 is a Time-of-Check Time-of-Use (TOCTOU) race condition vulnerability in pg_dump, a utility used for backing up PostgreSQL databases. This vulnerability allows an attacker to replace a relation type (such as a table or sequence) with a view or foreign table right when pg_dump is running. Because pg_dump often runs with superuser privileges, this attack could execute arbitrary SQL code, leading to unauthorized actions or data corruption. Source: https://www.postgresql.org/support/security/CVE-2024-7348/

How does this vulnerability impact Qlik Sense Enterprise on Windows?

Qlik can confirm that pg_dump is not actively used in the Qlik Sense Enterprise on Windows code.

How to mitigate the vulnerability 

Both steps are required to fully mitigate the issue.

• Upgrade PostgreSQL. This requires a standalone instance of PostgreSQL. See Upgrading and unbundling the Qlik Sense Repository Database using the Qlik PostgreSQL Installer on how to unbundle PostgreSQL if necessary.

  Always verify compatibility between your Qlik Sense version and PostgreSQL before planning an upgrade.

• Delete the pg_dump.exe located in the default Qlik Sense Enterprise on Windows Postgresql install folder: C:\Program Files\Qlik\Sense\Repository\Postgresql\14\
  
  The updated pg_dump.exe in, for example, C:\program files\postgresql\14 does not need to be removed. 

Does the Qlik PostgreSQL Installer (QPI) use the pgdump.exe?

QPI does not utilise pgdump.exe.

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".
       
       
18. Finalize the process by updating the references to the PostgreSQL binaries paths in the SetupDatabase.ps1 and Configure-Service.ps1 files. For detailed steps, see Cannot change the qliksenserepository password for microservices of the service dispatcher: The system cannot find the file specified.

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