Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report

PostgreSQL: postgresql.conf and pg_hba.conf explained

100% helpful (1/1)
cancel
Showing results for 
Search instead for 
Did you mean: 
Andre_Sostizzo
Digital Support
Digital Support

PostgreSQL: postgresql.conf and pg_hba.conf explained

Last Update:

Jul 25, 2023 6:37:22 AM

Updated By:

Sonja_Bauernfeind

Created date:

Jun 4, 2018 2:03:04 PM

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 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:


For bundled PostgreSQL instances, the files are located in: 

C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\x.x

If you have unbundled PostgreSQL using the Qlik PostgreSQL installer (QPI), the default location is:

C:\Program Files\PostgreSQL\xx\data

For any other standalone PostgreSQL instances, consulting with your database admin to locate 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

PostgresSQL_listen_addresses.gif

 

pg_hba.conf

The pg_hba.conf file defines a variety of aspects:

# 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)
Labels (1)
Comments
pasgalbarra
Partner - Creator
Partner - Creator

Hi @Sonja_Bauernfeind ,

We rollback to Feb 2022, and this is the current configuration since May 2022.

Qlik Sense is installed in the E: drive, the files are in C?

pasgalbarra_0-1695727811409.png

 

Regarding the error I had, I think was caused by the format of the service account (lower case), I will give a second try next weekend

 

 

Sonja_Bauernfeind
Digital Support
Digital Support

Hello @pasgalbarra 

For bundled PostgreSQL instances (which you have), the files are by default located in: 

C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\x.x

Unbundled or otherwise custom installs can have a different location, as you can choose the data directory.

Gabriel_Araya
Partner - Contributor III
Partner - Contributor III

Hi Qlikkers,

My customer has a QSEoW multinode y checking the Log Monitor app, I found many errors like "Unexpected error in ExistsByID", checking this post: 

https://community.qlik.com/t5/Official-Support-Articles/Some-nodes-shown-as-offline-in-a-Qlik-Sense-...

and this post about the Postgresql.conf and PG_HBA.Conf files we did the changes:

Postgresql.conf

listen_addresses = '*'

Max_connections = 790

The customer has 1 central node and 6 RIM nodes, according to the rule 7 nodes * 110 + 20

The PGA_HBA.CONF file is:

host all all fe80:: .................. /128 md5

host all all 10.171......../32 md5

host all all 0.0.0.0/0 md5

How we can avoid the error if we change the Postgre config file? Are there any other component that can produce this error?

Kind regards,

Gabriel

 

Sonja_Bauernfeind
Digital Support
Digital Support

Hello @Gabriel_Araya 

I recommend posting your requirement in the Qlik Sense Deployment and Management forum, where our active product experts and your knowledgeable Qlik peers are better able to assist you.

All the best, 
Sonja 

Version history
Last update:
‎2023-07-25 06:37 AM
Updated by: