PostgreSQL: Remotely connecting to Postgres instance using psql command

Question

I want to remotely connect to a Postgres instance. I know we can do this using the psql command passing the hostname

I tried the following:

psql -U postgres -p 5432 -h hostname

I modified the /etc/postgresql/9.3/main/pg_hba.conf file on the target machine to allow remote connections by default

I added the following line to the file

host all all source_ip/32 trust

I restarted the cluster using

pg_ctlcluster 9.2 mycluster stop
pg_ctlcluster 9.2 mycluster start

However, when I try to connect from the source_ip, I still get the error

Is the server running on host "" and accepting TCP/IP connections on port 5432?

What am I doing wrong here?

check you firewall settings, so port 5432 is open to accept connections — Dmitry Savinkov
Assuming your client machine has ssh you can also try localhost-like connection to remote postgres simply by setting ssh tunnel: ssh -L 5432:localhost:5432 your_server_ip and connecting the same way as you would connect to localhost db. This can help debugging. — jangorecki
By default postgresql only listens on localhost (and many distro's leave this default untouched) adjust the value of listen_addresses to '*' to make postgresql listen on all addresses your machine has. — Eelke

Jagadish S Jagadish S · Accepted Answer · 2020-07-22T10:43:24

I resolved this issue using below options ...

Whitelist your DB host from your network team to make sure you have access to remote host
Install postgreSQL version 4 or above
Run below command:

psql -h <REMOTE HOST> -p <REMOTE PORT> -U <DB_USER> <DB_NAME>