从PostgreSQL中访问Oracle数据库的利器：Oracle_fdw

1. 概述

Oracle_fdw是PG的一个外部数据接口插件，可以使PostgreSQL轻松跨库操作Oracle，Oracle_fdw的作用有以下两点：

• 可以使PG中的表和Oracle中表/视图作Join查询，类似dblink的功能。
• 此插件实现了外部表的更新接口，故通过Oracle_fdw在PG数据库中可以跨库增删改Oracle中的表
• 使用此插件可以方便的把数据在PostgreSQL与Oracle中进行相互导数据。

Oracle_fdw的源代码在github上：

• https://github.com/laurenz/oracle_fdw

在所有的第三方的FDW插件中，Oracle_fdw是一个质量比较高的插件，一直也有人在维护。

2. 配置Oracle环境

Oracle_fdw 的编译依赖系统中需要有pg_config和Oracle的环境，如果没有，就很难安装成功，需要安装一个。

2.1 安装Oracle客户端

为了方便我们安装Oracle的简版客户端instantclient，当然安装完全版本的Oracle 客户端程序也是可以的。

• 从官网下载 instantclient-basic-linux.x64-12.2.0.1.0.zip、 instantclient-sdk-linux.x64-12.2.0.1.0.zip、 instantclient-sqlplus-linux.x64-12.2.0.1.0.zip三个文件包，并放到/opt/oracle/目录下

mv instantclient-basic-linux.x64-12.2.0.1.0.zip instantclient-sdk-linux.x64-12.2.0.1.0.zip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip /opt/oracle/

• 解压三个文件包

unzip instantclient-basic-linux.x64-12.2.0.1.0.zip
unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip
unzip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip

• 解压后会生成instantclient_12_2 目录，将其更名为instantclient

mv instantclient_12_2 instantclient

3. 下载和编译安装

进入解压目录

[root@pg01 opt]# cd oracle_fdw-2.0.0/
[root@pg01 oracle_fdw-2.0.0]# ls
CHANGELOG  LICENSE   META.json                 oracle_fdw--1.1.sql  oracle_fdw.control  oracle_gis.c    README.oracle_fdw  TODO
expected   Makefile  oracle_fdw--1.0--1.1.sql  oracle_fdw.c         oracle_fdw.h        oracle_utils.c  sql

3.1 make

[root@pg01 oracle_fdw-2.0.0]# make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/sdk/include -I/oci/include -I/rdbms/public -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_fdw.o oracle_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/sdk/include -I/oci/include -I/rdbms/public -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_utils.o oracle_utils.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/sdk/include -I/oci/include -I/rdbms/public -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_gis.o oracle_gis.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/usr/pgsql-9.6/lib -Wl,--as-needed  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.6/lib',--enable-new-dtags  -L -L/bin -L/lib -lclntsh -L/usr/lib/oracle/12.2/client/lib -L/usr/lib/oracle/12.2/client64/lib -L/usr/lib/oracle/12.1/client/lib -L/usr/lib/oracle/12.1/client64/lib -L/usr/lib/oracle/11.2/client/lib -L/usr/lib/oracle/11.2/client64/lib -L/usr/lib/oracle/11.1/client/lib -L/usr/lib/oracle/11.1/client64/lib -L/usr/lib/oracle/10.2.0.5/client/lib -L/usr/lib/oracle/10.2.0.5/client64/lib -L/usr/lib/oracle/10.2.0.4/client/lib -L/usr/lib/oracle/10.2.0.4/client64/lib -L/usr/lib/oracle/10.2.0.3/client/lib -L/usr/lib/oracle/10.2.0.3/client64/lib

3.2 make install

[root@pg01 oracle_fdw-2.0.0]# make install
/usr/bin/mkdir -p '/usr/pgsql-9.6/lib'
/usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-9.6/doc/extension'
/usr/bin/install -c -m 755  oracle_fdw.so '/usr/pgsql-9.6/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/pgsql-9.6/share/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql  '/usr/pgsql-9.6/share/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/pgsql-9.6/doc/extension/'

4. 使用

安装完成之后，看动态库中oracle_fdw.so y有没有成功生成

4.1 解决使用依赖

ldd 这个动态库文件，会显示缺少哪一些依赖。要把这些依赖都解决才能使用

我的解决方法

4.2 试用

在PG数据库中安装Oracle_fdw插件：

create   extention  oracle_fdw

创建外部数据源服务：

CREATE SERVER server_pgsql_oracle_fdw FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.10.248:1521/orcl');
CREATE USER MAPPING FOR public SERVER server_pgsql_oracle_fdw OPTIONS (user 'kebyy2013', password 'kebyy2013');

创建外部表：

CREATE FOREIGN TABLE dept(deptno smallint options(key 'true'), dname varchar(14), loc varchar(13))
   SERVER oradb OPTIONS (schema 'SCOTT', table 'DEPT');

测试一下在PG中查询dept这张表：

osdba=# select * from dept;
 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

可以看出已查询到数据了。

oracle_fdw外部表也可以支持插入、更新、删除:

mydb=> insert into dept values(50, 'OSDBA', 'HANGZHOU');
INSERT 0 1
mydb=> select * from dept;
 deptno |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | OSDBA      | HANGZHOU
(5 rows)
mydb=> update dept set loc='HZ' where deptno=50;
UPDATE 1
mydb=> select * from dept;
 deptno |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | OSDBA      | HZ
(5 rows)
mydb=> delete from dept where deptno=50;
DELETE 1
mydb=> select * from dept;
 deptno |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

5. 附

5.1 oracle的tnsname配置方法

在oracle中通常是使用tnsnames提供的一个名字访问oracle，即通过一个名字映射到oracle数据库的IP、端口、服务名等等，
这个配置通常是配置在文件$ORACLE_HOME/network/admin/tnsnames.ora中，其中$ORACLE_HOME是Oracle中的一个环境变量，安装过oracle的人都知道这个环境变理，这个环境变量指定了Oracle软件的安装目录，当然tnsnames.ora也可以用环境变量TNS_NAME指定。我们在这个文件中配置如下内容：

oratest =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (sid = oratest)
      (SERVER = DEDICATED)
    )
  )

那边我们在PG中建外部数据源服务器的命令中就可以用这个“oratest”这个名称取代IP地址、端口和服务名：

CREATE SERVER server_pgsql_oracle_fdw FOREIGN DATA WRAPPER oracle_fdw
          OPTIONS (dbserver 'oratest');