新需求，老板要在pg数据库中外链到oracle，
环境
- 操作系统：Debian11
- postgresql: 13
- Oracle: 12
1.去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
2.安装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;
此步骤中可能会报找不到动态库的错误
- libclntsh.so
CREATE EXTENSION oracle_fdw
> 错误: 无法加载库 "/usr/lib/postgresql/13/lib/oracle_fdw.so": libclntsh.so.21.1: 无法打开共享对象文件: 没有那个文件或目录
libclntsh.so.21.1这个文件在Oracle客户端的源码中，所以需要将其包含到动态链接库中
vi /etc/ld.so.conf.d/oracle-x86_64.conf添加以下内容
/home/nyy/instantclient_21_8
再执行
ldconfig
- libaio.so
CREATE EXTENSION oracle_fdw
> 错误: 无法加载库 "/usr/lib/postgresql/13/lib/oracle_fdw.so": libaio.so.1: 无法打开共享对象文件: 没有那个文件或目录
libaio.so.1这个文件没有，需要安装
apt install libaio-dev
解决完依赖的问题之后就能正常安装插件了
CREATE EXTENSION oracle_fdw
> OK
3.pg外链到Oracle
- 创建Oracle数据库映射
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.52.102:1521/ntc.192.168.52.102');
- 授予用户访问权限
授予postgres用户访问oradb 服务的权限
grant usage on foreign server oradb to postgres;
- 创建用户映射
将postgres用户映射为oradb的用户
CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'ntc', password 'ntc');
- 创建Oracle外部表
CREATE FOREIGN TABLE o_m_kensakijyu(
KANRIBUSYO_CD character varying(10) OPTIONS (key 'true') NOT NULL ,
KESAKIJYU_CD character varying(3) OPTIONS (key 'true') NOT NULL ,
KAKUDUKE character varying(4) OPTIONS (key 'true') NOT NULL ,
UTIWAKE character varying(10) OPTIONS (key 'true') NOT NULL ,
KETENSU numeric(6,0) DEFAULT 0,
BABUN numeric(6,0) DEFAULT 0,
YUTEIKESA character varying(60),
KESAKIJYU character varying(30),
TUKA character varying(200),
NYUKO_TYO character varying(200),
KETEN_SU character varying(200),
JYUDAI_KETEN character varying(200),
SOKUDO character varying(200),
TOUKOU character varying(200),
BABUN_KEISAN character varying(200),
HABA_HIRO character varying(200),
MITUDO character varying(200),
BIKO character varying(200),
SEISEI_BI DATE,
SEISEI_PROGRAM_ID character varying(30),
SEISEI_SHA_USER_ID character varying(16),
KOSHIN_BI DATE,
KOSHIN_PROGRAM_ID character varying(30),
KOSHIN_SHA_USER_ID character varying(16)
) SERVER oradb OPTIONS (schema 'NTC', table 'M_KENSAKIJYU');
