mysql 外部表_Postgresql使用Mysql_fdw联接外部表

一、安装Mysql数据库需安装Mysql开发包

#  yum install mysql-*

#wget -c http://api.pgxn.org/dist/mysql_fdw/2.1.2/mysql_fdw-2.1.2.zip

#unzip mysql_fdw-2.1.2.zip

# cd mysql_fdw-2.1.2

#  export PATH=/usr/local/pg9.5.2/bin/:$PATH

#  export PATH=/usr/local/bin/:$PATH

#  make USE_PGXS=1

#  make USE_PGXS=1 install

二、配置Postgresql数据库

[postgres@db2 ~]$ psql -U postgres

psql (9.5.2)

Type "help" for help.

postgres=# \l

List of databases

Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges

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

postgres     | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |

template0    | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +

|          |          |             |             | postgres=CTc/postgres

template1    | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +

|          |          |             |             | postgres=CTc/postgres

warehouse_db | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |

(4 rows)

postgres=# \c warehouse_db

You are now connected to database "warehouse_db" as user "postgres".

warehouse_db=# CREATE EXTENSION mysql_fdw;

CREATE EXTENSION

warehouse_db=# CREATE SERVER mysql_server

warehouse_db-#      FOREIGN DATA WRAPPER mysql_fdw

warehouse_db-#      OPTIONS (host '127.0.0.1', port '3306');

CREATE SERVER

warehouse_db=#

三、配置Mysql数据库

[root@db2 ~]# mysql -uroot -p8732

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 5.0.95 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant select,insert,update,create,delete,drop,index,alter,references,create temporary tables,lock tables on ghan. * to ghan@'localhost'  identified by "ghan";

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> \q

Bye

You have new mail in /var/spool/mail/root

[root@db2 ~]# mysql -ughan -pghan

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 11

Server version: 5.0.95 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \q

Bye

[root@db2 ~]#

四、初始化FOREIGN Table

warehouse_db=# CREATE FOREIGN TABLE warehouse(

