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
With a Shared Persistence Architecture in Qlik Sense, it is important to understand the options which PostgreSQL uses to allow connectivity. These principles are valid no matter whether you are using the Qlik Sense Repository Database service to host the QSR database or using a standalone PostgreSQL instance. The two key files which PostgreSQL uses to define who can connect, how they connect, where they can connect from, and how many connections are allowed are:
In a default Qlik Sense site, these two files will be located in C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\9.6 (for Qlik Sense June 2017 or higher) or C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\9.3 (for Qlik Sense 3.x Shared Persistence). If using a standalone PostgreSQL server, or a Database as a service (DBaaS) vendor like AWS or Azure this location can vary, so check with the administrator of the database for details on the data directory.
Note: Any changes to these files will require a restart of the Qlik Sense Repository Database or PostgreSQL.
Note: Any lines prefixed with a # are commented out and not active.
postgresql.conf
In the postgresql.conf, in a basic Qlik Sense configuration, there are two key configuration options which are important:
- listen_addresses
- This configuration defines which NICs PostgreSQL will listen on. For standalone PostgreSQL servers or multi-node Qlik Sense sites, the recommended setting is * (example below). This allows PostgreSQL to bind to any NICs presented to the server. Multiple comma-separated DNS entries may be added as well. (e.g. listen_addresses = 'localhost,QlikServer2.domain.local' ) More advanced configurations are possible here but will not be covered in this guide.
- max_connections
- This configuration defines how many connections PostgreSQL will allow. It is best to use a rule of thumb of about 110 connections per node +20. For example, for 4 nodes set to 460. See Installing Qlik Sense in a multi node for more details.
Where these lines are can vary but an expected single node Qlik Sense site would look like this:
listen_addresses = '*' max_connections = 100
pg_hba.conf
The pg_hba.conf file defines a variety of aspects:
- What IP addresses or ranges can connect to PostgreSQL and for what accounts
- What method of authentication those IP ranges / accounts can use
- For more details on configuration options for IP listeners, such as samenet, see https://www.postgresql.org/docs/9.6/auth-pg-hba-conf.html
# TYPE DATABASE USER ADDRESS METHOD # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::/0 md5 # Allow replication connections from localhost, by a user with the # replication privilege. #host replication postgres 127.0.0.1/32 md5 #host replication postgres ::1/128 md5 host all all 0.0.0.0/0 md5
In this example pg_hba.conf file there two active lines. The first line specifies that all users can connect to all databases from the local loopback IP using MD5 authentication (host all all 127.0.0.1/32 md5). The second line specifies that all users can connect to all databases from any valid IPv4 address using MD5 authenication (host all all 0.0.0.0/0 md5)
Please review our help for references to more sophisticated configurations where specific IP addresses or IP address ranges are covered.
To recap, when ensuring connectivity in a Qlik Sense Shared Persistence environment, especially a multi-node site, the core configurations which are needed are:
- PostgreSQL configured to listen to an external IP address (listen_address in postgresql.conf)
- Sufficient connections available for Qlik Sense (max_connections in postgresql.conf)
- Explicit allowing the IP address which is used to connect (pg_hba.conf)
