PostgreSQL: Remotely connecting to Postgres instance using psql command
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?
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.
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
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
I figured it out.
Had to set listen_addresses='*' in postgresql.conf to allow for incoming connections from any ip / all ip
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
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.
Note that "ident" in pg_hba.conf requires a "ident server" to be running on the client.
Follow WeChat
Success story sharing
psql -h <surus.db.elephantsql.com> -U <user>. The host can change in the free version, then I had to add the password, which is separated with colon from the user in the URL String.