create mysql table in PostgreSQL with mysql_fdw

以前写过一些在PostgreSQL中创建file, CouchDB, Redis, PostgreSQL, Oracle等外部表的BLOG.

一直没有写MySQL的，刚好最近有一个项目要从MySQL迁移到PostgreSQL。今天补上如下 : 

下载

http://pgxn.org/dist/mysql_fdw/

1. 

修改PATH和LD_LIBRARY_PATH包含mysql和postgresql的相关目录, 例如

su - root
export MYSQLHOME=/usr/local/mysql
export PGHOME=/usr/local/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:$MYSQLHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export PATH=$PGHOME/bin:$MYSQLHOME/bin:$PATH:.
make USE_PGXS=1
make USE_PGXS=1 install

2. mysql中的测试表

mysql> desc CMSArticles
    -> ;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| articleid   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| position    | int(11)          | NO   | MUL | 0       |                |
| cateid      | int(11) unsigned | NO   | MUL | 0       |                |
| db_id       | varchar(20)      | YES  | MUL | NULL    |                |
| title       | varchar(50)      | YES  |     | NULL    |                |
| link        | varchar(200)     | YES  |     | NULL    |                |
| icon        | varchar(200)     | YES  |     | NULL    |                |
| tag         | varchar(50)      | YES  |     | NULL    |                |
| keyword     | varchar(50)      | YES  |     | NULL    |                |
| tag_url     | varchar(200)     | YES  |     | NULL    |                |
| keyword_url | varchar(200)     | YES  |     | NULL    |                |
| summary     | varchar(300)     | YES  |     | NULL    |                |
| content     | text             | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

3. 在库中创建extension

psql -h 127.0.0.1 -U postgres -d digoal
# create extension mysql_fdw;
# create server mysql_server1 foreign data wrapper mysql_fdw options(address '127.0.0.1', port '3306');
# CREATE USER MAPPING FOR digoal server mysql_server1 options (username 'root', password 'Roote');
# grant usage on FOREIGN server mysql_server1 to digoal;
\c digoal digoal
> create FOREIGN TABLE mysql_foreign_table1 (articleid int8, position int8, cateid int8, db_id varchar(20), title varchar(50), link varchar(200), icon varchar(200), tag varchar(50), keyword varchar(50), tag_url varchar(200), keyword_url varchar(200), summary varchar(300), content text)
SERVER mysql_server1
OPTIONS (database 'my_digoal', table 'CMSArticles');
> select count(*) from CMSArticles;

与MySQL结果一致.

前面使用的是table选项, 另外一个创建外部表的参数, query. 如下 :

create FOREIGN TABLE mysql_foreign_table2 (articleid int8, position int8, cateid int8, db_id varchar(20), title varchar(50), link varchar(200), icon varchar(200), tag varchar(50), keyword varchar(50), tag_url varchar(200), keyword_url varchar(200), summary varchar(300), content text)
SERVER mysql_server1
OPTIONS (database 'my_digoal', query 'select articleid, position, cateid, db_id, title, link, icon, tag, keyword, tag_url, keyword_url, summary, content from CMSArticles where articleid=1');

【参考】

http://pgxn.org/dist/mysql_fdw/

http://blog.163.com/digoal@126/blog/static/163877040201141641148311/

http://blog.163.com/digoal@126/blog/static/16387704020119181188247/

http://blog.163.com/digoal@126/blog/static/163877040201181505331588/

http://blog.163.com/digoal@126/blog/static/16387704020118151162340/