PostgrestSQL-安装oracle_fdw插件

1. 概述

PostgreSQL 中的 FDW（Foreign-Data Wrapper）外部数据封装器有使用在以下场景：

1. 同构或异构数据的迁移，如需要读取远程数据库中的postgres、mysql、oracle 数据库中的数据。
2. 通过指定文件的存放路径，直接读取数据库外部数据，进行数据分析。

FDW支持的外部数据源有：

• RDBMS FDW：mysql_fdw、oracle_fdw、sqlite_fdw、postgres_fdw、odbc_fdw
• NoSQL FDW：couchdb_fdw、redis_fdw、mongo_fdw、rethinkdb_fdw、wdb_fdw
• Format FDW：json_fdw、file_fdw、multicdr_fdw

FDW的工作过程：

1. 分析器为输入的SQL创建一颗查询树。
2. 计划器（或执行器）连接到远程服务器。
3. 如果启用了use remote estimate选项（默认关闭），则划器将执行EXPLAIN命令以估计每条计划路径的代价。
4. 计划器按照计划树创建出纯文本SQL语句，在内部称该过程为逆解析（deparesing）。
5. 执行器将纯文本SQL语句发送到远程服务器并接收结果。

由于目前企业都正在从国外数据库逐渐迁移到国内或开源数据库，在用 Oracle 的还是比较多，下面主要是演示的是 oracle_fdw 从插件安装到使用并在安装过程中遇到的问题，希望对大家在安装与配置 oracle_fdw 插件进行访问远程 Oracle 数据库有所帮助。

2. 相关插件下载地址

oracle_fdw 插件的下载地址为：
pgxn.org-oracle_fdw 或者：github-oracle_fdw

Oracle Instant Client 的下载地址：
instant-client

3. 实验环境

前提已安装好 postgresql ，并服务能正常运行。如未安装 postgresql 数据库可参考以下文章：
【PostgreSQL源码方式安装】

4. 安装依赖包

ldd 包作用：可用来查询 oracle_fdw.so 的运行时所需要依赖包是否都有配置正确。由于本机可连接互联网，则可直接使用 yum 安装，如果主机是在内网，则需要先配置 yum 源，并挂载系统ISO文件才能安装成功。

[root@pg01 ~]# yum install -y ldd

5. 相关安装包

• instantclient-basic-linux.x64-19.20.0.0.0dbru.zip
• instantclient-sdk-linux.x64-19.20.0.0.0dbru.zip
• instantclient-sqlplus-linux.x64-19.20.0.0.0dbru.zip
• oracle_fdw-2.7.0.zip

6. 解压安装包

将文件上传到 /soft 目录下，并进入此目录进行解压 instantclient 相关文件。

[postgres@pg01 ~]$ cd /soft/
[postgres@pg01 soft]$ ll
-rw-r--r--. 1 root     root     75130721 10月 16 19:11 instantclient-basic-linux.x64-19.20.0.0.0dbru.zip
-rw-r--r--. 1 root     root       934887 10月 16 19:11 instantclient-sdk-linux.x64-19.20.0.0.0dbru.zip
-rw-r--r--. 1 root     root       911671 10月 16 19:11 instantclient-sqlplus-linux.x64-19.20.0.0.0dbru.zip
-rw-r--r--. 1 postgres postgres   162540 10月 16 16:43 oracle_fdw-2.7.0.zip

[postgres@pg01 soft]$ unzip instantclient-basic-linux.x64-19.20.0.0.0dbru.zip
[postgres@pg01 soft]$ unzip instantclient-sdk-linux.x64-19.20.0.0.0dbru.zip
[postgres@pg01 soft]$ unzip instantclient-sqlplus-linux.x64-19.20.0.0.0dbru.zip

7. 配置 instantclient 环境变量

[postgres@pg01 lib]$ vim ~/.bash_profile
# 增加以下内容
export ORACLE_HOME=/soft/instantclient_19_20
export OCI_LIB_DIR=$ORACLE_HOME
export OCI_INC_DIR=$ORACLE_HOME/sdk/include
export LD_LIBRARY_PATH=$PGHOME/lib:$ORACLE_HOME:$LD_LIBRARY_PATH
[postgres@pg01 lib]$ source ~/.bash_profile

环境变量配置完成后，需要使用 source ~/.bash_profile 来刷新环境变量才会生效。

8. 安装 oracle_fdw

oracle_fdw 使用以下步骤安装：

