Postgresql使用oracle_fdw连接Oracle表

一、设置Orcale客户端

续上文已经安装了Oracle客户端
[root@pg01 oracle_fdw-1.5.0]# more  ~/.bashrc 
# .bashrc
export PGHOME=/usr/local/pg9.6
export  ORACLE_HOME=/usr/lib/oracle/11.2/client64
export  TNS_ADMIN=$ORACLE_HOME/network/admin
export  NLS_LANG='simplified chinese_china'.ZHS16GBK
export  LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib 
export  PATH=$ORACLE_HOME/bin:$PGHOME/bin:$PATH

二、安装Oracle_fdw扩展
[root@pg01]# source ~/.bashrc 

[root@pg01~]# unzip oracle_fdw-1.5.0.zip

[root@pg01 oracle_fdw-1.5.0]#   which pg_config
/usr/local/pg9.6/bin/pg_config
[root@pg01 oracle_fdw-1.5.0]# 
[root@pg01 oracle_fdw-1.5.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/usr/lib/oracle/11.2/client64/sdk/include -I/usr/lib/oracle/11.2/client64/oci/include -I/usr/lib/oracle/11.2/client64/rdbms/public -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/local/pg9.6/include/postgresql/server -I/usr/local/pg9.6/include/postgresql/internal -D_GNU_SOURCE   -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/usr/lib/oracle/11.2/client64/sdk/include -I/usr/lib/oracle/11.2/client64/oci/include -I/usr/lib/oracle/11.2/client64/rdbms/public -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/local/pg9.6/include/postgresql/server -I/usr/local/pg9.6/include/postgresql/internal -D_GNU_SOURCE   -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/usr/lib/oracle/11.2/client64/sdk/include -I/usr/lib/oracle/11.2/client64/oci/include -I/usr/lib/oracle/11.2/client64/rdbms/public -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/local/pg9.6/include/postgresql/server -I/usr/local/pg9.6/include/postgresql/internal -D_GNU_SOURCE   -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/usr/local/pg9.6/lib -Wl,--as-needed -Wl,-rpath,'/usr/local/pg9.6/lib',--enable-new-dtags  -L/usr/lib/oracle/11.2/client64 -L/usr/lib/oracle/11.2/client64/bin -L/usr/lib/oracle/11.2/client64/lib -lclntsh -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 
[root@pg01 oracle_fdw-1.5.0]# make install
/bin/mkdir -p '/usr/local/pg9.6/lib/postgresql'
/bin/mkdir -p '/usr/local/pg9.6/share/postgresql/extension'
/bin/mkdir -p '/usr/local/pg9.6/share/postgresql/extension'
/bin/mkdir -p '/usr/local/pg9.6/share/doc/postgresql/extension'
/usr/bin/install -c -m 755  oracle_fdw.so '/usr/local/pg9.6/lib/postgresql/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/local/pg9.6/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql  '/usr/local/pg9.6/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/local/pg9.6/share/doc/postgresql/extension/'

三、新建oracle_fdw扩展
[root@pg01 oracle_fdw-1.5.0] #su - postgres
[postgres@pg01 oracle_fdw-1.5.0]# psql 

postgres=# CREATE EXTENSION oracle_fdw;
ERROR:  could not load library "/usr/local/pg9.6/lib/postgresql/oracle_fdw.so": libclntsh.so.11.1: cannot open shared object file: No such file or directory
postgres=# 


[root@pg01 ~]# more /etc/ld.so.conf.d/oracle.conf
/usr/lib/oracle/11.2/client64/lib
[root@pg01 ~]# ldconfig 
[root@pg01 ~]# su - postgres


postgres=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION
postgres=# 


postgres=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
          OPTIONS (dbserver 'orcl');    //Orcl需要在tnsnames.ora 设置
CREATE SERVER
postgres=# CREATE USER MAPPING FOR postgres SERVER oradb
          OPTIONS (user 'zabbix', password 'zabbix'); 
CREATE USER MAPPING
  
postgres=# CREATE FOREIGN TABLE oratab (
          id        integer  options(key 'true') ,
          name     varchar(30)
       ) SERVER oradb OPTIONS (schema 'ZABBIX', table 'TEST');     //Oracle schema名及table名必须大写
CREATE FOREIGN TABLE
postgres=# select * from oratab;
 id |  name  
----+--------
  2 | df
  1 | 张国汉
  3 | 张国汉
(3 rows)


postgres=# delete from oratab where id =2;
DELETE 1
postgres=#

四、指定postgresql用户访问Oracle外部表测试
[postgres@pg01 ~]$ psql -d zabbix
psql (9.6.1)
Type "help" for help.


zabbix=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION
zabbix=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
zabbix-#           OPTIONS (dbserver 'orcl'); 
CREATE SERVER
zabbix=# 
zabbix=#  CREATE USER MAPPING FOR tank SERVER oradb
zabbix-#           OPTIONS (user 'zabbix', password 'zabbix');
CREATE USER MAPPING
zabbix=# 
zabbix=# GRANT USAGE ON FOREIGN SERVER oradb TO tank;
GRANT
zabbix=# CREATE FOREIGN TABLE oratab (
zabbix(#           id        integer  options(key 'true') ,
zabbix(#           name     varchar(30)
zabbix(#        ) SERVER oradb OPTIONS (schema 'ZABBIX', table 'TEST');
CREATE FOREIGN TABLE


zabbix=# \c - tank
You are now connected to database "zabbix" as user "tank".
zabbix=# select * from oratab;
 id |  name  
----+--------
  1 | 张国汉
  3 | 张国汉
(2 rows)


zabbix=# insert into oratab values(5,'sadf');
INSERT 0 1
zabbix=# select * from oratab;
 id |  name  
----+--------
  1 | 张国汉
  3 | 张国汉
  5 | sadf
(3 rows)


zabbix=# insert into oratab values(5,'saddf');
ERROR:  error executing query: OCIStmtExecute failed to execute remote query
DETAIL:  ORA-00001: unique constraint (ZABBIX.TE_PK) violated
zabbix=#