背景：
同一個專案兩個系統分別使用了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)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/286081.html
標籤：PostgreSQL
上一篇：ORA-01536: space quota exceeded for tablespace案例
下一篇：Excel-宏與VBA-資料型別