1. 解压 oracle_fdw 包
2. 进入到 oracle_fdw 解压后的目录，执行 make
3. 编译正常后，再执行 make install

[postgres@pg01 soft]$ unzip oracle_fdw-2.7.0.zip
[postgres@pg01 soft]$ cd oracle_fdw-2.7.0
[postgres@pg01 oracle_fdw-2.7.0]$ make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -I"/soft/instantclient_11_2/sdk/include" -I"/soft/instantclient_11_2/oci/include" -I"/soft/instantclient_11_2/rdbms/public" -I"/soft/instantclient_11_2/"  -I. -I./ -I/usr/postgres/16.1/include/server -I/usr/postgres/16.1/include/internal  -D_GNU_SOURCE   -c -o oracle_utils.o oracle_utils.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -I"/soft/instantclient_11_2/sdk/include" -I"/soft/instantclient_11_2/oci/include" -I"/soft/instantclient_11_2/rdbms/public" -I"/soft/instantclient_11_2/"  -I. -I./ -I/usr/postgres/16.1/include/server -I/usr/postgres/16.1/include/internal  -D_GNU_SOURCE   -c -o oracle_gis.o oracle_gis.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/usr/postgres/16.1/lib    -Wl,--as-needed -Wl,-rpath,'/usr/postgres/16.1/lib',--enable-new-dtags  -fvisibility=hidden -L"/soft/instantclient_11_2/" -L"/soft/instantclient_11_2/bin" -L"/soft/instantclient_11_2/lib" -L"/soft/instantclient_11_2/lib/amd64"  -lclntsh
[postgres@pg01 oracle_fdw-2.7.0]$ make install
/bin/mkdir -p '/usr/postgres/16.1/lib'
/bin/mkdir -p '/usr/postgres/16.1/share/extension'
/bin/mkdir -p '/usr/postgres/16.1/share/extension'
/bin/mkdir -p '/usr/postgres/16.1/share/doc/extension'
/bin/install -c -m 755  oracle_fdw.so '/usr/postgres/16.1/lib/oracle_fdw.so'
/bin/install -c -m 644 .//oracle_fdw.control '/usr/postgres/16.1/share/extension/'
/bin/install -c -m 644 .//oracle_fdw--1.2.sql .//oracle_fdw--1.0--1.1.sql .//oracle_fdw--1.1--1.2.sql  '/usr/postgres/16.1/share/extension/'
/bin/install -c -m 644 .//README.oracle_fdw '/usr/postgres/16.1/share/doc/extension/'
[postgres@pg01 oracle_fdw-2.7.0]$

安装完成后检查安装目录下的 lib 目录下是否有 oracle_fdw.so 文件。

再使用 ldd 命令来检查 oracle_fdw.so 的引用是否正确配置：

[postgres@pg01 soft]$ ldd /usr/postgres/16.1/lib/oracle_fdw.so
        linux-vdso.so.1 =>  (0x00007ffd133b3000)
        libclntsh.so.11.1 => /soft/instantclient_19_20/libclntsh.so.11.1 (0x00007ffbc3e4f000)
        libc.so.6 => /lib64/libc.so.6 (0x00007ffbc3a81000)
        libnnz19.so => /soft/instantclient_19_20/libnnz19.so (0x00007ffbc3409000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007ffbc3205000)
        libm.so.6 => /lib64/libm.so.6 (0x00007ffbc2f03000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007ffbc2ce7000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007ffbc2acd000)
        librt.so.1 => /lib64/librt.so.1 (0x00007ffbc28c5000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007ffbc26c3000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007ffbc24a9000)
        /lib64/ld-linux-x86-64.so.2 (0x00007ffbc821a000)
        libclntshcore.so.19.1 => /soft/instantclient_19_20/libclntshcore.so.19.1 (0x00007ffbc1f05000)
[postgres@pg01 soft]$

9. 外部表创建

安装完 oracle_fdw 插件后，现在可以登录 postgres 用户，进入 psql 命令行窗口创建外部服务器的数据封装器，创建映射，创建外部表。

create server oradb foreign data wrapper oracle_fdw options(dbserver '//192.168.80.111:1521/orcl');

create user mapping for postgres server oradb options(user 'test',password 'test');

create foreign table t_test(age int) server oradb options(table 'T_TEST_TABLE');

