How to Remotely Connect to Another Instance Using PSQL

1. Initial Setup of Remote Connections to PostgreSQL Instance
2. Modify the QUERY Statement for Remote Access in PostgreSQL

This article introduces how to set up a remote connection to another PostgreSQL instance. We’ll take you to step by step through the journey and emphasize points needing clear explanations.

Initial Setup of Remote Connections to PostgreSQL Instance

If you try to run the following command at first from COMPUTER 1, you will get an error.

postgres=# psql -U postgres -h [ip addr. of Computer 2]

Output:

psql: error: connection to server at "[ip addr. of Computer 2]", port 5432 failed: FATAL:  no pg_hba.conf entry for host "[ip addr. of Computer 1]", user "postgres", database "postgres", no encryption

Let’s first try to understand what’s happening here. -h [hostname] specifies the machine on which the database server is running; thus, we use it to connect to the remote machine database of PostgreSQL.

And the reason it gives an error is that the pg_hba.conf does not have any authorization for our client computer; COMPUTER 1. This is the client authorization configuration file, and it needs us to configure the records to allow remote access from COMPUTER 1.

Check if the PING TO [ip_addr of Computer 2] Is Working From Command Prompt

In CMD, type the following command.

ping [ip_addr of comp. 2]

If you see something like:

This means that the route from the client to the server is open and vice versa. We’ll assign the IP 192.168.10.8 to the SERVER and 192.168.10.4 to the CLIENT.

COMPUTER 1 now refers to the CLIENT and COMPUTER 2 to the SERVER. For instance, if the PING returns a REQUEST TIMED OUT, ensure that the ports are open on both computers and the internet connection is fine.

Go to SERVER COMPUTER (2) and Modify the Pg_hba.conf File

You’ll be able to find this file in the data folder of your PostgreSQL installation. Or for default installations, the path is:

C:\Program Files\PostgreSQL\14\bin (or any version number in case of 14)

Now edit this pg_hba.conf file and under the IPv4 Local Connections heading.

We will add the IP ADDRESS of our CLIENT with the configuration statement.

host all all 192.168.10.4/24 md5

The 24 above is the subnet of the CLIENT IP specified and the METHOD defined as md5. This will allow authentication through an md5 encrypted password.

Now that our first step is complete, we can move on to the second most important step of enabling remote access.

Go to postgresql.conf File and Update Settings

The PostgreSQL server is specified to listen to the localhost address or * in the default initial settings. However, we now have to change the configurations provided for remote access.

Open the postgresql.conf file in the Data folder and click on Edit. Once the file is opened, under the CONNECTIONS AND AUTHENTICATION, look for listen_addresses = ''.

If you find it, change it to the following.

listen_addresses = `192.168.10.4`

Or keep it at *. The * means that the database will now accept all incoming connections.

If you only fill in the client address here, no other clients will be able to connect until you specify them too. Hence, it’s better to use a *.

Once again, 192.168.10.4 is our CLIENT PC, and 192.168.10.8 is our SERVER PC. Change the addresses in the statements given to you as you desire.

You may now close the file and restart the PostgreSQL server for the changes.

Test the Connection

Back into the psql command prompt, try this statement again.

postgres-# psql -U postgres -h 192.168.10.8

It will then ask you to enter the PASSWORD for Postgres. Remember that this password is for the database on the SERVER side.

Once logged in, you can now access each database object and perform queries as you desire.

We’ll look at some basic problems you can encounter while setting up remote access.

Modify the QUERY Statement for Remote Access in PostgreSQL

A specific command may not work in some instances; you can also use the following statement.

psql -h 192.168.10.8 -p 5432 -U postgres

We also define the PORT as 5432, the default PORT set for our PostgreSQL installations.

Another statement that we can use to query could be:

psql -h 192.168.10.8 -p 5432 -U postgres -W

The -W here asks for the password from the USER. However, this method may not run in all cases and may return a CONNECTION error.

Remote access in PostgreSQL can allow multiple users on different machines to access a common database and run queries. However, this connection may often be interrupted by either firewall issues or port closures, or even if some configurations are left unchanged.

Often, invalid ports and access protocols block user access, and many users already have pre-existing modifications inside their database files.

Our alterations are made to run on systems with a clean file system, where no other modifications are present to the files in the back-end. Hence, there may be exceptional cases where even our solutions don’t tend to work for you.

Ensure your versions are up to date, Windows isn’t blocking any important access for remote connections, or your router enables cross-LAN communication.

We hope you learned how to set up the remote access for PostgreSQL and will be equipped with the basics of doing so and performing your modifications later on.