.

When setting up a PostgreSQL database server, it often defaults to listening only on localhost, which restricts remote TCP/IP connections. To enable remote connections, you need to modify PostgreSQL's configuration files and settings. Here’s a step-by-step guide to achieve this on Ubuntu.

Step 1: Install PostgreSQL

If PostgreSQL is not installed, you can do so using the following command:

sudo apt update

sudo apt install postgresql

Step 2: Edit postgresql.conf to Change Listen Address

1. Open the postgresql.conf file for editing with a text editor like vim:

   sudo vim /etc/postgresql/14/main/postgresql.conf

2. Find the listen_addresses parameter and change it to listen on all available IP addresses:

    listen_addresses = '*'

3. To edit the file in vim, press i to enter insert mode, make your changes, then press Esc and type :wq to save and exit.

Step 3: Edit pg_hba.conf for Remote Host Access

1. PostgreSQL uses pg_hba.conf to control client authentication. Open this file for editing:

    sudo vim /etc/postgresql/14/main/pg_hba.conf

2. Add the following lines at the bottom to allow connections from any IP address (0.0.0.0/0 for IPv4 and ::0/0 for IPv6):

   # IPv4 Addresses host all all 0.0.0.0/0 md5 
   # IPv6 Addresses host all all ::0/0 md5

Step 4: Restart PostgreSQL

Apply the changes made in postgresql.conf and pg_hba.conf by restarting the PostgreSQL service:

sudo systemctl restart postgresql

Step 5: Verify PostgreSQL Listening Port

To ensure PostgreSQL is listening on the correct port (default is 5432), you can use the following command:

ss -nlt | grep 5432

Step 6: Connect Using pgAdmin 4

1. Launch pgAdmin 4 and navigate to the dashboard.

2. Click on Add New Server.

3. In the Connection tab, enter the following details:

   • Name: A name for your server connection.
   • Host name/address: The IP address or hostname of your PostgreSQL server.
   • Port: The port PostgreSQL is listening on (usually 5432).
   • Username: Your PostgreSQL username.
   • Password: Your PostgreSQL password.

4. Click Save to connect to your PostgreSQL server remotely.

Conclusion

By following these steps, you have successfully configured PostgreSQL on Ubuntu to allow remote TCP/IP connections. This setup is crucial for accessing your database server from other machines or applications, enabling efficient management and utilization of PostgreSQL in your environment.

For more detailed PostgreSQL configuration options and security considerations, refer to the PostgreSQL Documentation.