In this article I will describe how to connect to remote PostgreSQL database with PostgreSQL dblink.
Dblink usage is similar to other databases. but in order to use this dblink, we need to create the extension.
You can create the dblink extension with the help of the following command.
1 | CREATE EXTENSION dblink; |
After activating, you can connect using the command below.
1 | select id,ad from dblink('host=192.168.87.154 user=postgres password=P@sSWoRD1! dbname=link_d1', 'select id,ad from db_lnk1') as linktable(id int, ad varchar(150)); |
The parameters used in the above query can be found in the following table.
host = 192.168.87.154 -> the server IP you want to connect to
user = postgres -> the user with the superuser authority in the postgresql that you want to connect to
password = P@ssword1! -> the password of the user you specified above
dbname = link_d1 -> the database name in the postgresql that you want to connect to
‘select id, ad from db_lnk1’ -> query
linktable (id int, ad varchar (150)) -> linktable is a name that I give. You can also give another name. In the linktable, we specified the types of columns in the table named db_lnk1 that we used in the query.
The two servers need to be able to access each other through the postgresql ports in order to be able to query with dblink between these two servers.
If you are using a firewall in your organization, you must give the necessary permissions for the related ports between this two servers.
If you are not using a firewall and you have restricted the pg_hba.conf file, you must make the necessary definitions on the pg_hba.conf file so that this two servers can access each other through the corresponding port.