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');