oracle遷移postgres之-oracle_fdw

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

-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.

postgres=# delete from lottu where id = 1;

delete 1

id | name

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

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');

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;