This tutorial covers how to work with the PostgreSQL dblink extension. We will see how to allow the remote connection to a PostgreSQL remote database and execute the queries on the client machine.
PostgreSQL Remote Database Connection
When you want to use the PostgreSQL dblink extension to connect to a remote server, you must have the IP address of the PostgreSQL database. Still, you must know which port to allow a TCP connection through.
When you have the remote IP for the PostgreSQL database, you can attempt to check the connection using the following syntax:
By executing the command, we can see that the connection to the server failed.
The error implies that we did not allow the remote connection to the PostgreSQL database. Let’s quickly allow the remote access to our Postgres server before we can use the dblink extension on the Postgres client.
Access the “postgresql.conf” file on your remote Postgres and edit it. You can open the configuration file with an editor of choice. Run the following command. Here, we run the PostgreSQL version 14. Replace it with that in your case.
When you open the file, locate the IPV4 local connection section as shown in the following:
Modify the connection to add the following line and comment out the localhost connection by adding a “#”:
Your file should now look like the following. Save the changes and exit the file:
You must also open the “postgresql.conf” file and edit the connection source. Open it with the following command:
Locate the line which contains the listen_addresses and uncomment it by removing the hashtag. Replace the line to allow PostgreSQL to accept the connections from all IP addresses by adding an asterisk instead of the “localhost”.
Your configuration file should appear like in the following image:
If you don’t want to allow the connection from all available IP addresses, you can replace the asterisk with the specific IP address that you want to allow. Save the changes and exit the file.
Postgres remote connection is made via TCP connection on port 5432. Hence, you must create a firewall rule to allow the connection via 5432/tcp by executing the following command:
You should get an output which confirms that the rule has been added. In our case, we get an output which confirms that we already have the firewall rule.
Next, you must restart the Postgres service on the server for the change to take effect.
You can verify that Postgres is running on your server by checking its status. It should display an active status.
Connecting to Remote PostgreSQL Database via the Dblink Extension
Before creating our dblink extension, it’s wise to verify that you can remotely connect to the Postgres server.
Execute the previous command on your Postgres client machine. If the Postgres server allows the remote connection, you must enter its Postgres database password for authentication.
We verified that we can remotely connect to our PostgreSQL database on the client machine. Hence, we are guaranteed that the dblink extension works.
Exit the remote Postgres shell by typing \q or exit. We now need to access the client PostgreSQL database. For that, execute the following command to open up the Postgres shell:
Once the Postgres shell opens, create the dblink extension by executing the following command:
Once the extension is created, connect to the remote PostgreSQL database. You must have the port number, the IP address of the remote PostgreSQL database, and the username of the account that accesses the database and the password.
Use the following syntax and replace it with your values:
The “connect-name” is any random name that you will use to connect to the remote PostgreSQL database. For our case, we set it to “remote test1”.
The command should return an “Ok” output to confirm that the connection is created successfully.
In the client Postgres, we execute a query to display all the table contents in the Postgres server. The remote PostgreSQL database contains the details table which contains different values as shown in the following image:
Let’s now execute a select query to retrieve the table elements from the client Postgres. We use the dblink extension that we created and run the following command for that:
Ensure that you replace the values to match the values of your case and specify the table that you want to execute the query on.
The previous output confirms that our query has been successfully executed and that we utilized the dblink extension to connect to our Postgres server and execute different queries.
Conclusion
Postgres offers the dblink extension to facilitate the connection to a remote PostgreSQL database. This tutorial explained the steps to use the dblink extension, from allowing the remote connection to creating the dblink connection on the client machine. That’s it!
