新需求，老板要在pg数据库中外链到oracle，

环境

• 操作系统：Debian11
• postgresql: 13
• Oracle: 12

安装Oracle客户端

去Oracle官网下载Oracle客户端
https://www.oracle.com/database/technologies/instant-client/downloads.html
这里我们选择 Instant Client for Linux x86-64需要下载三个包
instantclient-basic-linux.x64-21.8.0.0.0dbru.zip
instantclient-sqlplus-linux.x64-21.8.0.0.0dbru.zip
instantclient-sdk-linux.x64-21.8.0.0.0dbru.zip
将其解压到同一个文件夹中

unzip instantclient-basic-linux.x64-21.8.0.0.0dbru.zip
unzip instantclient-sqlplus-linux.x64-21.8.0.0.0dbru.zip
unzip instantclient-sdk-linux.x64-21.8.0.0.0dbru.zip

设置环境变量ORACLE_HOME

export ORACLE_HOME==/home/nyy/instantclient_21_8

安装oracle_fdw插件

下载插件源码 https://github.com/laurenz/oracle_fdw编译

make
make install

make的时候出现找不到头文件的错误，试了好多方法都不行，最终把头文件都复制到插件源码目录下成功了

cp /home/nyy/instantclient_21_8/sdk/include/* ./

在pg数据库中执行安装插件命令

CREATE EXTENSION oracle_fdw;

此步骤中可能会报找不到动态库的错误

1. libclntsh.so

   CREATE EXTENSION oracle_fdw
   > 错误:  无法加载库 /usr/lib/postgresql/13/lib/oracle_fdw.so: libclntsh.so.21.1: 无法打开共享对象文件: 没有那个文件或目录

   libclntsh.so.21.1这个文件在Oracle客户端的源码中，所以需要将其包含到动态链接库中

   echo "/home/nyy/instantclient_21_8" >> /etc/ld.so.conf.d/oracle-x86_64.conf

   再执行

   ldconfig

2. libaio.so

   CREATE EXTENSION oracle_fdw
   > 错误:  无法加载库 /usr/lib/postgresql/13/lib/oracle_fdw.so: libaio.so.1: 无法打开共享对象文件: 没有那个文件或目录

   libaio.so.1这个文件没有，需要安装libaio-dev

   apt install libaio-dev

   解决完依赖的问题之后就能正常安装插件了

   CREATE EXTENSION oracle_fdw
   > OK

配置oracle_fdw插件

可以先试试能不能正常连接到Oracle

./sqlplus ntc/ntc@192.168.52.102:1521/ntc.192.168.52.102

1. 创建Oracle数据库映射

   CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.52.102:1521/ntc.192.168.52.102');

2. 授予用户访问权限 授予postgres用户访问oradb 服务的权限

   grant usage on foreign server oradb to postgres;

3. 创建用户映射 将postgres用户映射为oradb的用户

   CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'ntc', password 'ntc');

创建Oracle外部表

1. CREATE FOREIGN TABLE

DROP FOREIGN TABLE IF EXISTS "public"."o_m_kakuduke";
CREATE FOREIGN TABLE "public"."o_m_kakuduke" (
    "kakuduke" varchar(4) OPTIONS ("key" 'true') NOT NULL,
    "utiwake" varchar(10) OPTIONS ("key" 'true') NOT NULL,
    "setumei" varchar(30),
    "biko" varchar(200)
)
SERVER "oradb"
OPTIONS ("schema" 'NTC', "table" 'M_KAKUDUKE')
;

注意点：

• 外部表的字段名不需要与 Oracle 的表保持一致，但是字段顺序需要与 Oracle 的表保持一致
• 外部表的主键需要与 Oracle 的表保持一致
• OPTIONS 属性里的 schema 和 table 名称必须大写，不然 PostgreSQL 端操作外部表会报 ‘ORA-00942: table or view does not exist’。
• OPTIONS 属性里的 schema 和 table 必须用单引号
• 必须定义 oracle_fdw 可以转换的列

2. IMPORT FOREIGN SCHEMA

IMPORT FOREIGN SCHEMA "NTC" limit to (M_KAKUDUKE) from server oradb into public;

注意点：

• 这种方式不需要指定表结构，但是外部表名需要一致，也就是当前 PostgreSQL 的 SCHEMA 下不能存在同名表，否则创建失败。
• 从 PostgreSQL 9.5 开始，支持 IMPORT FOREIGN SCHEMA 为 Oracle 模式中的所有表批量导入表定义。
• IMPORT FOREIGN SCHEMA 将为在 ALL_TAB_COLUMNS 中找到的所有对象创建外部表。这包括表、视图和物化视图，但不包括同义词。
• Oracle SCHEMA 名称通常为大写。由于 PostgreSQL 在处理之前将名称转换为小写，因此您必须用双引号保护 SCHEMA 名称（例如"NTC"）。
• LIMIT TO 导入括号内包含的表，多个表以逗号分隔，EXCEPT 导入不包含(排除)括号内的表，多个表以逗号分隔

坑点

1. 字段对应不上

CREATE FOREIGN TABLE方式创建的外链表字段对应关系时按照顺序而非字段名，比如上面的CREATE改为：

DROP FOREIGN TABLE IF EXISTS "public"."a_m_kakuduke";
CREATE FOREIGN TABLE "public"."a_m_kakuduke" (
    "kakuduke" varchar(4) OPTIONS ("key" 'true') NOT NULL,
    "utiwake" varchar(10) OPTIONS ("key" 'true') NOT NULL,
    "biko" varchar(200),
    "setumei" varchar(30)
)
SERVER "oradb"
OPTIONS ("schema" 'NTC', "table" 'M_KAKUDUKE')
;

查询一下

select biko,setumei  from o_m_kakuduke limit 1;
select biko,setumei from a_m_kakuduke limit 1;

会出现下面两个不同的结果

结果1
biko    setumei
2       1
结果2
biko    setumei
1       2

所以创建外链表的时候一定要将字段与Oracle的一一对应，否则就会导致数据对应不上，如果数据类型对应不上则会直接报错。

2. 随机报错ORA-08177

解决方法

alter server oradb options (isolation_level 'read_committed');

以下是oracle_fdw插件作者的话

Unfortunately Oracle's implementation of SERIALIZABLE is rather bad and causes serialization errors (ORA-08177) in unexpected situations, like inserts into the table.
Using READ COMMITTED transactions works around this problem, but there is a risk of inconsistencies. If you want to use it, check your execution plans if the foreign scan could be executed more than once.

翻译过来就是：

不幸的是，Oracle数据库对于事务隔离级别SERIALIZABLE的实现相当糟糕，会在一些意外情况下出现序列化错误（ORA-08177），例如在插入表时。
使用READ COMMITTED事务可以解决这个问题，但存在不一致的风险。如果您想使用它，请检查您的执行计划是否可以多次执行外部扫描。

参考链接

• PostgreSQL插件--oracle_fdw