1. 安装oracle_fdw
在编译安装前，需要设置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。
2. 创建oracle_fdw外部表
创建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).
3.测试验证
在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 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;