PostgreSQL通過oracle_fdw訪問Oracle數據的實現步驟

Posted on by

背景：

同一個項目兩個系統分別使用瞭PG庫和Oracle庫，Oracle是生產庫，數據動態更新，現在在PG庫中需要實時的獲取到更新的數據進行統計，基於此種方式，可以通過ETL的工具實現，但是需要定期進行維護等，於是想著是否可以通過類似於Oracle數據庫DBLINK的方式去實現，經過網上查找相關資料，發現可以通過oracle_fdw實現。

測試環境：

本地搭建測試環境，基礎配置如下：

Oracle數據庫測試服務器（IP:192.168.1.110）：WIN10操作系統，Oracle數據庫版本為11.2.0.4，實例名為orcl，安裝有32位客戶端；

PG庫測試服務器（虛擬機，IP:192.168.30.128，NAT模式）：WIN10操作系統，PG數據庫版本為11.11.1；

實現步驟：

1、首先確定網絡通常，在PG庫服務器可以訪問到Oracle庫服務器。

2、安裝PG庫（步驟略）。這裡需要註意，安裝完成的PG庫沒有開啟遠程訪問，如果需要遠程訪問，需要先修改pg_hba.conf文件，添加以下內容即可。

host all all 0.0.0.0/0 md5

3、下載oracle_fdw，註意下載時候需要匹配PG庫的版本。

下載地址：Releases · laurenz/oracle_fdw · GitHub

我這裡下載的是匹配PG11，選擇Windows64位置操作系統的。

註意：fdw版本必須和PG庫版本以及操作系統版本相對應，否則後面會出問題。

3、解壓oracle_fdw，將【lib】和【share/extension】文件夾中文件拷貝到PG庫安裝路徑下對應的【lib】和【share/extension】文件夾中。

拷貝之後，通過sql語句可以查詢到oracle_fdw，說明文件拷貝放置成功，但是尚未安裝（isstalled_version為空）。

select * from pg_available_extensions;

4、安裝Oracle客戶端（步驟略）

先不用急著安裝oracle_fdw（安裝也不會成功），因為還需要Oracle客戶端支持。如果不安裝Oracle客戶端，會有下面的錯誤提示。

Oracle客戶端建議和連接的Oracle服務端采用相同版本（測試有小版本差別也不影響，大版本未測試），另外看網上資料也可以按照輕量級的oracle instant client替代，這裡我沒有試過，有興趣的可以嘗試一下。

安裝完成後註意先進行連接測試，確保連接正常。

註意：客戶端的版本必須和PG庫的一致，例如我安裝的是64位的PG庫，那麼一定要安裝64位的oracle客戶端，之前習慣安裝瞭32位的客戶端，在創建外部表後沒法打開，提示下面錯誤。

如果還是有問題，可以檢查安裝路徑是否已經寫入Path變量中，將其移動至最上面。

5、創建安裝oracle_fdw

-- 創建oracle_fdw
create extension oracle_fdw;

安裝成功後通過下面之前的語句進行驗證。

select * from pg_available_extensions;

可以看到installed_version已經顯示安裝版本瞭，驗證表示安裝成功。

註意：如果多次安裝失敗，建議可以重啟一下PG服務或者服務器後重試。

6、Oracle庫中制作測試數據

數據庫連接信息如下：192.168.1.110/orcl 用戶名/密碼：GIS/GIS

-- Create test table
create table ORACLEDATA_TEST
 (
   ID   NUMBER(10) not null,
   XZQMC        NVARCHAR2(50),
   XZQDM        NVARCHAR2(30)
 )
-- insert test data
insert into oracledata_test values(1,'市南區','370202');
insert into oracledata_test values(2,'市北區','370203');

增加測試數據後註意進行提交操作。

7、PG庫創建Oracle連接

--創建Oracle外部連接，其中oradb_110為連接名稱
create server oradb_110 foreign data wrapper oracle_fdw options(dbserver '192.168.1.110/orcl');

創建後可以通過連接獲取Oracle數據庫數據。

8、PG庫進行用戶授權

--授權
grant usage on foreign server oradb_110 to postgres;

授權根據實際需要進行。

9、創建到Oracle的映射

--創建到oracle的映射
create user mapping for postgres server oradb_110 options(user 'GIS',password 'GIS');

其中oradb_110是之前創建的數據庫連接名稱，GIS為連接Oracle的用戶名和密碼。

10、創建需要訪問Oracle的對應表

註意這裡創建的時候要註意字段類型的轉換，Oracle和PG庫在字段類型上還是有所差別的。其中oradb_110是我們上面創建的數據庫連接名稱，GIS是連接。

--創建需要訪問的oracle中對應表的結構
create foreign  table ORACLEDATA_TEST_PG
 (
   ID   numeric(10) not null,
   XZQMC        VARCHAR(50),
   XZQDM        VARCHAR(30)
 ) server oradb_110 options(schema 'GIS',table 'ORACLEDATA_TEST');

註意：這裡建立的表並不像是視圖那樣獲取oracle指定表中的字段，而是通過順序映射的方式，後面會進行測試說明。

11、現在通過外部表即可查看Oracle過來的數據。

如果需要對創建的內容進行刪除，可以使用下面語句：

DROP FOREIGN TABLE table_name;
DROP USER MAPPING FOR user_name SERVER server_name;
DROP SERVER server_name;

11、數據同步測試。

在oracle數據庫中實時插入一條記錄

-- insert test data
insert into oracledata_test values(3,'李滄區','370203');

插入數據後註意提交，然後查詢確認。

在PG庫中進行查詢確認：

可以看到，數據可以實時的同步過去。

12、表映射測試。

例如現在的測試表中有三個字段，我在PG庫中如果隻用到第一個和第三個字段，那我的外部表這樣去構建：

--創建需要訪問的oracle中對應表的結構
create foreign  table ORACLEDATA_TEST_PG_2
 (
   ID   numeric(10) not null,
   XZQDM        VARCHAR(30)
 ) server oradb_110 options(schema 'GIS',table 'ORACLEDATA_TEST');

然後查詢數據：

從結果中可以看出，我們選擇的xzqdm獲取到的並非是xzqdm的值，而是xzqmc的值，其為根據順序映射的，並非是通過字段名稱映射。

13、性能方面

初步測試瞭一下，對於大數據量性能還是比較低的，這塊沒有進行嚴格的測試，後面有機會可以再補充。

參考資料：

詳解PostgreSQL成功安裝oracle_fdw方法，解決the specified procedure could not be found錯誤_ljinxin的博客-CSDN博客

PostgreSQL之oracle_fdw安裝與使用 – Kevin_zheng – 博客園 (cnblogs.com)

到此這篇關於PostgreSQL通過oracle_fdw訪問Oracle數據的實現步驟的文章就介紹到這瞭,更多相關PostgreSQL訪問Oracle數據內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：