Postgresql – How to get HeidiSQL to connect to Postgres databases for the first time

linuxpostgresqltcpipwindows

With two different Linux servers I have installed Postgres on them using no standard configurations. They are in different networks off-site.

The Postgres service is running (as I checked by running ps -ef | grep postgres). I have installed namp on both Linux servers.

These commands show port 5432 is not blocked:

nmap -p 5432 x.x.x.x

I can SSH to either Linux server from my Windows machine. I have Heidi SQL on my Windows desktop. When I try to connect to either of these servers with HeidiSQL (using port 5432), I get "could not connect to server: Connection refused…is the server running on host "x.x.x.x" and accepting TCP/IP connections on port 5432." This has never worked before.

What should I do to get HeidiSQL to connect to a Postgres database on a Linux server?

Best Answer

First of all, make sure you can connect to your PostgreSQL database from your Linux machine itself (if you have two, you'll have everything on both machines).

You do that using psql:

 psql -h localhost -p 5432 postgres

This assumes psql is in your path; if not... finding where it resides depends on your specific Linux version, PostgreSQL version and installation methods; please, specify them.

If you cannot connect to the database from the local machine... make sure it is running, and that it is the process actually using the 5432 port.


Next, you need to check your PostgreSQL configuration file settings. The file to check is called postgresql.conf. Its location, again, depends on how you installed it. Check Where are my postgres *.conf files?.

In the configuration file, you need to check for the parameteres listen_addresses and port, which indicates PostgreSQL which TCP-IP addresses and ports it should connect to. You might have port 5432 blocked, and PostgreSQL listening to it, but only for your 127.0.0.1 (localhost) IP address, but not listening to the "outside world". The default setting (AFAIK) is to listen only to 'localhost'.

If your Linux box is connected to the outside world through port 192.168.1.2, and you want to use the standard 5432 port, you should have:

listen_addresses = 'localhost, 192.168.1.2'
port = 5432

If you change any of these parameters, you should stop and restart the PostgreSQL service. [There are alternatives that don't need to stop, but at this point, I think this is the easiest way.]


Once you have PostgreSQL listening to the outside world, you still need to allow connections from users. That is done through the pg_hba.conf file. You need to have some user(s) allowed to connect to your database(s) from some IP address (or some range, or all of them, ...) using a specific authentication method. The most usual settings would look like:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# local net
host    all             all             192.168.1.0/24          md5

This means: all users can connect to all databases using a TCP/IP host on an address 192.168.1.0 .. 192.168.1.255, authenticating using md5 (meaning a password).


If your Linux box uses SELinux/iptables, make sure they allow the connections. Check, for instance Linux: Iptables Allow PostgreSQL server incoming request, or Connect to Postgres remotely, open port 5432 for Postgres in iptables.


If you need to setup SSL for PostgreSQL connections... check Secure TCP/IP Connections with SSL. I would suggest that you use SSL only after you have managed to have insecure (non-SSL) connections first.


Check the firewall in your Windows Machine, and/or any external Firewall between your Windows and your Linux machines. If any of them exists and are active, make sure they allow for communications to port 5432. Your specifics depend a lot on your actual setup. Resource: How to open firewall ports in Windows 10


Make sure you can ping your Linux box from within Windows. If you have telnet on your windows machine, try telnet (ip-of-your-linux-box) 5432 and check also that you can connect (as soon as you type something, you're very likely to get disconnected, that's ok).


At this point, HeidiSQL should be able to connect to your remote Linux machine. (Now, repeat with the 2nd one).