在编译安装前，需要设置postgres的环境变量，如在.bash_profile中增加：

export ORACLE_HOME=/u01/app/oracle

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export PATH=${PATH}:${ORACLE_HOME}/bin/

--下载 ： http://pgxn.org/dist/oracle_fdw/

[root@sdserver40_222 contrib]# unzip oracle_fdw-1.5.0.zip

[root@sdserver40_222 contrib]# cd oracle_fdw-1.5.0

--查看pg_config是否在对应PGHOME/bin目录下。编译后会在对应的目录下面

[root@sdserver40_222 oracle_fdw-1.5.0]# which pg_config

/opt/pgsql/bin/pg_config

[root@sdserver40_222 oracle_fdw-1.5.0]# make

[root@sdserver40_222 oracle_fdw-1.5.0]# make install

[root@sdserver40_222 oracle_fdw-1.5.0]# cd /opt/pgsql/lib

[root@sdserver40_222 lib]# ll oracle_fdw.so

-rwxr-xr-x 1 root root 151893 Oct 18 14:11 oracle_fdw.so

编译成功会在PGHOME/lib目录下会生成 oracle_fdw.so。

创建oracle_fdw模块需要libclntsh.so.11.1加载库；在$ORACLE_HOME/lib目录下面；

[root@sdserver40_222 lib]# cd /u01/app/oracle/lib/

[root@sdserver40_222 lib]# ll libclntsh.so.11.1

-rwxr-xr-x 1 oracle oinstall 48725761 Jul 26 14:12 libclntsh.so.11.1

[root@sdserver40_222 lib]# cp libclntsh.so.11.1 /opt/pgsql/lib

[root@sdserver40_222 lib]# cd /opt/pgsql/lib

[root@sdserver40_222 lib]# ll libclntsh.so.11.1

-rwxr-xr-x 1 root root 48725761 Oct 18 14:50 libclntsh.so.11.1

在psql中，使用超级用户：

postgres=# create extension oracle_fdw;

CREATE EXTENSION

postgres=# \dx

List of installed extensions

Name | Version | Schema | Description

-------------+---------+------------+----------------------------------------

oracle_fdw | 1.1 | public | foreign data wrapper for Oracle access

pgstattuple | 1.3 | public | show tuple-level statistics

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

--本次环境测试是；oracle和postgres在同一台服务器上；若不在同一台服务器；postgres服务器需要安装oracle客户端；并配置tnsnames.ora

--下面的 ora229 是来源于$ORACLE_HOME/networks/admin/tnsnames里面的。

--当然也可以使用//oracle-ip/oracle_sid来替换ora229。

postgres=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'ora229');

CREATE SERVER

-- 将server oradb付给用户

postgres=# GRANT USAGE ON FOREIGN SERVER oradb TO lottu;

GRANT

--关联用户(oracle->postgres)

postgres=# CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'lottu', password 'li0924');

CREATE USER MAPPING

--在oracle服务器创建测试验证表oratab；操作如下：

SQL> select * from oratab;

no rows selected

SQL> insert into oratab select level,'lottu'||level from dual connect by level <=5;

5 rows created.

SQL> commit;

Commit complete.

--这里主要的是oracle跟postgres数据类型不一样时；需要修改下。

--在postgres9.3版本 oracle_fdw支持对外部表的 Insert ,delete ,update ;增加表操作项 options(key 'true') (当值设置为 true|on|yes 表示不可以做增删改操作) 默认值false

postgres=# CREATE FOREIGN TABLE lottu(id int options(key 'true'), name varchar(20)) SERVER oradb OPTIONS (schema 'LOTTU', table 'ORATAB');

CREATE FOREIGN TABLE

postgres=# select * from lottu;

id | name

----+--------

1 | lottu1

2 | lottu2

3 | lottu3

4 | lottu4

5 | lottu5

若出现下面这种问题；原因是出现在OPTIONS (schema 'LOTTU', table 'ORATAB');里面的schema/table需要用大写标注；

借用官方一句话就是“(Remember that table and schema name -- the latter is optional -- must normally be in uppercase.)”

postgres=# select * from oratab;

ERROR: Oracle table "lottu"."oratab" for foreign table "oratab" does not exist or does not allow read access

DETAIL: ORA-00942: table or view does not exist

HINT: Oracle table names are case sensitive (normally all uppercase).

在postgres9.3版本oracle_fdw支持对外部表的 Insert ,delete ,update；这意味着；oracle|postgres都可以对表进行dml操作。这对oracle迁移postgres将会很灵活。

oracle

SQL> insert into oratab values (1001,'li0924');

1 row created.

SQL> commit;

Commit complete.

postgres=# delete from lottu where id = 1;

DELETE 1

postgres=# select * from lottu;

id | name

------+--------

2 | lottu2

3 | lottu3

4 | lottu4

5 | lottu5

1001 | li0924

【总结】

1. 在postgres9.3版本oracle_fdw支持对外部表的 Insert ,delete ,update；建表添加options(key 'true')这意味着；

oracle|postgres都可以对表进行dml操作。这对oracle迁移postgres将会很灵活。

对postgres是否外部表的 Insert ,delete ,update。oracle_fdw有两个参数可以决定

1. Column options：options (true|on|yes, defaults to "false")

2. table options： readonly (true|on|yes, defaults to "false")

当确定postgres不支持外部表的 Insert ,delete ,update操作；建议使用readonly 'yes'；出现的错误提示更亲民些。

例如

postgres=# CREATE FOREIGN TABLE lottu01(id int options(key 'true'), name varchar(20)) SERVER oradb OPTIONS (schema 'LOTTU', table 'ORATAB', readonly 'yes');

CREATE FOREIGN TABLE

postgres=# delete from lottu01 where id = 2;

ERROR: foreign table "lottu01" does not allow deletes

2. 外部表支持逻辑备份pg_dump

[postgres@sdserver40_222 ~]$ pg_dump -F p -C -d postgres -f lottu.sql

[postgres@sdserver40_222 ~]$ grep "FOREIGN TABLE" lottu

grep: lottu: No such file or directory

[postgres@sdserver40_222 ~]$ grep "FOREIGN TABLE" lottu.sql

-- Name: lottu; Type: FOREIGN TABLE; Schema: public; Owner: postgres

CREATE FOREIGN TABLE lottu (

ALTER FOREIGN TABLE lottu ALTER COLUMN id OPTIONS (

ALTER FOREIGN TABLE lottu OWNER TO postgres;

-- Name: tab01; Type: FOREIGN TABLE; Schema: public; Owner: postgres

CREATE FOREIGN TABLE tab01 (

ALTER FOREIGN TABLE tab01 ALTER COLUMN id OPTIONS (

ALTER FOREIGN TABLE tab01 OWNER TO postgres;