1. 安装Oracle客户端工具
编译安装oracle_fdw之前，需要安装Oracle的客户端程序；步骤略
下载地址：http://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html
2. 安装oracle_fdw
下载地址：http://pgxn.org/dist/oracle_fdw/
[root@Postgres201 opt]# unzip oracle_fdw-2.0.0.zip
[root@Postgres201 opt]# cd oracle_fdw-2.0.0
#加载环境变量后执行 pg_config是否在对应PGHOME/bin目录下。编译后会在对应的目录下面
[root@Postgres201 oracle_fdw-2.0.0]# source /home/postgres/.bashrc
[root@Postgres201 oracle_fdw-2.0.0]# pg_config
[root@Postgres201 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 -O2 -fpic -I/u01/app/oracle/sdk/include -I/u01/app/oracle/oci/include -I/u01/app/oracle/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/opt/pgsql96/include/server -I/opt/pgsql96/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -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 -O2 -fpic -I/u01/app/oracle/sdk/include -I/u01/app/oracle/oci/include -I/u01/app/oracle/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/opt/pgsql96/include/server -I/opt/pgsql96/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -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 -O2 -fpic -I/u01/app/oracle/sdk/include -I/u01/app/oracle/oci/include -I/u01/app/oracle/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/opt/pgsql96/include/server -I/opt/pgsql96/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -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 -O2 -fpic -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/opt/pgsql96/lib -Wl,--as-needed -Wl,-rpath,'/opt/pgsql96/lib',--enable-new-dtags -L/u01/app/oracle -L/u01/app/oracle/bin -L/u01/app/oracle/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
/usr/bin/ld: cannot find -lclntsh
collect2: ld returned 1 exit status
make: *** [oracle_fdw.so] Error 1
FAQ：执行make若出现“/usr/bin/ld: cannot find -lclntsh”；原因是找不到库liblclntsh文件；
解决方案：
1. 检查环境变量，看ORACLE有关的环境变量是否设置正确
2. 是否文件名字后有oracle版本信息；需要改名字
该文件在oracle安装目录下；本例是需要改名字即可
[root@Postgres201 oracle]# ln -sv libclntsh.so.11.1 libclntsh.so
[root@Postgres201 oracle_fdw-2.0.0]# make
[root@Postgres201 oracle_fdw-2.0.0]# make install
/bin/mkdir -p '/opt/pgsql96/lib'
/bin/mkdir -p '/opt/pgsql96/share/extension'
/bin/mkdir -p '/opt/pgsql96/share/extension'
/bin/mkdir -p '/opt/pgsql96/share/doc/extension'
/usr/bin/install -c -m 755 oracle_fdw.so '/opt/pgsql96/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/opt/pgsql96/share/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql '/opt/pgsql96/share/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/opt/pgsql96/share/doc/extension/'
[root@Postgres201 oracle_fdw-2.0.0]# ll /opt/pgsql96/lib/oracle_fdw.so
-rwxr-xr-x. 1 root root 156608 May 29 23:15 /opt/pgsql96/lib/oracle_fdw.so
安装完成！
3. 创建oracle_fdw外部表
本例oracle服务器是在192.168.1.221上；oracle_fdw是通过oci接口访问Oracle了，所以需要配置$ORACLE_HOME/network/admin/tnsnames.ora。
[root@Postgres201 admin]# vi tnsnames.ora
ora221 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
3.1 创建oracle_fdw
lottu=# create extension oracle_fdw;
CREATE EXTENSION
查看以加载扩展模块
lottu=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+-----------------------------------------------------------
oracle_fdw | 1.1 | lottu | foreign data wrapper for Oracle access
pg_stat_statements | 1.4 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers
lottu=# select * from pg_foreign_data_wrapper ;
fdwname | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions
--------------+----------+------------+--------------+--------+------------
postgres_fdw | 10 | 41021 | 41022 | |
oracle_fdw | 16384 | 49212 | 49213 | |
(2 rows)
3.2 在本地库创建SERVER
#采用//IP|解析主机名/实例名
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//oracle221/orcl');
#采用解析tnsnames.ora文件获取服务名
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'ora221');
lottu=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'ora221');
CREATE SERVER
lottu=# GRANT USAGE ON FOREIGN SERVER oradb TO lottu;
GRANT
查看信息
lottu=# select * from pg_foreign_server where srvname = 'oradb';
srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions
---------+----------+--------+---------+------------+-----------------+-------------------
oradb | 16384 | 49216 | | | {lottu=U/lottu} | {dbserver=ora221}
lottu=# \des
List of foreign servers
Name | Owner | Foreign-data wrapper
-------+-------+----------------------
lottu | lottu | postgres_fdw
oradb | lottu | oracle_fdw
3.3 在本地库创建user mapping
lottu=# CREATE USER MAPPING FOR lottu SERVER oradb OPTIONS (user 'lottu', password 'li0924');
CREATE USER MAPPING
查看信息
lottu=# select * from pg_user_mappings where srvname = 'oradb';
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+---------+------------------------------
49218 | 49217 | oradb | 16384 | lottu | {user=lottu,password=li0924}
lottu=# \deu
List of user mappings
Server | User name
--------+-----------
lottu | lottu
oradb | lottu
3.4 创建foreign table
在oracle中有表oratab
SQL> conn lottu/li0924
Connected.
SQL> desc oratab
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
TEXT VARCHAR2(30)
创建外部表
lottu=# CREATE FOREIGN TABLE pgtab(id int OPTIONS(key 'true'), text varchar(30)) server oradb OPTIONS (schema 'LOTTU', table 'ORATAB');
查看信息
lottu=# select * from pg_foreign_table;
ftrelid | ftserver | ftoptions
---------+----------+------------------------------------------
41032 | 41027 | {schema_name=lottu,table_name=user_info}
49225 | 49217 | {schema=LOTTU,table=ORATAB}
(2 rows) lottu=# \det
List of foreign tables
Schema | Table | Server
--------+-----------+--------
lottu | pgtab | oradb
lottu | user_info | lottu
(2 rows)
注意点
1. CREATE FOREIGN TABLE中声明的列数据类型和其他性质必须要匹配实际的远程表。列名也必须匹配，
2. 原因是出现在OPTIONS (schema '×××', table '×××');里面的schema/table需要用大写标注
3. 在postgres9.3版本以后oracle_fdw支持对外部表的 Insert ,delete ,update ;增加表操作项 options(key 'true') (当值设置为 true|on|yes 表示不可以做增删改操作)