mysql_fdw是一款基于bsd协议的开源工具，目前还不是postgresql内置，最新版本是1.0.1。
一、环境
centos 6.3
postgresql 9.3.4
host 10.1.11.73
二、mysql的数据准备
mysql> create database db_kenyon;query ok, 1 row affected (0.01 sec)mysql> create table tbl_kenyon(id int,vname varchar(48));query ok, 0 rows affected (0.00 sec)mysql> insert into tbl_kenyon values(1,'test');query ok, 1 row affected (0.00 sec)mysql> insert into tbl_kenyon values(2,'kenyon');query ok, 1 row affected (0.00 sec)mysql> insert into tbl_kenyon values(null,'it/'s null');query ok, 1 row affected (0.00 sec)mysql> insert into tbl_kenyon values(4,null);query ok, 1 row affected (0.01 sec)mysql> select * from tbl_kenyon;+------+-----------+| id | vname |+------+-----------+| 1 | test || 2 | kenyon || null | it's null || 4 | null |+------+-----------+4 rows in set (0.00 sec)mysql> grant select on db_kenyon.tbl_kenyon to 'usr_kenyon'@'%' identified by '123456';query ok, 0 rows affected (0.00 sec)mysql> flush privileges;query ok, 0 rows affected (0.00 sec)mysql> select host,user,password from mysql.user;+-----------+------------+-------------------------------------------+| host | user | password |+-----------+------------+-------------------------------------------+| localhost | root | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 || db1 | root | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 || 127.0.0.1 | root | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 || ::1 | root | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 || % | usr_kenyon | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |+-----------+------------+-------------------------------------------+5 rows in set (0.00 sec)
三、 安装使用 mysql_fdw
下载地址： http://pgxn.org/dist/mysql_fdw/
安装时使用postgres用户，参考readme，有两步
make use_pgxs=1
make use_pgxs=1 install
安装完可能会有的异常，error: could not load library /home/postgres/lib/mysql_fdw.so: libmysqlclient.so.18: cannot open shared object file: no such file or directory 将mysql下的libmysqlclient.so.18文件拷贝到/home/postgres/lib下面或者做个软连接就可以了
1.创建extension扩展 postgres=# create extension mysql_fdw ;create extension
2.创建server postgres=# create server mysql_svr foreign data wrapper mysql_fdw options (address '10.1.11.73', port '3306');create server
3.创建一个或多个外部表(foreign table) postgres=# create foreign table pg_mysql_tbl1 (id integer, name text) server mysql_svr options (table 'db_kenyon.tbl_kenyon');create foreign tablepostgres=# create foreign table pg_mysql_tbl2 ( id integer, vname text) server mysql_svr options (query 'select id, vname from db_kenyon.tbl_kenyon where id2;');create foreign table
4.创建postgresql的fdw查询用户，pg_hba配置略
postgres=# create user u_select encrypted password '123456';create role
5.创建用户匹配关系(user mapping),用户为远程mysql的用户密码 create user mapping for u_select server mysql_svr options (username 'usr_kenyon', password '123456');
6.查询数据，需要u_select登录，不然会报user mapping not found postgres=> select * from pg_mysql_tbl1 ; id | name ----+----------- 1 | test 2 | kenyon | it's null 4 | (4 rows)postgres=> select * from pg_mysql_tbl2; id | vname ----+------- 1 | test 4 | (2 rows)
四、删除扩展 postgres=# drop foreign table pg_mysql_tbl1;drop foreign tablepostgres=# drop foreign table pg_mysql_tbl2;drop foreign tablepostgres=# drop user mapping for u_select server mysql_svr ;drop user mappingpostgres=# drop server mysql_svr ;drop serverpostgres=# drop extension mysql_fdw ;drop extension
五、总结
1.这个工具在etl迁移mysql到postgresql时比较有用，跨平台，跨数据库，而且简单方便
2.目前该工具还不是内置版本，引起的风险要注意，比如mysql和pg库端表字段后类型不一样，会产生乱码
六、参考：
1.http://pgxn.org/dist/mysql_fdw/
2.http://francs3.blog.163.com/blog/static/40576727201111211324599/?suggestedreading