• 创建外部Oracle数据库服务器常用参数说明如下：

  参数	说明	是否必填
  dbserver	Oracle数据库的连接串，包含IP地址、端口号、数据库名。	是
  nls_lang	如值为：AMERICAN_AMERICA.AL32UTF8，当没有设置时， oracle_fdw 会自动识别	否
  isolation_level	事务隔离级别，默认为SERIALIZABLE。可选的参数类型如下：（serializable：序列化；read_committed：读已提交；read_only：只读；）	默认值为：serializable
  nchar	是否在Oracle端开启字符转换。	默认值为：off

• mapping 参数说明如下：

  参数	说明	是否必填
  user	远程数据库用户名	是
  password	远程数据库密码	是

• 创建外部表常用参数说明如下：

  参数	说明	是否必填
  table	表名 或 查询语句，如设置查询语句则不需要设置 schema。通常用大写	是
  dblink	如有用 Oracle 的dblink，通常用大写	否
  schema	Oracle 用户名，通常用大写	否
  max_long		默认值为：32767
  readonly	当设置成 yes/on/true 时，表不能INSERT, UPDATE 或DELETE	默认值为：false

• 类型映射：

  Oracle类型	可能的PostgreSQL类型
  CHAR	char, varchar, text
  NCHAR	char, varchar, text
  VARCHAR	char, varchar, text
  VARCHAR2	char, varchar, text, json
  NVARCHAR2	char, varchar, text
  CLOB	char, varchar, text, json
  LONG	char, varchar, text
  RAW	uuid, bytea
  BLOB	bytea
  BEILE	bytea (read-only)
  LONG RAW	bytea
  NUMBER	numeric, float4, float8, char, varchar, text
  NUMBER(n,m) with m<=0	numeric, float4, float8, int2, int4, int8, boolean, char, varchar, text
  FLOAT	numeric, float4, float8, char, varchar, text
  BINARY_FLOAT	numeric, float4, float8, char, varchar, text
  BINARY_DOUBLE	numeric, float4, float8, char, varchar, text
  DATE	date, timestamp, timestamptz, char, varchar, text
  TIMESTAMP	date, timestamp, timestamptz, char, varchar, text
  TIMESTAMP WITH TIME ZONE	date, timestamp, timestamptz, char, varchar, text
  TIMESTAMP WITH LOCAL TIME ZONE	date, timestamp, timestamptz, char, varchar, text
  INTERVAL YEAR TO MONTH	interval, char, varchar, text
  INTERVAL DAY TO SECOND	interval, char, varchar, text
  XMLTYPE	xml, char, varchar, text
  MDSYS.SDO_GEOMETRY	geometry (PostGIS)

更多详细的参数配置可参考：README.oracle_fdw 文件。

[postgres@pg01 instantclient_19_20]$ pg_ctl restart
[postgres@pg01 instantclient_19_20]$ psql
psql (16.1)
Type "help" for help.

postgres=# create extension oracle_fdw;
CREATE EXTENSION

postgres=# \des
         List of foreign servers
 Name  |  Owner   | Foreign-data wrapper
-------+----------+----------------------
 oradb | postgres | oracle_fdw
(1 row)

postgres=# \d
             List of relations
 Schema |  Name  |     Type      |  Owner
--------+--------+---------------+----------
 public | t_test   | foreign table | postgres
(1 rows)

至此，创建外部表成功，在 postgresql 中查看已经成功创建的外部表，并能像普通表一样查询外部表，可使用元命令 \des 和 \det 查看数据库中有哪些外部表和外部服务器。

10. 安装过程中遇到的问题与解决办法

postgres=# create extension oracle_fdw;
ERROR:  could not load library "/usr/postgres/16.1/lib/oracle_fdw.so": libclntsh.so.11.1: 无法打开共享对象文件: 没有那个文件或目录
postgres=# \q
[postgres@pg01 ~]$ ldd /usr/postgres/16.1/lib/oracle_fdw.so
        linux-vdso.so.1 =>  (0x00007ffd834f7000)
        libclntsh.so.11.1 => /soft/instantclient_11_2/libclntsh.so.11.1 (0x00007f36e780f000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f36e7441000)
        libnnz11.so => /soft/instantclient_11_2/libnnz11.so (0x00007f36e7074000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f36e6e70000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f36e6b6e000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f36e6952000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f36e6738000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f36e6536000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f36ea3a4000)
[postgres@pg01 ~]$ 

原因与解决办法：由于安装插件时，数据库还正在运行，没有重启数据库，导致插件安装安成后，检查相关运行依赖也没问题，但就是创建外部表的时候报错，经排查后发现，安装完插件后没有重启数据库，所以动态链接库没有生效。重启数据库之后问题得已解决。