DBLINK in PostgreSQL

Hi,

Find the below DBLINK concepts in PostgreSQL, PostgresPlus as well.


PostgreSQL
----------
Foreign Data Wrapper
--------------------
This is the one utility which encapsulates the data from the different Database servers using Handler functions.

Ex:- In PG 9.1, we can encapsulates the data from My Sql and store to PostgreSQL using handlers.
CREATE FOREIGN DATA WRAPPER HANDLER VALIDATOR ;

Currently(<= 9.0) PostgreSQL compatible with only remote PostgreSQL. From 9.1 onwards, we can make the PostgreSQL to communicate with SQL Server/My SQL through these wrappers. Servers ------- This is the one object which is having the Data wrapper methodology as well the remote host connectivity details. Ex:- CREATE SERVER FOREIGN DATA WRAPPER OPTIONS (address , port ,dbname );

User Mapping
------------
This is the one object which maps the current dbserver user/role to remote dbserver user.

Ex:-CREATE USER MAPPING FOR SERVER OPTIONS (username , password );

DBLINK
------
Dblink is one of the contrib module, which collects the data from the remote or different database through TCP/IP.

Test Case
---------
Remote
------
HOST :: 127.0.0.1
PORT :: 5434
Dbname :: postgres
Username :: postgres
Table :: dblinktest

Local
-----
HOST :: 127.0.0.1
PORT :: 5433
Username :: dinesh

Here, we are fetching the 5434 cluster's dblinktest table data as a "dinesh" user.

Step 1:
-------
postgres=# CREATE FOREIGN DATA WRAPPER DBLINK_FDW;
CREATE FOREIGN DATA WRAPPER

--It takes the postgresql by default handler and default validator.

Step 2:
-------
postgres=# CREATE SERVER DBLINK_FDW_SERVER FOREIGN DATA WRAPPER DBLINK_FDW OPTIONS(hostaddr '127.0.0.1',dbname 'postgres',port '5434');
CREATE SERVER

Step 3:
-------
postgres=# CREATE USER MAPPING FOR DINESH SERVER DBLINK_FDW_SERVER OPTIONS(USER 'postgres',password 'postgres');
CREATE USER MAPPING

Step 4:
-------
postgres=# GRANT USAGE ON FOREIGN SERVER DBLINK_FDW_SERVER to DINESH;
GRANT
--Giving the required permissions to the user Dinesh.

Step 5:
-------
postgres=# \c postgres dinesh
You are now connected to database "postgres".
postgres=> SELECT DBLINK_CONNECT('DBLINK_CONN','dblink_fdw_server');
dblink_connect
----------------
OK
(1 row)

Step 6:
-------
postgres=> SELECT * FROM DBLINK('DBLINK_CONN','SELECT * FROM TEST LIMIT 3') AS T(A INT);
a
---
1
2
3
(3 rows)


PostgresPlus
--------------
As we all know, Postgresplus is oracle compatable. So, We can directly execute the followind dblink in Postgreplus Advanced server.

Syntax:-

CREATE DATABASE LINK
CONNECT TO IDENTIFIED BY ''
USING libpq 'host= dbname= port=';

Ex:-
Step 1:
-------
edb=# CREATE DATABASE LINK DBLINK_PPAS CONNECT TO enterprisedb IDENTIFIED BY 'adminedb' using libpq 'host=127.0.0.1 dbname=edb port=5555';
CREATE DATABASE LINK
edb=# show port;
port
------
5444
(1 row)

Step 2:
-------
edb=# SELECT * FROM TEST@DBLINK_PPAS LIMIT 3;
t
---
1
2
3
(3 rows)

--Dinesh