背景：

同一個項目兩個系統分別使用了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數據內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！