Postgresql - Foreign data wrappers - mysql_fdw

将外部数据库当作外部表访问，可以访问PG，Oracle，MySQL，MS SQL Server，SQLite等。

我们将在以下环境中进行实验：

CentOS 7 + PG 10.1

CentOS 7 + MySQL 5.7.22

使用MySQL_FDW我们需要安装插件，

插件地址：

https://github.com/EnterpriseDB/mysql_fdw

安装mysql_fdw：

1. 下载

git clone https://github.com/EnterpriseDB/mysql_fdw.git

2.

export PATH=/usr/local/postgresql_10.1/bin/:$PATH

3. The mysql_config must also be in the path, it resides in the MySQL bin directory.

export PATH=/usr/bin/:$PATH

4.

make USE_PGXS=1

5.

make USE_PGXS=1 install

使用：

1. create extension

mytest=# create extension mysql_fdw ;

CREATE EXTENSION

2. Create Server

mytest=# CREATE SERVER mysql_server

mytest-# FOREIGN DATA WRAPPER mysql_fdw

mytest-# OPTIONS (host '192.168.4.17', port '3306');

CREATE SERVER

3. Create user mapping

mytest=# CREATE USER MAPPING FOR mytest

mytest-# SERVER mysql_server

mytest-# OPTIONS (username 'test', password 'Test!12345');

CREATE USER MAPPING

4. create foreign table

mytest=# CREATE FOREIGN TABLE test01_mysql(

mytest(# id int,

mytest(# col1 varchar(20),

mytest(# col2 varchar(20),

mytest(# col3 date

mytest(# )

mytest-# SERVER mysql_server

mytest-# OPTIONS (dbname 'mytest', table_name 'test01');

CREATE FOREIGN TABLE

5. verify

mytest=# select * from test01_mysql ;

id | col1 | col2 | col3

----+------+------+------------

1 | a | aa | 2018-07-11

(1 row)

mytest=# \d

List of relations

Schema | Name | Type | Owner

--------+--------------+---------------+--------

public | test01_mysql | foreign table | mytest

6. 向外部表插入数据

如果向外部表插入数据，第一个列必须为unique的，尽量做成主键

mytest=# insert into test01_mysql values (2,'b','bb',current_date);

ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation

在mysql端执行。

mysql> alter table test01 add primary key (id);

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

回到PG，执行插入成功

mytest=# insert into test01_mysql values (2,'b','bb',current_date);

INSERT 0 1

mytest=# select * from test01_mysql ;

id | col1 | col2 | col3

----+------+------+------------

1 | a | aa | 2018-07-11

2 | b | bb | 2018-07-11

(2 rows)