Dblinks provide to connect another databases. Also you can do DML operations with using dblink. I will use dblink to connect two PostgreSQL databases on this post. Also I will tell how to use select, update and delete with dblink.
Firstly, you have to create dblink entension with related user which is using dblink.
Create Dblink Extension
postgres@testdb ~]# psql psql> create extension dblink
Dblink Connection Test
psql> select dblink_connect('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword'); If result is OK, your connection test is successful.
Select Operation With Dblink
psql> select * from dblink('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','select id,name from remotetable') as t(id numeric(10,0),name character varying(100));
Update Operation With Dblink
psql> select dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','update remotetable set name=''Fatih'' where id = 1'); You can use dynamic variable to where condition. psql> select dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','update remotetable set name=''Fatih'' where id = ' || ID || ''); If you get an error like "ERROR: query has no destination for result data, if you want to discard the results of a select, use perform instead.", you can use perform statement instead of select. psql> perform dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','update remotetable set name=''Fatih'' where id = 1');
Delete Operation With Dblink
psql> select dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','delete from remotetable where id = 1'); You can use dynamic variable to where condition. psql> select dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','delete from remotetable where id = ' || ID || ''); If you get an error like "ERROR: query has no destination for result data, if you want to discard the results of a select, use perform instead.", you can use perform statement instead of select. psql> perform dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','delete from remotetable where id = 1');