PostgreSQL SQL/MED

SQL/MED

SQL/MED是SQL语言中管理外部数据的一个扩展标准。

PostgreSQL从9.1版本开始提供对SQL/MED标准的支持，通过SQL/MED可以连接到各种异构数据库或其他PostgreSQL数据库。

在SQL/MED标准中，实现了以下4类数据库对象来访问外部数据源。

• Foreign Data Wrapper：外部数据包装器，可以缩写为“FDW”，相当于定义外部数据驱动。
• Server：外部数据服务器，相当于定义一个外部数据源，需要指定外部数据源的Foreign Data Wrapper。
• User Mapping：用户映射，主要把外部数据源的用户映射到本地用户，用于控制权限。
• Foreign Table：外部表，把外部数据源映射成数据库中的一张外部表。

安装MYSQL_FDW插件

下载MYSQL_FDW插件
https://github.com/EnterpriseDB/mysql_fdw/releases

下载mysql客户端

配置postgres用户环境变量，增加mysql相关路径

su - postgres
vi .base_profile

export MYSQL_HOME=/app/mysql
export PGHOME=/app/postgresql
export PGUSER=postgres
export PGPORT=5432
export PGDATA=/app/postgresql/data
export PGLOG=/app/postgresql/log/postgresql.log
# PGHOST 需要与unix_socket_directories对应
export PGHOST=/app/postgresql/data/tmp
export PATH=$MYSQL_HOME/bin:$PGHOME/bin:$PATH:$HOME/bin
export LD_LIBRARY_PATH=$MYSQL_HOME/lib:$PGHOME/lib:$LD_LIBRARY_PATH

确保pg_config 和 mysql_config在$PATH路径里面。

which mysql_config
which pg_config

安装插件

make USE_PGXS=1
make USE_PGXS=1 install

/app/postgresql/bin/pg_ctl -D /app/postgresql/data -l /app/postgresql/log/logfile start

创建MySQL外部包服务对象

CREATE EXTENSION mysql_fdw;
# 查询是否创建成功
\dx 

外部数据包装器对象

创建外部文件包装器的示例如下：

CREATE FUNCTION file_fdw_handler()
RETURNS fdw_handler
AS 'file_fdw' LANGUAGE C STRICT;

CREATE FUNCTION file_fdw_validator(text[], oid)
RETURNS void
AS 'file_fdw' LANGUAGE C STRICT;

CREATE FOREIGN DATA WRAPPER file_fdw
HANDLER file_fdw_handler
VALIDATOR file_fdw_validator;

上面的示例中，第一个SQL创建了一个handle函数，第二个SQL语句创建了一个validator函数，第三个SQL语句创建了外部数据包装器，创建时指定了handle函数和validator函数。

handle函数有如下3点要求：

• 必须是用C语言写的扩展函数。
• 不能有参数。
• 必须返回“fdw_handler”类型。validator函数的要求如下：
• 必须有两个参数。
• 第一个参数类型必须是text[]，表示要校验的可选参数。
• 第二个参数类型必须是oid，指定可选参数的分类，分类为“server”“user mapping”“FDW”“Table”。

外部服务器对象

下面讲解外部服务器对象，即Server对象。Server对象是把FDW与连接外部数据源的连接参数关联起来的对象，主要定义如何连接外部数据源。创建Server对象的语法格式如下：

CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]
FOREIGN DATA WRAPPER fdw_name
[ OPTIONS ( option 'value' [, ... ] ) ]

语法说明如下:

• server_name：外部Server的名称。
• server_type：可选项，指定外部服务器的类型，是否使用此选项与具体的外部数据包装器有关，如果外部数据包装器没有此选项，则不需要定义此选项。
• server_version：外部服务器的版本，也与具体的外部数据包装器有关。
• fdw_name：指定此外部服务器的外部数据包装器。
• OPTIONS ( option ‘value’ [,…])：这些选项主要用于如何连接外部数据源，如连接外部数据源的IP地址、端口及其他一些参数，也与具体的外部数据包装器有关。

下面的示例是创建一个指向MySQL数据库的外部数据服务器：

CREATE SERVER mysql_fdw_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '111.11.11.11', port '3306');

用户映射对象

下面讲解SQL/MED中的用户映射，用户映射主要解决PostgreSQL用户与外部服务器的用户之间的映射关系。创建用户映射的语法格式如下：

CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC }
SERVER server_name
[ OPTIONS ( option 'value' [ , ... ] ) ]

语法说明如下：

• user_name：代表本地PostgreSQL数据库的用户，如果为“CURRENT_USER”或“USER”则代表当前的用户。当声明PUBLIC时，一个所谓的公共映射就创建完成了，当没有特定用户的映射时就会使用该公共映射。
• server_name：指定一个服务名称，就是前面用CREATE SERVER命令创建的名称。
• OPTIONS ( option ‘value’ [,…])：该选项通常定义映射的远程数据源上实际的用户名和密码。选项名称必须是唯一的。具体允许哪些选项是由外部数据包装器决定的。

示例如下：

CREATE USER MAPPING FOR PUBLIC SERVER mysql_fdw_server OPTIONS (username 'a', password 'a');
grant usage on foreign server mysql_fdw_server to dams;

创建外部表

create foreign table damsschema.a(a varchar(255),b varchar(255)) server mysql_fdw_server options(dbname 'test',table_name 'a');