Remote Connections in PostgreSQL

Share via:

Remote Connections in PostgreSQL:

Remote connections will allow you to connect to a PostgreSQL server from a different machine or network. In this article, we will explore the steps to establish remote connections in PostgreSQL and provide best practices for securing these connections.
This article discusses two types of remote connections in PostgreSQL.
Accessing a database from one Linux machine to another Linux machine:
Here we have two Linux machines server1 and server2, we are going to access the database in server1 through server2 remotely.
Follow the steps below for establishing a connection:
1. Start the server1:

2. Configure PostgreSQL server:
Edit the “postgresql.conf” file with the vi editor which will be present in the cluster data directory.

3. Modify pg_hba_conf:
Navigate through the data directory and open the ‘pg_hba.conf’ file.

This file controls the authentication and access rules for Postgresql connections.
The entry should specify the IP address or subnet range of the client machine.

Here we alter the IPv4 local connections.

4. Configure Firewall:
To allow connection from outside we should alter the firewall configuration.
Firstly, switch to the root user

Now use the following to stop the firewall.

Check the status by using following command.

5. Add details of hosts in both servers.
Run the following command and add the details.

We should do the same in both servers.
Run the command by switch to the Postgres root user.
6. Establishing connection from client to server:
Make sure that cluster is running in server1 before establishing connection. Later run the following command to access database in server1

In the above command,
Database – postgres
Username – postgres
Hostname –       # which contains database.
Port – 5433    # psql cluster port number
Now you will successfully access the databases i.e create a database or modify the database.
Run the following command to verify any cluster is running in server 2.

Here no cluster is running in server2 though we can access the database in another cluster


Author    : Prudhvi Teja

LinkedIn  :

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTExperts is always active on social media platforms.

Facebook  :
LinkedIn    :
Twitter       :
YouTube   :

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Add Comment