fdw pg数据库_PostgreSQL通过mysql_fdw访问MySQL数据库

Mysql与PostgreSQL的安装过程省略。

为简便起见，把MySQL和PostgreSQL都安装在一个机器上，然后在此机器上(准确地说是在PostgreSQL运行的机器上)安装mysql_fdw：

首先是下载 mysql_fdw：

mysql_fdw-1.0.1.zip

然后是解压和安装：

[root@server mysql_fdw-1.0.1]# pwd

/soft/fdw/mysql_fdw-1.0.1

[root@server mysql_fdw-1.0.1]# ls

META.json Makefile README mysql_fdw--1.0.sql mysql_fdw.c mysql_fdw.control

运行 make 和 make install，其README文件说得很清楚：

[root@server mysql_fdw-1.0.1]# cat README

MySQL FDW for PostgreSQL 9.1+

==============================

This PostgreSQL extension implements a Foreign Data Wrapper (FDW) for

the MySQL.

This code is experimental, and largely intended as a pet project for me

to experiment with and learn about FDWs in PostgreSQL.

By all means use it, but do so entirely at your own risk! You have been

warned!

Building

--------

Install MySQL, or just the C client library, and Once that's done, the

extension can be built with:

PATH=/usr/local/pgsql/bin/:/usr/local/mysql/bin:$PATH make USE_PGXS=1

sudo PATH=/usr/local/pgsql/bin/:/usr/local/mysql/bin:$PATH make USE_PGXS=1 install

(assuming you have PostgreSQL 9.1 in /usr/local/pgsql and MySQL in

/usr/local/mysql).

I've tested on Mac OS X 10.7 only, but other *nix's should also work.

I haven't tested on Windows, but the code should be good on MinGW.

Limitations

-----------

- No attempt is made to pushdown quals to MySQL.

- The MySQL connection used to plan queries isn't currently reused

during execution.

Usage

-----

The following parameters can be set on a MySQL foreign server:

address: The address or hostname of the MySQL server.

Default: 127.0.0.1

port: The port number on which the MySQL server is listening.

Default: 3306

The following parameter can be set on a MySQL foreign table:

database: The name of the MySQL database to query.

Default: NULL

query: An SQL query to define the data set on the MySQL server.

table: The name of a table (quoted and qualified as required)

on the MySQL table.

Note that the query and table paramters are mutually exclusive. Using

query can provide either a simple way to push down quals (which of

course is fixed at definition time), or to base remote tables on

more complex SQL queries.

The following parameter can be set on a user mapping for a MySQL

foreign server:

username: The username to use when connecting to MySQL

Default

password: The password to authenticate to the MySQL server with.

Default:

Example

-------

-- Install the extension

CREATE EXTENSION mysql_fdw;

-- Create the foreign server, a pointer to the MySQL server.

CREATE SERVER mysql_svr

FOREIGN DATA WRAPPER mysql_fdw

OPTIONS (address '127.0.0.1', port '3306');

-- Create one or more foreign tables on the MySQL server. The first of

-- these maps to a remote table, whilst the second uses an SQL query.

CREATE FOREIGN TABLE employees (

id integer,

name text,

address text)

SERVER mysql_svr

OPTIONS (table 'hr.employees');

CREATE FOREIGN TABLE overtime_2010 (

id integer,

employee_id integer,

hours integer)

SERVER mysql_svr

OPTIONS (query 'SELECT id, employee_id, hours FROM hr.overtime WHERE year = 2010;');

-- Create a user mapping to tell the FDW the username/password to

-- use to connect to MySQL, for PUBLIC. This could be done on a per-

-- role basis.

CREATE USER MAPPING FOR PUBLIC

SERVER mysql_svr

OPTIONS (username 'dpage', password '');

--

Dave Page

dpage@pgadmin.org

[root@server mysql_fdw-1.0.1]#

[root@server mysql_fdw-1.0.1]# PATH=/usr/local/pgsql/bin/:/usr/local/mysql/bin:$PATH make USE_PGXS=1

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/usr/local/mysql/include -I. -I. -I/usr/local/pgsql/include/server -I/usr/local/pgsql/include/internal -D_GNU_SOURCE -c -o mysql_fdw.o mysql_fdw.c

mysql_fdw.c: In function 'mysqlPlanForeignScan':

mysql_fdw.c:395: 警告: 'rows' may be used uninitialized in this function

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o mysql_fdw.so mysql_fdw.o -L/usr/local/pgsql/lib -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags -L/usr/local/mysql/lib -lmysqlclient -lpthread -lm -lrt -ldl

[root@server mysql_fdw-1.0.1]# sudo PATH=/usr/local/pgsql/bin/:/usr/local/mysql/bin:$PATH make USE_PGXS=1 install

/bin/mkdir -p '/usr/local/pgsql/lib'

/bin/mkdir -p '/usr/local/pgsql/share/extension'

/bin/sh /usr/local/pgsql/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755 mysql_fdw.so '/usr/local/pgsql/lib/mysql_fdw.so'

/bin/sh /usr/local/pgsql/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mysql_fdw.control '/usr/local/pgsql/share/extension/'

/bin/sh /usr/local/pgsql/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mysql_fdw--1.0.sql '/usr/local/pgsql/share/extension/'

[root@server mysql_fdw-1.0.1]#

检查一下 mysql_fdw.so文件，是否出现在 /usr/local/pgsql/lib 目录下。

然后，分别启动mysql和 postgresql：

[root@server ~]# mysqld_safe &

[1] 3223

[root@server ~]# 130918 09:38:14 mysqld_safe Logging to '/usr/local/mysql/data/server.gao.err'.

130918 09:38:14 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@server ~]# su - postgres

[postgres@server ~]$ pwd

/home/postgres

[postgres@server ~]$ cd /usr/local/pgsql

[postgres@server pgsql]$ ./bin/pg_ctl -D ./data start

server starting

[postgres@server pgsql]$ LOG: database system was shut down at 2013-09-13 13:36:47 CST

LOG: autovacuum launcher started

LOG: database system is ready to accept connections

在PostgreSQL端，建立FDW:

[postgres@server pgsql]$ ./bin/psql

psql (9.1.2)

Type "help" for help.

postgres=# CREATE EXTENSION mysql_fdw;

ERROR: extension "mysql_fdw" already exists

postgres=# CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw OPTIONS (address '127.0.0.1', port '3306');

CREATE SERVER

postgres=# CREATE FOREIGN TABLE example (id INT,data VARCHAR(100) ) SERVER mysql_svr

postgres-# OPTIONS (table 'mysql.example');

CREATE FOREIGN TABLE

postgres=#

postgres=# CREATE USER MAPPING FOR PUBLIC SERVER mysql_svr OPTIONS (username 'usrabc', password 'usrabc');

CREATE USER MAPPING

postgres=#

postgres=# select * from example;

ERROR: failed to connect to MySQL: Access denied for user 'usrabc'@'localhost' (using password: YES)

postgres=#

postgres=# select * from example;

id | data

----+------

(0 rows)

postgres=#