在 PostgreSQL 企業版 EnterpriseDB 中，所提供的 Oracle 相容性，可以用 DBLink 與 Oracle 資料庫交換資料。而在原生的 PostgreSQL 上，則可以透過 Foreign Data Wrapper 去存取位於 Oracle 資料庫上的的外部資料。Foreign Data Wrapper 是一種 PostgreSQL Extension，因此安裝 Foreign Data Wrapper 的方式，和安裝一般社群取得的 PostgreSQL Extension 步驟是差不多的。
在與 Oracle 交換資料的需求上，對於企業版 EnterpriseDB 使用者來說，一般還是建議使用 Foreign Data Wrapper，盡量擺脫 DBLink，以便降低對於 Oracle 資料庫功能的依賴，也讓企業在未來在社群版 PostgreSQL 及企業版 EnterpriseDB 之間能夠擁有更好的選擇彈性。
以下將由 CentOS 上的企業版 EnterpriseDB 進行示範。
環境：EDB 9.5 on CentOS 6 x64
PostgreSQL 安裝位置： /opt/PostgresPlus/9.5AS/
Oracle 環境：Oracle-XE 11.2 on CentOS 6 x64
對於 EnterpriseDB 發布的社群版 PostgreSQL，或是由社群維護的 YUM 佈署的 PostgreSQL（更甚至是其他眾多的 PostgreSQL 發行版 …，在此不贅述），安裝方式都是大同小異的。
編譯 oracle_fdw 環境需求：依據官方網站所提，oracle_fdw 需要 Oracle Client。不過在此只要使用 Oracle Instant Client 即可。（詳情參見 README - Installation Requirement 段落）
依文件所建議，可以安裝 Oracle Instant Client 10.0.2.5，比較能夠跨資料庫版本。不過在此我仍然以安裝 Oracle Instant Client 11.2.0.2.0，直接對照我所演練的 Oracle 版本。
1. 安裝 Oracle Instant Client 11.2.0.2.0：請至 Oracle 官網下載 rpm 安裝
取得 rpm 安裝檔：
oracle-instantclient11.2-basic-11.2.0.2.0.x86_64.rpm
oracle-instantclient11.2-devel-11.2.0.2.0.x86_64.rpm |
安裝：
[root@edb95 ~]# rpm -ivh oracle-instantclient11.2-basic-11.2.0.2.0.x86_64.rpm [root@edb95 ~]# rpm -ivh oracle-instantclient11.2-devel-11.2.0.2.0.x86_64.rpm |
2. 在系統帳戶 enterprisedb（ PostgreSQL process owner；若是社群版本，則為 postgres ）中設定環境變數 LD_LIBRARY_PATH：（使 PostgreSQL 找到 libnnz11.so 等這些 Library）
使用 Oracle Instant Client 壓縮檔：
[enterprisedb@edb95 ~]$ export LD_LIBRARY_PATH="/path/to/OCI/location/lib/":$LD_LIBRARY_PATH |
使用 Oracle Instant Client 的 rpm 安裝檔：
[enterprisedb@edb95 ~]$ export LD_LIBRARY_PATH="/usr/lib/oracle/11.2/client64/lib/":$LD_LIBRARY_PATH |
Note：也可以使這個設定永久生效
在 /etc/ld.so.conf 中將 Oracle lib 所在目錄路徑設定進去（下面用 cat 顯示結果）：
[root@edb95 ~]# cat /etc/ld.so.conf include ld.so.conf.d/*.conf /usr/lib/oracle/11.2/client64/lib/ [root@edb95 ~]# ldconfig
做出 soft link，以便使 PostgreSQL 在之後認得 libocci.so 以及 libclntsh.so
[root@edb95 ~]# ln -s /usr/lib/oracle/11.2/client64/lib/libocci.so.11.1 /usr/lib/oracle/11.2/client64/lib/libocci.so [root@edb95 ~]# ln -s /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 /usr/lib/oracle/11.2/client64/lib/libclntsh.so
3. 取得 oracle_fdw 並編譯
為了佔據版面（？），以下將編譯過程「全部」貼上：其實只要執行 make 就好了...
[enterprisedb@edb95 oracle_fdw-ORACLE_FDW_1_4_0]$ export PATH=$PATH:/opt/PostgresPlus/9.5AS/bin/ [enterprisedb@edb95 oracle_fdw-ORACLE_FDW_1_4_0]$ make -j2 gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fpic -I/u01/app/oracle/product/11.2.0/xe//sdk/include -I/u01/app/oracle/product/11.2.0/xe//oci/include -I/u01/app/oracle/product/11.2.0/xe//rdbms/public -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/opt/PostgresPlus/9.5AS/include/server -I/opt/PostgresPlus/9.5AS/include/internal -D_GNU_SOURCE -I/opt/local/20150616/181474c6-228d-4d58-b663-17981352ebce/include/libxml2 -I/opt/local/20150616/181474c6-228d-4d58-b663-17981352ebce/include -I/opt/local/20150616/181474c6-228d-4d58-b663-17981352ebce/include -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -c -o oracle_fdw.o oracle_fdw.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fpic -I/u01/app/oracle/product/11.2.0/xe//sdk/include -I/u01/app/oracle/product/11.2.0/xe//oci/include -I/u01/app/oracle/product/11.2.0/xe//rdbms/public -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/opt/PostgresPlus/9.5AS/include/server -I/opt/PostgresPlus/9.5AS/include/internal -D_GNU_SOURCE -I/opt/local/20150616/181474c6-228d-4d58-b663-17981352ebce/include/libxml2 -I/opt/local/20150616/181474c6-228d-4d58-b663-17981352ebce/include -I/opt/local/20150616/181474c6-228d-4d58-b663-17981352ebce/include -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -c -o oracle_utils.o oracle_utils.c oracle_fdw.c: In function 「oracleRegisterCallback」: oracle_fdw.c:5054: 警告：傳遞參數 1 (屬於 「RegisterXactCallback」)時在不相容的指標類型間轉換 /opt/PostgresPlus/9.5AS/include/server/access/xact.h:354: 附註：expected 「XactCallback」 but argument is of type 「void (*)(enum XactEvent, void *)」 oracle_fdw.c:5056: 警告：傳遞參數 1 (屬於 「RegisterSubXactCallback」)時在不相容的指標類型間轉換 /opt/PostgresPlus/9.5AS/include/server/access/xact.h:358: 附註：expected 「SubXactCallback」 but argument is of type 「void (*)(enum SubXactEvent, SubTransactionId, SubTransactionId, void *)」 oracle_fdw.c: In function 「oracleUnregisterCallback」: oracle_fdw.c:5067: 警告：傳遞參數 1 (屬於 「UnregisterXactCallback」)時在不相容的指標類型間轉換 /opt/PostgresPlus/9.5AS/include/server/access/xact.h:356: 附註：expected 「XactCallback」 but argument is of type 「void (*)(enum XactEvent, void *)」 oracle_fdw.c:5069: 警告：傳遞參數 1 (屬於 「UnregisterSubXactCallback」)時在不相容的指標類型間轉換 /opt/PostgresPlus/9.5AS/include/server/access/xact.h:359: 附註：expected 「SubXactCallback」 but argument is of type 「void (*)(enum SubXactEvent, SubTransactionId, SubTransactionId, void *)」 gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fpic -I/u01/app/oracle/product/11.2.0/xe//sdk/include -I/u01/app/oracle/product/11.2.0/xe//oci/include -I/u01/app/oracle/product/11.2.0/xe//rdbms/public -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/opt/PostgresPlus/9.5AS/include/server -I/opt/PostgresPlus/9.5AS/include/internal -D_GNU_SOURCE -I/opt/local/20150616/181474c6-228d-4d58-b663-17981352ebce/include/libxml2 -I/opt/local/20150616/181474c6-228d-4d58-b663-17981352ebce/include -I/opt/local/20150616/181474c6-228d-4d58-b663-17981352ebce/include -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -c -o oracle_gis.o oracle_gis.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fpic -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/opt/PostgresPlus/9.5AS/lib -L/opt/local/20150616/181474c6-228d-4d58-b663-17981352ebce/lib -L/opt/local/20150616/181474c6-228d-4d58-b663-17981352ebce/lib -L/opt/local/Current/lib -L/opt/local/20150616/181474c6-228d-4d58-b663-17981352ebce/lib -Wl,--as-needed -Wl,-rpath,'/opt/PostgresPlus/9.5AS/lib',--enable-new-dtags -L/u01/app/oracle/product/11.2.0/xe/ -L/u01/app/oracle/product/11.2.0/xe//bin -L/u01/app/oracle/product/11.2.0/xe//lib -lclntsh -L/usr/lib/oracle/12.1/client/lib -L/usr/lib/oracle/12.1/client64/lib -L/usr/lib/oracle/11.2/client/lib -L/usr/lib/oracle/11.2/client64/lib -L/usr/lib/oracle/11.1/client/lib -L/usr/lib/oracle/11.1/client64/lib -L/usr/lib/oracle/10.2.0.5/client/lib -L/usr/lib/oracle/10.2.0.5/client64/lib -L/usr/lib/oracle/10.2.0.4/client/lib -L/usr/lib/oracle/10.2.0.4/client64/lib -L/usr/lib/oracle/10.2.0.3/client/lib -L/usr/lib/oracle/10.2.0.3/client64/lib [enterprisedb@edb95 oracle_fdw-ORACLE_FDW_1_4_0]$ su -c "make install" 密碼： /bin/mkdir -p '/opt/PostgresPlus/9.5AS/lib' /bin/mkdir -p '/opt/PostgresPlus/9.5AS/share/extension' /bin/mkdir -p '/opt/PostgresPlus/9.5AS/share/extension' /bin/mkdir -p '/opt/PostgresPlus/9.5AS/doc/extension' /usr/bin/install -c -m 755 oracle_fdw.so '/opt/PostgresPlus/9.5AS/lib/oracle_fdw.so' /usr/bin/install -c -m 644 .//oracle_fdw.control '/opt/PostgresPlus/9.5AS/share/extension/' /usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql '/opt/PostgresPlus/9.5AS/share/extension/' /usr/bin/install -c -m 644 .//README.oracle_fdw '/opt/PostgresPlus/9.5AS/doc/extension/'
上面標紅色的地方，就是執行的指令（僅短短三行喔）
4. 在 PostgreSQL 中，檢查是否有安裝成功
edb=# SELECT name FROM pg_available_extensions
WHERE name LIKE '%fdw';
name
--------------
oracle_fdw
file_fdw
postgres_fdw
(3 rows)
順帶一提的是，原生 PostgreSQL 中就先內建好對 PostgreSQL 以及對某些含表格檔案（例如，CSV）的 Foreign Data Wrapper 了。
5. 最後，啟用並設置 oracle_fdw
以下為一般建立 Foreign Data Wrapper 的一般流程：建立一個 Foreign Data Wrapper 是以 Database 為單位的，接著是建立所連線的 Oracle Server 以及登入資訊（User mapping）。這樣便能夠建立外部表格了
edb=# CREATE EXTENSION oracle_fdw; CREATE EXTENSION edb=# CREATE SERVER to_ora edb-# FOREIGN DATA WRAPPER oracle_fdw edb-# OPTIONS (dbserver '//127.0.0.1/xe'); CREATE SERVER edb=# CREATE USER MAPPING FOR CURRENT_USER edb-# SERVER to_ora edb-# OPTIONS (user 'hr', password 'hr'); CREATE USER MAPPING edb=# -- 建立外部表格 edb=# CREATE FOREIGN TABLE tst2ftw edb-# (nme varchar2(10) edb(# ) SERVER to_ora edb-# OPTIONS ( schema 'HR', table 'TST2'); CREATE FOREIGN TABLE
這樣一來，Foreign Table tst2ftw 就能夠當成一般表格使用了。
最後要提醒的是，在這裡所打算存取的 Oracle 表格，記得欄位要指名清楚才認的到喔。[20161004 補充] oeacle_fdw 所使用的 Oracle 連線字串，格式為下列兩種。無須在 PostgreSQL 主機上設定 tnsnames.ora 設定檔。
ORACLE_DB_IP:ORACLE_PORT/SID
ORACLE_DB_IP:ORACLE_PORT/SERVICE_NAME
|
[20190110 補充] 要能夠透過外部表對 Oracle 原表格作 UPDATE 動作，需要 (1) Oracle 上面的表格有 Primary Key，(2) Postgres 上面的外部表，要對相對應的 Primary Key 都增加以下設定（Primary Key 可能是由多個欄位組成的，這些欄位都要有這個設定）
ALTER FOREIGN TABLE hr.test ALTER COLUMN pkey_column1 OPTIONS (ADD key 'true');
ALTER FOREIGN TABLE hr.test ALTER COLUMN pkey_column2 OPTIONS (ADD key 'true');
不然進行 UPDATE 指令會有類似以下的錯誤ERROR: no primary key column specified for foreign Oracle table DETAIL: For UPDATE or DELETE, at least one foreign table column must be marked as primary key column. HINT: Set the option "key" on the columns that belong to the primary key. ********** Error ********** ERROR: no primary key column specified for foreign Oracle table SQL state: HV00L Detail: For UPDATE or DELETE, at least one foreign table column must be marked as primary key column. Hint: Set the option "key" on the columns that belong to the primary key.
參考：
沒有留言:
張貼留言