Qlik sense connection to postgresql failed

[almarengo]

Hello everyone,

I'm trying to connect Qlik sense to my local postgres database but I'm getting this error:

Error message:Please check the values for Username‚ Password‚ Host and other properties. Description: Communication link failure

ERROR [08S01] [Qlik][PostgreSQL] (10) Error occurred while trying to connect: [SQLState 08S01] could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432? could not connect to server: Cannot assign requested address Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432?

Username, password are checked and they are correct. It's pretty frustrating because I should already be done with my analysis by now. 

Thank you!

[Vikram_Jayakumar]

Hi @almarengo 

Have you tried connecting it with default port 4432? Or is 5432 the one you used during installation 

[almarengo]

Thanks @Vikram_Jayakumar  I tried both ports but is still not connecting. But yes I used port 5432 for installation.

I also tried to connect Qlik sense with my local MySQL database and get the same error. Again I checked all the values and are correct. It's a general connection error.

Is it possible that is because I'm only on trial model?

[Vikram_Jayakumar]

Pretty sure you might have tried this https://www.bigbinary.com/blog/configure-postgresql-to-allow-remote-connection just in case.

Also what is the case here. Do you have a working Qlik sense for which you are trying to connect an new database or you are trying to install Qlik sense with a Postgres Installed on a dedicated server 

[almarengo]

I'm just starting with Qlik sense and wanted to connect with a Postgres local on my machine to make a dashboard. But I also tried to connect to my local MySQL database and same error. Do I have to install Qlik sense? I'm using the web app to connect to my local.

I checked my ports and this is what I get

[Vikram_Jayakumar · Accepted solution]

Okay, Now I think I understood what you are trying to do

• You have a an trail version of SaaS edition of Qlik Sense
• From the create new connection you are testing PostgresSQL and MySQL enterprise edition 

If I got this correct since you are able to see these connections in data source don't think your license should be a issue ( I am not sure need to confirm) 

Supported MySQL Enterprise Edition versions

• 8.0 server
• 5.7 server
• 5.6 server

Supported PostgreSQL versions

• 9.4 - 9.6, 10, 11

So my next recommendation would be to edit the pga_hba.conf and postgresql.conf configuration files of your DB to allow communication between your PostgreSQL database and external (Qlik) in this case

Note: Make a backup copy of the postgresql.conf and pg_hba.conf files before you start, so that you have the option to revert back to the original settings.

To edit the postgresql.conf file:

1. Navigate to the postgresql.conf file in C:\Program Files\PostgreSQL\<version>\data of your PostgreSQL installation.
2. Open the file in a text editor as an administrator.
3. Make the following configuration changes:

   Setting	Value	Description
   listen_addresses	*	Enter the IP address(es) to listen on. If entering multiple listen addresses, use a comma separated list. Enter * to listen for connections from all IP addresses.
   max_connections	600	Defines the maximum number of client connections allowed. To calculate this value, multiply by 100 the number of nodes in your deployment.

4. Save your changes.

pg_hba.conf

The pg_hba.conf file handles client authentication. Each record specifies a connection type, such as a client IP address range, database name, user name, and the authentication method used.

To edit the pg_hba.conf file:

1. Navigate to the pg_hba.conf file in C:\Program Files\PostgreSQL\<version>\data of your PostgreSQL installation.
2. Open the file in a text editor as an administrator.

3. Locate the following line:

   host all all 127.0.0.1/32 md5

   This line determines which servers can access the repository database server. The default address setting, 127.0.0.1/32, only allows local host to access the database.

4. Replace 127.0.0.1/32 with a sub net specification that covers all the IP addresses of the nodes in your site.
   When specifying these settings, add one row for each node, using /32 as a suffix for each address, or add a sub net that covers all addresses using, for example, /24 as a suffix:

   • IPv4 (32-bit addresses):
     • To specify a single address: 192.168.1.0/24, or 172.20.143.89/32

     • For a small network: 172.20.143.0/24, or 10.6.0.0/16 for a larger one.

     • To allow access from all IPv4 addresses: 0.0.0.0/0

   • IPv6 (128-bit numeric addresses):
     • For a single host: ::1/128 (in this case the IPv6 loopback address)
     • For a small network: fe80::7a31:c1ff:0000:0000/96
     • To allow access from all IPv6 addresses: ::/0
5. Save your changes.

For more detailed information about setting these parameters, see the PostgreSQL documentation.

If these things are sorted out at the DB end hopefully connection should work

Good Luck

[almarengo · Accepted solution]

CORRECT

[Vikram_Jayakumar]

Were you able to establish a connection

[almarengo]

I was able to! Thank you so much for your help @Vikram_Jayakumar