PostgreSQL插件之oracle_fdw

1.安装Oracle客户端

unzip instantclient-basic-linuxx64.zip -d ./
unzip instantclient-sqlplus-linuxx64.zip -d ./
unzip instantclient-sdk-linuxx64.zip -d ./
export ORACLE_HOME=/ora2pg/instantclient_21_9
export LD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$ORACLE_HOME:$PATH

2.下载oracle_fdw插件并安装

unzip oracle_fdw-2.0.0.zip
cd oracle_fdw-2.0.0
Make
Make install
--检查确认没有依赖未解决
ldd oracle_fdw.so

3.创建拓展

postgres=# create extension oracle_fdw ;
postgres=# \des
List of foreign servers
Name |  Owner   | Foreign-data wrapper
-------+----------+----------------------
oradb | postgres | oracle_fdw

4.创建外部数据源服务（创建Oracle数据库映射）

以xdap用户登录到test库为例：

[root@PgSlave ]# su - pgbi
[pgbi@PgSlave ~]$ psql -U postgres

postgres=# \c test xdap
You are now connected to database "test" as user "xdap".

语法：
CREATE SERVER <SERVER名称> FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbserver ‘<数据库地址>’,port ‘<数据库端口>’,dbname ‘<数据库名>’);
CREATE SERVER <SERVER名称> FOREIGN DATA WRAPPER oracle_fdw OPTIONS (host ‘<Oracle库内网地址>’, port ‘<Oracle库内网端口>’, dbname ‘<数据库名>’);

test=# CREATE SERVER oradb1 FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//133.96.13.54:1533/test');
CREATE SERVER

列出已经创建的foreign server：

test=# \des+
                                                      List of foreign servers
  Name  | Owner | Foreign-data wrapper | Access privileges | Type | Version |              FDW options               | Description 
--------+-------+----------------------+-------------------+------+---------+----------------------------------------+-------------
 oradb1 | xdap  | oracle_fdw           | xdap=U/xdap       |      |         | (dbserver '//133.96.13.54:1533/test') | 

5.授予用户访问权限

授予xdap用户访问foreign server--oradb1：
test=# grant usage on foreign server oradb1 to xdap;
GRANT

6.创建用户映射

此步一定要进入上一步赋权的用户xdap进行操作，否则创建外部表失败；
语法：
CREATE USER MAPPING FOR <数据库账号> SERVER <映射名> OPTIONS (user ‘<Oracle数据库用户名>’, password ‘<Oracle数据库用户密码>’);

test=# CREATE USER MAPPING FOR xdap SERVER oradb1 OPTIONS (user 'xdap', password '45zky3j34cZxnWV_r_8m');
CREATE USER MAPPING

7.创建Oracle外部表

语法：
CREATE FOREIGN TABLE <创建Vastbase表名>(<Oracle的表结构信息>) SERVER <映射名> OPTIONS (table ‘<Oracle的表名称>’, schema ‘<Oracle表的模式名称>’ prefetch, ‘<两个数据库的表之间一次性传输的行数>’);

其中prefetch参数代表每次fetch行数，范围在0到10240之间；
创建外部表时候，Oracle表名称和模式名称一定大写，否则查询不到数据；
外部表的结构需要和Oracle中的映射表结构保持一致；

test=# CREATE  FOREIGN TABLE "sc_role_user4" (
 ROLE_ID                         character(60)  NOT NULL,
 LOGIN_ID                       character(60)  NOT NULL,
 UPDATE_USER                character(60),
 UPDATE_DATE                DATE) 
 SERVER oradb1 OPTIONS (schema 'XDAP',table 'SC_ROLE_USER');

参数说明

• key 是否设置对应的列为主键，取值为true或false，默认值为false。如果要执行UPDATE和DELETE操作，必须将所有主键列设置为true。
• table 表名，大写，必填参数。
• schema 一般是Oracle用户名，保持大写，用来访问不属于当前连接用户的表。
• prefetch 外表扫描时，PostgreSQL和Oracle数据表之间一次性传输的行数，取值范围是0~1024，默认值是200，0代表取消prefetch功能。

8.访问外部表

test=# select * from sc_role_user4;

test=# \det+
                                 List of foreign tables
 Schema |     Table     | Server |               FDW options               | Description 
--------+---------------+--------+-----------------------------------------+-------------
 xdap   | sc_role_user4 | oradb1 | (schema 'XDAP', "table" 'SC_ROLE_USER') | 

查看表结构：
test=# \d+ sc_role_user4;

9.修改外部表数据

想要对remote table进行数据的修改操作，必须满足外部访问的表是有主键的情况，默认的列选项key设置的是false；
需要手工设置对应的列名的key选项为true；

test=# CREATE  FOREIGN TABLE "sc_role_user4" (
 ROLE_ID                         character(60)  OPTIONS (key 'true')  NOT NULL,
 LOGIN_ID                       character(60)  NOT NULL,
 UPDATE_USER                character(60),
 UPDATE_DATE                DATE) 
 SERVER oradb1 OPTIONS (schema 'XDAP',table 'SC_ROLE_USER');

10.删除创建的对象：

drop foreign table oratab;
drop user mapping for test server oracle_91;
drop server oracle_91; #创建用户删除
DROP EXTENSION oracle_fdw; #创建用户删除