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?

Richard T · Answer

Note that "ident" in pg_hba.conf requires a "ident server" to be running on the client.

Draeger · Answer

Step Wise below

Opening the Port - Make sure the PSQL Port is open to all remote connections or connections from a specific set of IPs as per your requirement. PSQL, in general, runs at port 5432, and it is configurable, so expose relevant Port accordingly.
Update Remote Server PSQL Configuration - Set listen_addresses = '*' in postgresql.conf file, path in general is /etc/postgresql/psql_version/main/postgresql.conf
Connect remotely - psql -U <db_username> -h <IP_address> - in case psql is running on a port other than 5432 on the remote server, specify port by adding -p <port_number>

A little plus below - In case the IP has been mapped to a domain name, you can connect by replacing <IP_address> with <host_name>. To do this, add a new connection rule in pg_hba.conf file

Note - All above explained can cause security issues - best practice always is to either keep your psql port closed, or only allow a list of IPs to connect through the port.

devops-admin · Answer

psql -h -p -d -U -W -W option will prompt for password. For example: psql -h 192.168.1.50 -p 5432 -d testdb -U testuser -W

Jagadish S · Answer

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>

Answered by Jagadish S on November 22, 2021

Hardik Kamdar · Answer

I figured it out.

Had to set listen_addresses='*' in postgresql.conf to allow for incoming connections from any ip / all ip

PostgreSQL: Remotely connecting to Postgres instance using psql command

5 Answers

Add your own answers!

Ask a Question