PostgreSQL9.6中使用mysql_fdw

安装

SELECT * from pg_available_extensions where name like '%fdw%';
     name     | default_version | installed_version |                      comment                       
--------------+-----------------+-------------------+----------------------------------------------------
 file_fdw     | 1.0             | [null]            | foreign-data wrapper for flat file access
 mysql_fdw    | 1.0             | [null]            | Foreign data wrapper for querying a MySQL server
 postgres_fdw | 1.0             | [null]            | foreign-data wrapper for remote PostgreSQL servers
(3 rows)

Time: 1.742 ms

使用

CREATE EXTENSION mysql_fdw;

如果是源码编译安装的MySQL，如果没有设置LD_LIBRARY_PATH，会报如下的错误:

CREATE EXTENSION mysql_fdw;
ERROR:  HV00L: failed to load the mysql query: 
libmysqlclient.so: cannot open shared object file: No such file or directory
HINT:  export LD_LIBRARY_PATH to locate the library
LOCATION:  _PG_init, mysql_fdw.c:267
Time: 74.768 ms

这时，需要停止PG服务器，然后在启动PG服务器之前，要配置好LD_LIBRARY_PATH环境变量，将它指向MySQL_HOME/lib目录下.即：

export LD_LIBRARY_PATH=MySQL_HOME/lib:$LD_LIBRARY_PATH
pg_ctl -D pg_data_dir start

然后再创建mysql_fdw即可。

CREATE EXTENSION mysql_fdw ;
CREATE EXTENSION
Time: 155.894 ms

连接外部表

CREATE SERVER mysql_server_beta FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'your_ip_address', port 'your_mysql_port');
CREATE USER MAPPING FOR 你的pg的用户名 SERVER mysql_server_beta OPTIONS (username 'your_mysql_user_name', password 'your_mysql_passwd');

CREATE FOREIGN TABLE wb_status_ft (
    id int,
    sid bigint,
	idstr varchar(64),
	mid bigint,
	user_id bigint,
	user_screen_name varchar(40),
	user_profile_image_url varchar(128),
	text varchar(1024),
	source varchar(256),
	thumbnail_pic varchar(256),
	bmiddle_pic varchar(256),
	original_pic varchar(256),
	retweeted_status_id varchar(64),
	geo varchar(256),
	reposts_count int,
	comments_count int,
	attitudes_count int,
	visible varchar(64),
	pic_urls varchar(1024),
	create_at timestamp,
	update_at timestamp,
	ad varchar(256),
	is_deleted smallint
) SERVER mysql_server_beta OPTIONS (dbname '原mysql的数据库名', table_name '原mysql的表名');

done.

Contents

安装

使用