PostgreSQL DB Link and FDW

DB Link:

                ‘DB Link’ in PostgreSQL enables a database user to access a table present on a different postgres instance. It provides a functionality in PostgreSQL similar to that of ‘DB Link’ in Oracle, ‘Linked Server’ in SQL Server and ‘Federated tables’ in MySQL.

Options:

dblink_connect — opens a persistent connection to a remote database

dblink_disconnect — closes a persistent connection to a remote database

dblink — executes a query in a remote database

dblink_exec — executes a command in a remote database

Example:

postgres=# create extension dblink ;

CREATE EXTENSION

postgres=# create server myserver foreign data wrapper dblink_fdw OPTIONS (host'192.168.1.201',dbname'pearl',port'5432');

CREATE SERVER

postgres=# create user MAPPING FOR postgres SERVER myserver OPTIONS (user'postgres',password'post');

CREATE USER MAPPING

postgres=# select * from dblink('myserver','select * from emp') as a(id int,name text,age int);

 id |   name   | age 

----+----------+-----

  1 | muthu    |  28

  2 | siva     |  25

  3 | chandru  |  27

  4 | thanigai |  25

  5 | kathir   |  27

  6 | jana     |  28

(6 rows)

postgres=# select dblink_exec('myserver','insert into emp values(7,''regan'',25);');

 dblink_exec 

-------------

 INSERT 0 1

(1 row)

postgres=# select dblink_exec('myserver','update emp set name=''muthu siva'' where id=1;');

 dblink_exec 

-------------

 UPDATE 1

(1 row)

postgres=# select dblink_exec('myserver','delete from emp where name=''siva'';');

 dblink_exec 

-------------

 DELETE 1

(1 row)

postgres=# select * from dblink('myserver','select * from emp') as a(id int,name text,age int);

 id |    name    | age 

----+------------+-----

  3 | chandru    |  27

  4 | thanigai   |  25

  5 | kathir     |  27

  6 | jana       |  28

  7 | regan      |  25

  1 | muthu siva |  28

(6 rows)

postgres=# select dblink_connect('myconn','myserver');

 dblink_connect 

----------------

 OK

(1 row)

postgres=# select * from dblink('myconn','select * from emp') as a(id int,name text,age int);

 id |    name    | age 

----+------------+-----

  3 | chandru    |  27

  4 | thanigai   |  25

  5 | kathir     |  27

  6 | jana       |  28

  7 | regan      |  25

  1 | muthu siva |  28

(6 rows)

postgres=# select dblink_disconnect('myconn');

 dblink_disconnect 

-------------------

 OK

(1 row)

FDW:

              PostgreSQL has a useful feature called Foreign Data Wrapper, which lets you create foreign tables in a PostgreSQL database that are proxies for some other data source. When you make a query against a foreign table, the Foreign Data Wrapper will query the external data source and return the results as if they were coming from a table in your database.

Example:

postgres=# create extension postgres_fdw ;

CREATE EXTENSION

postgres=# create server myserver foreign data wrapper postgres_fdw OPTIONS (host'192.168.1.201',dbname'pearl',port'5432');

CREATE SERVER

postgres=# create user MAPPING FOR postgres SERVER myserver OPTIONS (user'postgres',password'post');

CREATE USER MAPPING

postgres=# import foreign schema public from server myserver into public;

IMPORT FOREIGN SCHEMA

postgres=# select * from emp;

 id |    name    | age 

----+------------+-----

  3 | chandru    |  27

  4 | thanigai   |  25

  5 | kathir     |  27

  6 | jana       |  28

  7 | regan      |  25

  1 | muthu siva |  28

(6 rows)

postgres=# insert into emp values (8,'moorthi',28);

INSERT 0 1

postgres=# select * from emp;

 id |    name    | age 

----+------------+-----

  3 | chandru    |  27

  4 | thanigai   |  25

  5 | kathir     |  27

  6 | jana       |  28

  7 | regan      |  25

  1 | muthu siva |  28

  8 | moorthi    |  28

(7 rows)

postgres=# select * from pg_foreign_table ;

 ftrelid | ftserver |                ftoptions                

---------+----------+-----------------------------------------

   24828 |    24826 | {schema_name=public,table_name=company}

   24831 |    24826 | {schema_name=public,table_name=emp}

   24834 |    24826 | {schema_name=public,table_name=v1}

(3 rows)

postgres=# select * from pg_foreign_server;

 srvname  | srvowner | srvfdw | srvtype | srvversion | srvacl |                 srvoptions                  

----------+----------+--------+---------+------------+--------+---------------------------------------------

 myserver |       10 |  24825 |         |            |        | {host=192.168.1.201,dbname=pearl,port=5432}

(1 row)

postgres=# select * from pg_user_mapping;

 umuser | umserver |           umoptions           

--------+----------+-------------------------------

     10 |    24826 | {user=postgres,password=post}

(1 row)

postgres=# select * from pg_user_mappings;

 umid  | srvid | srvname  | umuser | usename  |           umoptions           

-------+-------+----------+--------+----------+-------------------------------

 24827 | 24826 | myserver |     10 | postgres | {user=postgres,password=post}

(1 row)