FOREIGN TABLES

FOREIGN TABLES/DATA WRAPPERS
===============================

Foreign data wrapper is a library which understand the heterogeneous database information. For example, PostgreSQL does not understand the MYSQL data structure/information since both engines have different mechanism. If we want to get any heterogeneous database information then we need to configure the respective fdw(Foreign Data Wrapper) into the PostgreSQL Library location.

Please find the below link, which gives you all the available Foreign Data Wrappers.
http://wiki.postgresql.org/wiki/Foreign_data_wrappers

Here we have chosen MYSQL table as a source to PostgreSQL. Below are the steps.

1) Install mysql and mysql-devel using yum .

yum install mysql*

2) Install PostgreSQL 9.1 through EnterpriseDB graphical installer. 3) Get the MYSQL FDW from the below link.

https://github.com/dpage/mysql_fdw/archive/master.tar.gz

4) set the "PATH" as shown below.

export PATH=<PostgreSQL 9.1 Bin>:<Mysql Bin>:$PATH;

Ex:-

[root@localhost mysql_fdw-master]# echo $PATH 
/opt/PostgreSQL/9.1/bin/:/usr/bin/mysql:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

5) Make & Make Install

[root@localhost mysql_fdw-master]# make USE_PGXS=1
[root@localhost mysql_fdw-master]# make USE_PGXS=1 install

6) Create an Extension & Server as below.

postgres=# create EXTENSION mysql_fdw ;
CREATE EXTENSION


postgres=# CREATE SERVER mysql_svr
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (address '127.0.0.1', port '3306'); --MySql Port Default 3306

7) Create USER Mapping from PUBLIC users to "MySql Root".

CREATE USER MAPPING FOR PUBLIC
SERVER mysql_svr
OPTIONS (username 'root', password 'root');
CREATE USER MAPPING

8) Create Foreign Table as below.

postgres=# CREATE FOREIGN TABLE TEST(T INT) SERVER mysql_svr OPTIONS(TABLE 'DINESH.XYZ'); --Dinesh is a database & XYZ is a table.
CREATE FOREIGN TABLE

9) From MYSQL

mysql> \u DINESH
Database changed
mysql> SELECT * FROM XYZ;
+------+
| T    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

10) From PostgreSQL

postgres=# select * from test;
 t 
---
 1
 2
 3
(3 rows)

postgres=# explain analyze select * from test;
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Foreign Scan on test  (cost=10.00..13.00 rows=3 width=4) (actual time=0.211..0.212 rows=3 loops=1)

   Local server startup cost: 10
   MySQL query: SELECT * FROM DINESH.XYZ
 Total runtime: 0.675 ms
(4 rows)

దినేష్ కుమార్ 
Dinesh Kumar