本文共 2696 字，大约阅读时间需要 8 分钟。

oracle_fdw已经有较为正式的版本（oracle_fdw-1.1.0），分别试验了本地PG连接到local windows oracle和本地PG连接到remote linux oracle（10.0.0.234）场景，select/insert/update/delete以及各种join/limit/order by等语句都可以很好的支持。

1、Install Oracle FDW

测试场景：PostgreSQL 9.3.4, Oracle XE 11g, oracle_fdw-1.1.0, Windows 7, Linux CentOS 6.3

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

把folder lib & share 里的文件copy 到PostgreSQL的安装目录

DROP EXTENSION oracle_fdw CASCADE;

CREATE EXTENSION oracle_fdw;

SELECT oracle_diag();

-- grant all on foreign data wrapper oracle_fdw to postgres;

2、Create connection

包括：install extension, external server name, user mapping, foregin table name

  e.g.

  CREATE SERVER oradb_234 FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//10.0.0.234:1521/devdb');

  CREATE USER MAPPING FOR postgres SERVER oradb_234 OPTIONS (user 'test', password 'test');

  CREATE FOREIGN TABLE ORA_234_TEST01 (id int options (key 'true'), name varchar(20)) SERVER oradb_234 OPTIONS (table 'TEST01'); -- TEST01 need to be uppercase

  -- GRANT USAGE ON FOREIGN SERVER oradb_234 TO postgres;

  SELECT rolname FROM pg_user -- pg_shadow, pg_roles

  -- CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }

3、Test connection

-- Remote Oracle

-- describe test01;

-- cmd>tnsping //10.0.0.234:1521/devdb -- expect OK

-- cmd>sqlplus test/test@//10.0.0.234:1521/devdb-- expect OK

-- PostgreSQL

-- \dx -- expect EXTENSION can be found

-- \d -- expect FOREIGN TABLE can be found

select * from ORA_234_TEST01;

insert into ORA_234_TEST01 values (2, 'from_PG'); update ORA_234_TEST01 SET name = 'from_PG1' where id = 2; delete from ORA_234_TEST01 where id = 2;

4、Performance

性能方面当它需要copy大量数据时开销较大，其它情况则还好。以下是一些例子：

  select count(*) from ORA_234_TEST01; -- 184s, result: 971000   select * from ORA_234_TEST01 limit 1 offset 900000; -- 160s   select * from ORA_234_TEST01 limit 1; -- 0.1s   select * from ORA_234_TEST01 where id = 900001; -- 0.2s

5、Note

  PostgreSQL 9.1 开始支持Oracle FDW

  PostgreSQL 9.2 support for ANALYZE is available

  PostgreSQL 9.3 Support for INSERT, UPDATE and DELETE is available

  FOREIGN TABLE - OPTIONS: dbserver,user,password,table (required); schema,plan_costs,readonly (optional)

  Remember that table and schema name must be in uppercase

  update / delete: options (key 'true') is required

  其它：还有一些FDW如JDBC_FDW, ODBC_FDW理论上也可以连去oracle，不过作者维护度很低也没正式发布版，不建议使用。

6、Reference

http://blog.itpub.net/133735/viewspace-730107

http://pgxn.org/dist/oracle_fdw/ http://francs3.blog.163.com/blog/static/4057672720122354546425/ http://www.csdn.net/article/2014-06-04/2820079/2

https://github.com/atris/JDBC_FDW

http://blog.osdba.net/522.html

http://www.postgresql.org/docs/9.3/static/fdwhandler.html

http://blog.163.com/digoal@126/blog/static/163877040201181505331588/

转载地址：http://owili.baihongyu.com/