在PostgreSQL中使用oracle_fdw访问Oracle

Posted on 2014-11-11 23:13:15 by osdba

本文讲述如何在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');

GRANT USAGE ON FOREIGN SERVER oradb TO scott;

注意上面命令中“dbserver 'oratest'”中的"oratest"就是前面tnsnames中设置的TNSNAMES。 上面的SQL中把访问Oracle外部服务的权限赋给了用户scott,现在使用scott用户登录:

CREATE USER MAPPING FOR current_user SERVER oradb
          OPTIONS (user 'scott', password 'tiger');

CREATE FOREIGN TABLE 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=> 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)