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?

Answer 1

I resolved this issue using below options:

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

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

Answer 2

psql -h <IP_Address> -p <port_no> -d <database_name> -U <DB_username> -W

-W option will prompt for password. For example:

psql -h 192.168.1.50 -p 5432 -d testdb -U testuser -W