PostgreSQL跨庫操作Oracle利器-Oracle_fdw

Oracle_fdw是PG的一個外部資料接口，可以使PostgreSQL輕松跨庫操作Oracle。Oracle_fdw的作用有以下兩點：

• PG可以跨庫增删改查Oracle中的表，可以查詢Oracle的視圖，可以使PG中的表和Oracle中表/視圖作Join查詢，類似dblink的功能。

• 快速将Oralce表遷移進入PostgreSQL。

  本文簡單介紹下Oracle_fdw的安裝和使用。

一 Oracle_fdw安裝

官方位址：

http://pgxn.org/dist/oracle_fdw/

，選擇一個版本下載下傳。

1.1 安裝Oracle Instant Client

從

oralce官網

下載下傳 'Basic' and 'SDK'，假如下載下傳後檔案所在位置在/opt/oracle中。

cd /opt/oracle
unzip instantclient-basic-linux.x64-12.2.0.1.0.zip
unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip
mv instantclient_12_2 instantclient
cd instantclient
#建立一下軟連接配接
ln -s libclntsh.so.12.1 libclntsh.so
#設定環境變量
vi /etc/profile
#邊界内容如下：
#oracle_home一定要寫，否則編譯會報錯
export ORACLE_HOME=/opt/oracle/instantclient
export OCI_LIB_DIR=$ORACLE_HOME
export OCI_INC_DIR=$ORACLE_HOME/sdk/include
#儲存退出
#重新開機用profile檔案
source /etc/profile
           

1.2 編譯oracle_fdw

啟用postgres使用者環境變量

[root@bogon opt]# source /home/postgres/.bashrc
           

解壓oracle_fdw

[root@bogon opt]# unzip oracle_fdw-1.5.0.zip 
           

編譯安裝oracle_fdw

[root@bogon opt]# cd oracle_fdw-1.5.0
#編譯
[root@bogon oracle_fdw-1.5.0]# make
#安裝
[root@bogon oracle_fdw-1.5.0]# make install
           

沒報錯的話，代表安裝成功了，有時候會報一找不到.h頭檔案的錯誤，比如：

fatal err:oci.h:No such file or directory
#或者
fatal err:stdio.h:No such file or directory
           

錯誤截圖1.png

都證明ORACLE_HOME沒指定或沒有正确配置，需檢查環境變量及其檔案對應是否正确。

二 建立oracle_fdw擴充

postgres=# create extension oracle_fdw;
CREATE EXTENSION
           

代表建立成功，如果遇到下面這個問題：

postgres=# create extension oracle_fdw;
ERROR:  could not load library "/home/postgres/lib/oracle_fdw.so": libclntsh.so: cannot open shared object file: No such file or directory
           

是缺少so檔案了，有時候編譯成功了，還是會缺不少檔案，用ldd檢視下oracle_fdw.so的依賴：

[postgres@localhost lib]$ ldd oracle_fdw.so 
    linux-vdso.so.1 =>  (0x00007fff5973b000)
    libclntsh.so.12.1 => not found
    libc.so.6 => /lib64/libc.so.6 (0x00007fa9c8185000)
    libmql1.so => not found
    libipc1.so => not found
    libnnz12.so => not found
    libons.so => not found
    libdl.so.2 => /lib64/libdl.so.2 (0x00007fa9c7f6d000)
    libm.so.6 => /lib64/libm.so.6 (0x00007fa9c7c6b000)
    libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fa9c7a4f000)
    libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fa9c7835000)
    librt.so.1 => /lib64/librt.so.1 (0x00007fa9c762d000)
    libaio.so.1 => /lib64/libaio.so.1 (0x00007fa9c742b000)
    libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fa9c7210000)
    /lib64/ld-linux-x86-64.so.2 (0x00007fa9cc20e000)
    libclntshcore.so.12.1 => not found
    libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fa9c6ffa000)

           

對于這些not found的so檔案，我們在ORACLE_HOME目錄中發現是存在的，如下圖：

ORACLE_HOME.png

是以需要手動建立一下軟連接配接：

ln -s /opt/oracle/instantclient/libclntsh.so.12.1  /home/postgres/lib/libclntsh.so.12.1
ln -s /opt/oracle/instantclient/libmql1.so  /home/postgres/lib/libmql1.so
ln -s /opt/oracle/instantclient/libipc1.so  /home/postgres/lib/libipc1.so
ln -s /opt/oracle/instantclient/libnnz12.so  /home/postgres/lib/libnnz12.so
ln -s /opt/oracle/instantclient/libons.so  /home/postgres/lib/libons.so
ln -s /opt/oracle/instantclient/libclntshcore.so.12.1  /home/postgres/lib/libclntshcore.so.12.1
           

再次建立oracle_fdw：

postgres=# create extension oracle_fdw;
CREATE EXTENSION
           

應該就能建立成功了。

三 使用oracle_fdw

postgres=# create server oradb_215 foreign data wrapper oracle_fdw options(dbserver '10.144.15.215:1521/mcsas');
postgres=# grant usage on foreign server oradb_215 to postgres;
postgres=# create user mapping for postgres server oradb_215 options(user 'MG_APP',password 'QWERasdf');
postgres=# create foreign table ZWGK_SJJC_FBYJ_GTSJHD123
(
  OBJ_ID  VARCHAR(42) not null,
  XLMC    VARCHAR(50),
  DYDJ    VARCHAR(50),
  GTXH    VARCHAR(50),
  SJFBHD  VARCHAR(50),
  SSBQ    VARCHAR(50),
  BNHD    VARCHAR(50),
  SSWS    VARCHAR(50),
  PMSGTID VARCHAR(150),
  PMSGTBH VARCHAR(150),
  SFCL    VARCHAR(150)
) server oradb_215 options(schema 'MG_APP',table 'ZWGK_SJJC_FBYJ_GTSJHD');
postgres=# select * from ZWGK_SJJC_FBYJ_GTSJHD123 limit 10;
           

這樣，将oracle中MG_APP.ZWGK_SJJC_FBYJ_GTSJHD表“映射”到pg了，可以查詢了。

四 可能遇到的錯誤

4.1 OCIEnvCreate錯誤

OCIEnvCreate錯誤.png

解決方法：

• 1 檢查 /etc/profile中ORACLE_HOME配置及其

  export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH

• 2 檢查home/postgres/.bashrc也有:

  

  環境變量.png
• 3 postgres使用者下檢查oracle_fdw.so的執行權限：

  

  image.png

  Xshell下是綠色的，要是灰色，就 chmod 777 $PGHOME/lib/oracle_fdw.so

• 4 全部檢查完畢後一定要重新開機pg服務。

4.2 client host name is not set

有時候報錯：ORA-24454: client host name is not set，這屬于非主流錯誤，原因是本機的hosts設定問題。

可能遇到的錯誤.png

編輯對應伺服器的hosts檔案：

Root@後面的是伺服器的名稱，是以如下配置:

儲存退出即可解決問題。