create mysql table in PostgreSQL with mysql_fdw

背景

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

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

下载

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

https://github.com/EnterpriseDB/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');  

后面的PG版本，可以使用如下语法直接IMPORT整个库生成本地的外部表。

Command:     IMPORT FOREIGN SCHEMA  
Description: import table definitions from a foreign server  
Syntax:  
IMPORT FOREIGN SCHEMA remote_schema  
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]  
    FROM SERVER server_name  
    INTO local_schema  
    [ OPTIONS ( option 'value' [, ... ] ) ]  

参考

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

https://github.com/EnterpriseDB/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/

digoal’s 大量PostgreSQL文章入口