本文讲述如何在PostgreSQL中使用oracle_fdw访问Oracle上的数据。
1. 安装oracle_fdw
可以参照:oracle_fdw in github
编译安装oracle_fdw之前,需要安装Oracle的客户端程序,通常可以安装轻量级客户端包:oracle instant client,下载地址为:oracle instant client
安装oracle_fdw,可以到网站上: oracle_fdw in github](https://github.com/laurenz/oracle_fdw "")下载安装包,或直接使用git clone下载源代码:
cd <postgresql源码目录>/contrib
git clone https://github.com/laurenz/oracle_fdw.git
在编译安装前,需要设置Oracle的环境变量,如在.bash_profile中增加:
export ORACLE_HOME=/opt/oracle/products/11.2.0
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=${PATH}:${ORACLE_HOME}/bin/
然后进入到oracle_fdw源码目录进行编译安装:
make
make insttall
安装完后,需要重启数据库才能生效。
2. 创建oracle_fdw外部表
oracle_fdw是通过oci接口访问Oracle了,所以需要配置$ORACLE_HOME/network/admin/tnsnames.ora,内容如下:
oratest =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(sid = oratest)
(SERVER = DEDICATED)
)
)
上面我们配置了一个连接串DSN为“oratest”,后面我们会用到这个串。配置完后,使用tnsping测试一下:
oracle@osdba-work:~$ tnsping oratest
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 11-NOV-2014 22:28:06
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (sid = oratest) (SERVER = DEDICATED)))
OK (0 msec)
假设在Oracle数据库中有scott用户,密码为tiger,其中有表dept,下面我把在PostgreSQL建张外部表,可以访问到Oracle中的这张dept表上:
在psql中,使用超级用户:
CREATE EXTENSION oracle_fdw;CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver 'oratest');GRANTUSAGEONFOREIGN SERVER oradb TO scott;
注意上面命令中“dbserver 'oratest'”中的"oratest"就是前面tnsnames中设置的TNSNAMES。 上面的SQL中把访问Oracle外部服务的权限赋给了用户scott,现在使用scott用户登录:
CREATEUSER MAPPING FORcurrent_user SERVER oradb
OPTIONS (user'scott', password 'tiger');CREATEFOREIGNTABLE dept(deptno smallint options(key'true'), dname varchar(14), loc varchar(13))
SERVER oradb OPTIONS (schema'SCOTT', table'DEPT');
测试一下,在psql中查询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=> insertinto dept values(50, 'OSDBA', 'HANGZHOU');INSERT01
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;UPDATE1
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=> deletefrom dept where deptno=50;DELETE1
mydb=> select * from dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
(4 rows)