How to connect to PostgreSQL Database

This article is a simple step-by-step guide on how to connect to PostgreSQL database server from the client machine.

 

PostgreSQL connection to server

When we click Add New Server in PgAdmin4, we get the below error even though all the information is correct.

 

 

 

This error can be due to multiple causes:

Step 1:

Firewall in the PostgreSQL Linux server may be blocking the connection. By default, PostgreSQL listens on a TCP port 5432.  Linux uses firewall utility called iptables. Execute the below commands to give access to the client.

Allow PostgreSQL.

Or to control the access only from the local subnet, we can use the below command.

Make sure to check all the connections are allowed as required.

 

 

Step 2:

Make sure PostgreSQL service listening correct server IP address and port. (in our case server IP is 192.168.56.11)

Next make changes in PostgreSQL, so that server can listen to the server IP address.

Restart the PostgreSQL service to implement the above changes.

Finally check the service is listening on the correct port with the below command

Step 3:

In the PostgreSQL server, we enter IP address of the client as a trusted connection.

With the above changes in pg_hba.conf file, we have given access to all IPs in the 192.168.56.0 subnet.

With 192.168.56.1/32, we can only grant access to the IP we want.

If only one client is going to access it, it’s safer to allow access to a single IP.

 

Step 4:

Reloading the configuration file

 

The connection can be established now

Connection to server via terminal.

Note: It gives a warning when the client version and the server version are different.

To see the connections and processes