Panwei fdw插件介绍与实操

FDW 是 Foreign Data Wrappers 的简称,叫做外部数据封装。外部数据是通过外部数据封装器的帮助来访问的。一个外部数据封装器是一个可以与外部数据源沟通的库，隐藏与外部数据源连接的细节并且从外部数据源获得数据。本篇介绍了 PanWeiDB 支持的外部数据封装器，分为四个章节：Oracle_fdw,postgre_fdw,dblink_connect，database link

FDW机制由四个核心组件构成：

1. Foreign Data Wrapper：特定于各数据源的库，定义了如何建立与外部数据源的连接、执行查询及处理其他操作。

2. Foreign Server：在本地PostgreSQL中定义一个外部服务器对象，对应实际的远程或非本地数据存储实例。

3. User Mapping：为每个外部服务器设置用户映射，明确哪些本地用户有权访问，并提供相应的认证信息，如用户名和密码。

4. Foreign Table：在本地数据库创建表结构，作为外部数据源中表的映射。对这些外部表发起的 SQL 查询将被转换并传递给相应的 FDW，在外部数据源上执行。

Oracle_FDW

1.1 功能描述

支持创建外部数据封装器 oracle_fdw，连接 Oracle 数据库，并能在外部表上进行查询、插入、更新和删除操作。

1.2 编译 Oracle_FDW

pg需要进行fdw插件的编译和oracle客户端的安装，但是panwei不需要，它可以直接创建fdw插件

进入安装目录的lib/postgresql/下，可以看到安装产物so文件

安装目录的share/postgresql/extension/下可以看到 相关的 sql 文件和 control 文件

1.3 使用 Oracle_FDW

1.3.1 磐维 加载oracle_fdw 扩展：

CREATE EXTENSION oracle_fdw;

创建用户

create user oracle password 'Qhyd_2024';

grant usage on foreign data wrapper oracle_fdw to oracle;

1.3.2 创建 FDWServer 用于识别外部数据源。（磐维的oracle用户创建）

\c - oracle

create server ora_fdw_server foreign data wrapper oracle_fdw options(dbserver '10.230.XX.XX:16XX/XXdb_cntst')

1.3.3 创建 USER MAPPING 用于映射 Oracle 数据库用户。（panwei的oracle用户创建）

create user mapping for oracle server ora_fdw_server options(user 'fdw',password 'XXX_XXX');

用户的口令会加密后保存到系统表 PG_USER_MAPPING中，加密时需要使用 usermapping.key.cipher 和usermapping.key.rand 作为加密密码文件和加密因子。首次使用前需要通过如下命令创建这两个文件，并将这两个文件放入各节点目录$GAUSSHOME/bin，且确保具有读权限

1.3.4 在 oracle 创建表

1.3.5 在 panweidb 创建外部表，切换到用户 oracle,查询和插入oracle新建表。

\c - oracle

create foreign table emp_fdw_ora(id int,name varchar2(25)) server ora_fdw_server options(schema 'DW_ENMO',table 'TEMP');

select * from emp_fdw_ora_enmo;

insert into emp_fdw_ora_enmo values(6,'FF');后再次查询

注意事项

 两个Oracle 外表间的 SELECT JOIN 不支持下推到 Oracleserver 执行，会被分成两条 SQL 语句传递到 Oracle 执行，然后在 PanWeiDB 处汇总处理结果。

 不支持 IMPORT FOREIGN SCHEMA 语法。

 不支持对外表进行 CREATE TRIGGER 操作。

2 POSTGRE_FDW

2.1 功能描述

postgres_fdw 功能提供了外部数据封装器postgres_fdw插件，它可以被用来访问存储在外部 PanWeiDB 或 PostgreSQL 服务器中的数据。用户可以通过指定参数 remote_kind 的值来控制远端连接的数据库为PanWeiDB 或是 PostgreSQL11。

2.2 注意事项

 两个postgres_fdw外表间的 SELECTJOIN 不支持下推到远端PanWeiDB 执行，会被分成两条 SQL 语句传递到远端PanWeiDB 执行，然后在本地汇总处理结果。

 支持使用 PostgreSQL 兼容性手册的 IMPORTFOREIGNSCHEMA 命令从外部服务器导入表定义。

 不支持对外表进行 CREATETRIGGER 操作。

 若需要 PanWeiDB 访问远端PostgreSQL 11 的分区表，需要设置GUC参数 sql_beta_feature 的值为 partition_fdw_on。

2.3 示例：使用 postgres_fdw连接到远程 panweidb 数据库，并访问外表

在本地服务器和远端服务器创建数据库用户之前，建议关闭远端数据库和本地数据库的强制修改密码功能。（参数默认关闭。）

2.3.1 在本地服务器创建用户

create user enmo with password 'XXX';

2.3.2 远端服务器修改配置文件pg_hba.conf 的配置参数，新增下列参数。

host all all 0.0.0.0/0 sha256

2.3.3 远端服务器修改配置文件postgresql.conf 中的 listen_addresses参数，值为本地服务器的 IP 地址。

2.3.4 远端服务器中创建用户

create user oracle with password ‘XXX';

2.3.5 远端服务器使用 oracle 用户创建测试表

create table test_fdw (id int,name varchar2(25));

insert into test_fdw values (1,'AA');

2.3.6 在本地服务器中，使用omm用户登录系统,加载postgres_fdw扩展。

create extension postgres_fdw;

grant usage on foreign data wrapper postgres_fdw to enmo;

2.3.7 切换enmo用户，创建外部服务器。

