psql: could not connect to server… Is the server running on host … and accepting TCP/IP connections on port 5432?

Question

I know this is a popular question. I have tried everything suggested in similar threads. I am trying to connect remotely to a postgresql database on an azure windows server 2012 machine. I have tried the following...

postgresql.conf

listen_addresses = '*'

pg_hba.conf

# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

Firewall

New inbound rule (firewall) protocols and ports > protocol type > TCP

Local port > 5432 Remote port > All

Grab IP of remote machine

I copied from azure dashboard and also as indicated on top of remote desktop connection

CMD from local machine

Then from local machine tried the following.

psql -U postgres -h 13.xx.xx.xx

Error message.

psql: could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "13.xx.xx.xx" and accepting TCP/IP connections on port 5432?

I am not sure what else should be done.

After you changed the listen_addresses and pg_hba.conf settings, did you restart Postgres? The pg_hba.conf changes only require a reload, but the listen_addresses change requires a restart. You can verify the change from Postgres by running select current_setting('listen_addresses'); You can check your pg_hba settings via select * from pg_catalog.pg_hba_file_rules(); (if Azure is using PG10 that is) — bma
Yes, you need restart psql. It it still does not work, it seems a firewall issue. See my answer. — Shui shengbao

Shui shengbao Shui shengbao · Accepted Answer · 2018-01-15T01:48:28

After you modify configuration file, you need restart it. You could use netstat -ant|findstr 5432 to check it. It should listen like below:

C:\Users\shui>netstat -ant|findstr 5432
  TCP    0.0.0.0:5432           0.0.0.0:0              LISTENING       InHost
  TCP    [::]:5432              [::]:0                 LISTENING       InHost

It seems a firewall issue. You need open port 5432 on Azure NSG.

I test in my lab, it works for me.

psql: could not connect to server… Is the server running on host … and accepting TCP/IP connections on port 5432?

1 Answers