Postgresql Connection Timed out

Question

when I am connecting to psql on remote server

>psql -h {hostname} psql: could not connect to server: Connection timed out (0x0000274C/10060). Is the server running on host "{hostname}" (194.58.98.133) and accepting TCP/IP connections on port 5432? 

On server:

netstat -nlp | grep 5432 tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 29609/postmaster tcp 0 0 :::5432 :::* LISTEN 29609/postmaster unix 2 [ ACC ] STREAM LISTENING 2107633273 29609/postmaster /tmp/.s.PGSQL.5432 

there is not iptables on server. Pinging the server is successful.

What I am doing wrong?

Answer 1

I was getting this kind of error when my pg_hba.conf or AWS security groups have not been configured properly. There are plenty of docs about how to resolve this. For example, you can check the below, quoted from this link

Error: psql: could not connect to server: Connection refused Is the server running on host "192.168.0.1" and accepting TCP/IP connections on port 5432?

Common Cause: The postmaster or PostgreSQL's server daemon process is not running or configured incorrectly.

When you receive this error most of the time it is due to not having PostgreSQL configured to allow TCP/IP connections or at least not connections from your particular workstation. If you have verified that postmaster is indeed running on the host you are trying to connect to then here is a list of common causes to this problem:

• postgresql.conf not setup to allow TCP/IP connections. You'll want to look at the listen_address configuration parameter.
• postgresql.conf is not setup to allow connections on a non-standard port number. To determine this look at the port configuration option.
• authentication rules in PostgreSQL's access configuration file (pg_hba.conf) are not setup to allow either your uses or IP address to connect to that database. See the official documentation for more information on setting up your pg_hba.conf properly.
• ensure that there are no firewalls, such as iptables that are keeping your local system from even establishing a connection to the remote host

Answer 2

You should have a look at postgresql.conf. To allow connections from everywhere you have to set the listen_adresses to *:

listen_addresses = '*' 

the pg_hba.conf should have an entry like that, to allow connection from your network:

# IPv4 local connections: host all all 127.0.0.1/32 md5 host all all 192.168.190.0/24 md5 

Answer 3

In my case as I had an AWS RDS managed instance the problem was a security group rule.

Adding an inbound rule to allow traffic from my PC allowed the connection. For further details please reference: https://stackoverflow.com/a/70337387/4725074

Answer 4

Did you check your firewall settings? Try to allow 5432 port connection. E.g. Ubuntu ufw: sudo ufw allow 5432