warehouse_db(#     warehouse_id int,

warehouse_db(#      warehouse_name text)

warehouse_db-# SERVER mysql_server

warehouse_db-#      OPTIONS (dbname 'ghan', table_name 'warehouse');

CREATE FOREIGN TABLE

warehouse_db=# INSERT INTO warehouse values (1, 'UPS');

ERROR:  failed to execute the MySQL query:

Table 'ghan.warehouse' doesn't exist

warehouse_db=# INSERT INTO warehouse values (1, 'UPS');

ERROR:  failed to execute the MySQL query:

Table 'ghan.warehouse' doesn't exist

五、创建mysql create warehouse tables

mysql> CREATE  TABLE warehouse(

->      warehouse_id int,

->      warehouse_name text);

Query OK, 0 rows affected (0.16 sec)

mysql>

mysql> select * from warehouse;

Empty set (0.00 sec)

六、Postgreseq insert inot table 测试

warehouse_db=# INSERT INTO warehouse values (1, 'UPS');

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

warehouse_db=# INSERT INTO warehouse values (1, 'UPS');

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

七、创建 mysql create table unique index

mysql> CREATE UNIQUE INDEX warehouse_inx ON warehouse(warehouse_id);

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql>

八、postgresq insert into table 测试

[postgres@db2 ~]$ psql -U postgres

psql (9.5.2)

Type "help" for help.

postgres=# \c warehouse_db

You are now connected to database "warehouse_db" as user "postgres".

warehouse_db=# INSERT INTO warehouse values (1, 'UPS');

INSERT 0 1

warehouse_db=# INSERT INTO warehouse values (2, 'UPS');

INSERT 0 1

warehouse_db=# INSERT INTO warehouse values (4, 'UPS');

INSERT 0 1

warehouse_db=# INSERT INTO warehouse values (4, 'UPSdsasf');

ERROR:  failed to execute the MySQL query:

Duplicate entry '4' for key 1

warehouse_db=# INSERT INTO warehouse values (6, 'UPSdsasf');

INSERT 0 1

warehouse_db=# select * from warehouse;

warehouse_id | warehouse_name

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

1 | UPS

2 | UPS

4 | UPS

6 | UPSdsasf

(4 rows)

九、Postgresql\Mysql操作测试

9.0 查询测试

warehouse_db=# select * from  warehouse;

warehouse_id | warehouse_name

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

1 | UPS

2 | UPS

4 | UPS

6 | UPSdsasf

(4 rows)

warehouse_db=#

9.1、Mysql

mysql> select * from warehouse;

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

| warehouse_id | warehouse_name |

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

|            1 | UPS            |

|            2 | UPS            |

|            4 | UPS            |

|            6 | UPSdsasf       |

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

4 rows in set (0.00 sec)

mysql>

9.2、测试：

warehouse_db=# DELETE FROM warehouse where warehouse_id = 3;

DELETE 0

warehouse_db=# UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;

UPDATE 1

warehouse_db=# EXPLAIN SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_name LIKE 'UP' limit 1;

QUERY PLAN

---------------------------------------------------------------------------

Limit  (cost=10.00..11.00 rows=1 width=36)

->  Foreign Scan on warehouse  (cost=10.00..1010.00 rows=1000 width=36)

(2 rows)

warehouse_db=#

十、修改Mysql_fdw.c支持非唯一值索引

10.1.、修改mysql_fdw.c源文件

[root@db2 mysql_fdw-2.1.2]# vi mysql_fdw.c

1179         /*if (!mysql_is_column_unique(foreignTableId))

1180                 elog(ERROR, "first column of remote table must be unique for INSERT/UPDATE/DELETE operation");

1181           */

注掉即可

10.2 重新编译安装

[root@db2 mysql_fdw-2.1.2]# export PATH=/usr/local/pg9.5.2/bin/:$PATH

[root@db2 mysql_fdw-2.1.2]# export PATH=/usr/local/bin/:$PATH

[root@db2 mysql_fdw-2.1.2]# make USE_PGXS=1

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv

-O2 -fpic -I/usr/include/mysql -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/local/pg9.5.2/include/postgresql/server -

I/usr/local/pg9.5.2/include/postgresql/internal -D_GNU_SOURCE   -c -o mysql_fdw.o mysql_fdw.c

mysql_fdw.c:834: 警告：‘mysql_is_column_unique’ 定义后未使用

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv

-O2 -fpic -shared -o mysql_fdw.so connection.o option.o deparse.o mysql_query.o mysql_fdw.o -L/usr/local/pg9.5.2/lib  -Wl,-rpath,'/usr/local/pg9.5.2/lib',--enable-new-dtags

[root@db2 mysql_fdw-2.1.2]# make USE_PGXS=1 install

/bin/mkdir -p '/usr/local/pg9.5.2/lib/postgresql'

/bin/mkdir -p '/usr/local/pg9.5.2/share/postgresql/extension'

/bin/mkdir -p '/usr/local/pg9.5.2/share/postgresql/extension'

/usr/bin/install -c -m 755  mysql_fdw.so '/usr/local/pg9.5.2/lib/postgresql/mysql_fdw.so'

/usr/bin/install -c -m 644 .//mysql_fdw.control '/usr/local/pg9.5.2/share/postgresql/extension/'

/usr/bin/install -c -m 644 .//mysql_fdw--1.0.sql  '/usr/local/pg9.5.2/share/postgresql/extension/'

[root@db2 mysql_fdw-2.1.2]# su - postgres

10.3、在Mysql库新非唯一值索引表：

[root@db2 ~]# mysql -ughan -pghan

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 16

Server version: 5.0.95 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use ghan

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> CREATE  TABLE ghan(

->        warehouse_id int,

->     warehouse_name text);

Query OK, 0 rows affected (0.14 sec)

mysql> show tables;

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

| Tables_in_ghan |

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

| ghan           |

| warehouse      |

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

2 rows in set (0.00 sec)

mysql> use ghan

Database changed

mysql> select * from ghan;

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

| warehouse_id | warehouse_name |

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

|            6 | UPSdsasf       |

|            6 | UPSdsasf       |

|            5 | UPSdsasf       |

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

3 rows in set (0.00 sec)

mysql>

10.4、在Postgrsql测试

[root@db2 mysql_fdw-2.1.2]# su - postgres

[postgres@db2 ~]$ psql -U postgres

psql (9.5.2)

Type "help" for help.

postgres=# \c warehouse_db

You are now connected to database "warehouse_db" as user "postgres".

warehouse_db=# CREATE FOREIGN TABLE ghan(

warehouse_db(#       warehouse_id int,

warehouse_db(#      warehouse_name text)

warehouse_db-#  SERVER mysql_server

warehouse_db-#       OPTIONS (dbname 'ghan', table_name 'ghan');

CREATE FOREIGN TABLE

warehouse_db=# INSERT INTO ghan values (6, 'UPSdsasf');

INSERT 0 1

warehouse_db=# INSERT INTO ghan values (6, 'UPSdsasf');

INSERT 0 1

warehouse_db=# INSERT INTO ghan values (5, 'UPSdsasf');

INSERT 0 1

warehouse_db=# \q

[postgres@db2 ~]$