TransWikia.com

PostgreSQL: Remotely connecting to Postgres instance using psql command

Stack Overflow Asked by Hardik Kamdar on November 22, 2021

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?

5 Answers

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

Answered by Richard T on November 22, 2021

Step Wise below

  1. 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.
  2. Update Remote Server PSQL Configuration - Set listen_addresses = '*' in postgresql.conf file, path in general is /etc/postgresql/psql_version/main/postgresql.conf
  3. 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.

Answered by Draeger on November 22, 2021

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

Answered by devops-admin on November 22, 2021

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>

Answered by Jagadish S on November 22, 2021

I figured it out.

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

Answered by Hardik Kamdar on November 22, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP