dblink in postgresql

dblink  is a Postgresql extension and supports executing  a query in a remote database, dblink which is very similar to Linked Servers in SQL Server. 

There is a substantial similarity between dblink & Foreign Data Wrapper (postgres_fdw) , and the PostgreSQL documentation suggests\recommends  using postgres_fdw due to a number of benefits such as :

1) standards compliance . 

2) performance 

3)  postgres_fdw offers a read only capability

4)  postgres_fdw establishes a permanent connection in comparison to dblink which is recreated each time and survives just for the session

Although the recommendation is to use postgres_fdw , the dblink has benefits:

1) commands available and works well in a programmatic framework.

dblink setup example

Before you proceed with creating the the dblink instance , you can look up the availability of the extension within the PostgreSQL server . The pg_available_extensions view returns the available extensions for installation

SELECT * FROM  pg_available_extensions

--find the reference to dblink

CREATE EXTENSION dblink;

--done! or alternatively you can setup dblink and associate with a specific schema

CREATE EXTENSION dblink SCHEMA myschema;

--test the connection

SELECT dblink_connect('host=localhost port=5444 user=myuser password=mypw dbname=mydb');

Read more on Foreign Data Wrapper PostgreSQL

Read more on How to list extensions in PostgreSQL