create server postgres_server foreign data wrapper postgres_fdw options (host '10.230.xx.xx', port 'xx', dbname 'xxxxx');

2.3.8 创建一个用户到外部服务器的映射

CREATE USER MAPPING FOR oracle SERVER postgres_server OPTIONS (user 'oracle', password 'Qhyd_2024');

2.3.9 创建外表

create foreign table temp_fdw_pg(id int,name varchar2(25)) server postgres_server options(schema_name 'oracle',table_name 'test_fdw');

2.3.10 查询并进行dml操作

3. dblink

3.1 功能描述

dblink 是一个可以在一个 PanWeiDB 数据库会话中连接到其它PanWeiDB数据库的工具

3.2 注意事项

因PanWeiDB 与PostgreSQL依赖冲突，因此无法连接至PostgreSQL数据库。

 不同的用户可以创建同名的 privatedblink，且用户只能访问自己创建的DBLINK。

 同一用户可以创建同名的 privatedblink 和 publicdblink，在访问时优先访问privatedblink。

 为支持不同用户创建同名 dblink，需禁用 GRANTFOREIGNSERVER，不再允许将 server 权限给予其它用户，以防一个用户能访问多个同名server，造成混乱。

 不支持访问远程 Oracle 包变量和常量。（注：数据也不支持）

 不支持将远程函数和存储过程创建为同义词方式使用。

 创建private dblink 或 public dblink 时，必须提前创建 jdbc_fdw，并具备DBLINK相关权限

3.3 示例

3.3.1 创建扩展(已有)

create extension dblink;

3.3.2 先执行 dblink_connect 保持连接

SELECT dblink_connect('enmo','hostaddr=10.230.xx port=xx dbname=xx user=oracle password=xx');

3.3.3 执行 BEGIN 命令

select dblink_exec('enmo','begin');

3.3.4 执行数据操作

SELECT dblink_exec('enmo', 'create table people(id int,info varchar(10))');

SELECT dblink_exec('enmo', 'insert into people values(1,''foo'')');

SELECT dblink_exec('enmo', 'insert into people values(2,''foo'')');

SELECT dblink_exec('enmo', 'update people set info=''bar'' where id=1');\

查看源库oracle

3.3.5 解除连接

SELECT dblink_disconnect('enmo');

4. DATABASE LINK

4.1 功能描述

当用户需要跨越本地数据库，访问远程数据库的数据时，可以通过 DATABASE LINK 像访问本地数据库一样访问远程数据库表中的数据。

 在Oracle 兼容模式下，PanWeiDB 支持使用`tablename@dblink_name`语法进行表的增删改查功能。

 在Oracle 兼容模式下，PanWeiDB 支持 postgres_fdw 类型的 dblink同义词（包含 PanWeiDB 到 PanWeiDB 和 PanWeiDB 到 Oracle 两种场景）。

4.2 支持的数据源

PanWeiDB 对DATABASELINK的兼容建立在外部数据封装器（ForeignData Wrapper，FDW）上，因此，在创建DATABASELINK 时需指定使用的封装器。

 要连接 PanWeiDB 或 PostgreSQL 数据源，使用 postgres_fdw。

postgres_fdw 连接串格式为 postgres_fdw(host 'remote_host',port 'reomte_port',dbname 'db_remote')。

 要连接Oracle数据源，使用 jdbc_fdw。

jdbc_fdw 连接串格式为jdbc_fdw(url'jdbc_url',jarfile 'jarfile')

4.3 示例： 使用 dblink 连接远端 panweidb 数据库并执行 DML 操作

4.3.1 源端创建public.tmp表

4.3.2 在本地数据库创建用户并授权。

grant usage on foreign data wrapper postgres_fdw to oracle;

grant all on all tables in schema XXX to oracle;

grant usage on schema XXX to oracle;

4.3.3 关闭远端数据库和本地数据库的强制修改密码功能（默认关闭）

alter system set password_force_alter=off;

4.3.4 在本地数据库，把当前会话里的会话用户标识和当前用户标识都设置为用户oracle

set session session authorization oracle password 'XXXX';

4.3.5 在本地数据库创建 DBLINK

CREATE DATABASE LINK dblink_27 CONNECT TO oracle IDENTIFIED BY 'XXX' USING postgres_fdw(host 'XXXX',port 'XXXX',dbname 'XXXX');

4.3.6 通过dblink进行查询和dml操作

4.4 示例： 使用public dblink 连接远端 oracle 数据库并执行 DML 操作

4.4.1 修改 postgresql.conf 参数，将 jdbc_fdw 配置到shared_preload_libraries 参数中

shared_preload_libraries = 'jdbc_fdw'

参数说明：此参数用于声明一个或者多个在服务器启动的时候预先装载的共享库，多个库名称之间用逗号分隔。

4.4.2 创建插件

create extension jdbc_fdw;

4.4.3 创建 DATABASELINK

（1）创建私有 private DATABASELINK

create database link oradb1 connect to XX identified by 'XX' using jdbc_fdw( url 'jdbc:oracle:thin:@//XX:XX/utf8', jarfile '/home/panweidb2/ojdbc7.jar' )

（2）创建公共 publicDATABASELINK。

create public database link oradb2 connect to fdw identified by 'XXXXXX' using jdbc_fdw( url 'jdbc:oracle:thin:@//10.230.xx.xx:port/dbname', jarfile '/intdata/panweidb/app/lib/postgresql/ojdbc7.jar' );

源端新建测试表：

本地查询：

源端查询：