背景：

同一個(gè)項(xiàng)目?jī)蓚€(gè)系統(tǒng)分別使用了PG庫(kù)和Oracle庫(kù)，Oracle是生產(chǎn)庫(kù)，數(shù)據(jù)動(dòng)態(tài)更新，現(xiàn)在在PG庫(kù)中需要實(shí)時(shí)的獲取到更新的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，基于此種方式，可以通過(guò)ETL的工具實(shí)現(xiàn)，但是需要定期進(jìn)行維護(hù)等，于是想著是否可以通過(guò)類似于Oracle數(shù)據(jù)庫(kù)DBLINK的方式去實(shí)現(xiàn)，經(jīng)過(guò)網(wǎng)上查找相關(guān)資料，發(fā)現(xiàn)可以通過(guò)oracle_fdw實(shí)現(xiàn)。

測(cè)試環(huán)境：

本地搭建測(cè)試環(huán)境，基礎(chǔ)配置如下：

Oracle數(shù)據(jù)庫(kù)測(cè)試服務(wù)器（IP:192.168.1.110）：WIN10操作系統(tǒng)，Oracle數(shù)據(jù)庫(kù)版本為11.2.0.4，實(shí)例名為orcl，安裝有32位客戶端；

PG庫(kù)測(cè)試服務(wù)器（虛擬機(jī)，IP:192.168.30.128，NAT模式）：WIN10操作系統(tǒng)，PG數(shù)據(jù)庫(kù)版本為11.11.1；

實(shí)現(xiàn)步驟：

1、首先確定網(wǎng)絡(luò)通常，在PG庫(kù)服務(wù)器可以訪問(wèn)到Oracle庫(kù)服務(wù)器。

2、安裝PG庫(kù)（步驟略）。這里需要注意，安裝完成的PG庫(kù)沒(méi)有開(kāi)啟遠(yuǎn)程訪問(wèn)，如果需要遠(yuǎn)程訪問(wèn)，需要先修改pg_hba.conf文件，添加以下內(nèi)容即可。

host all all 0.0.0.0/0 md5

3、下載oracle_fdw，注意下載時(shí)候需要匹配PG庫(kù)的版本。

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

我這里下載的是匹配PG11，選擇Windows64位置操作系統(tǒng)的。

注意：fdw版本必須和PG庫(kù)版本以及操作系統(tǒng)版本相對(duì)應(yīng)，否則后面會(huì)出問(wèn)題。

3、解壓oracle_fdw，將【lib】和【share/extension】文件夾中文件拷貝到PG庫(kù)安裝路徑下對(duì)應(yīng)的【lib】和【share/extension】文件夾中。

拷貝之后，通過(guò)sql語(yǔ)句可以查詢到oracle_fdw，說(shuō)明文件拷貝放置成功，但是尚未安裝（isstalled_version為空）。

select * from pg_available_extensions;

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

先不用急著安裝oracle_fdw（安裝也不會(huì)成功），因?yàn)檫€需要Oracle客戶端支持。如果不安裝Oracle客戶端，會(huì)有下面的錯(cuò)誤提示。

Oracle客戶端建議和連接的Oracle服務(wù)端采用相同版本（測(cè)試有小版本差別也不影響，大版本未測(cè)試），另外看網(wǎng)上資料也可以按照輕量級(jí)的oracle instant client替代，這里我沒(méi)有試過(guò)，有興趣的可以嘗試一下。

安裝完成后注意先進(jìn)行連接測(cè)試，確保連接正常。

注意：客戶端的版本必須和PG庫(kù)的一致，例如我安裝的是64位的PG庫(kù)，那么一定要安裝64位的oracle客戶端，之前習(xí)慣安裝了32位的客戶端，在創(chuàng)建外部表后沒(méi)法打開(kāi)，提示下面錯(cuò)誤。

如果還是有問(wèn)題，可以檢查安裝路徑是否已經(jīng)寫入Path變量中，將其移動(dòng)至最上面。

5、創(chuàng)建安裝oracle_fdw

-- 創(chuàng)建oracle_fdw
create extension oracle_fdw;

安裝成功后通過(guò)下面之前的語(yǔ)句進(jìn)行驗(yàn)證。

select * from pg_available_extensions;

可以看到installed_version已經(jīng)顯示安裝版本了，驗(yàn)證表示安裝成功。

注意：如果多次安裝失敗，建議可以重啟一下PG服務(wù)或者服務(wù)器后重試。

6、Oracle庫(kù)中制作測(cè)試數(shù)據(jù)

數(shù)據(jù)庫(kù)連接信息如下：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,'市南區(qū)','370202');
insert into oracledata_test values(2,'市北區(qū)','370203');

增加測(cè)試數(shù)據(jù)后注意進(jìn)行提交操作。

7、PG庫(kù)創(chuàng)建Oracle連接

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

創(chuàng)建后可以通過(guò)連接獲取Oracle數(shù)據(jù)庫(kù)數(shù)據(jù)。

8、PG庫(kù)進(jìn)行用戶授權(quán)

--授權(quán)
grant usage on foreign server oradb_110 to postgres;

授權(quán)根據(jù)實(shí)際需要進(jìn)行。

9、創(chuàng)建到Oracle的映射

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

其中oradb_110是之前創(chuàng)建的數(shù)據(jù)庫(kù)連接名稱，GIS為連接Oracle的用戶名和密碼。

10、創(chuàng)建需要訪問(wèn)Oracle的對(duì)應(yīng)表

注意這里創(chuàng)建的時(shí)候要注意字段類型的轉(zhuǎn)換，Oracle和PG庫(kù)在字段類型上還是有所差別的。其中oradb_110是我們上面創(chuàng)建的數(shù)據(jù)庫(kù)連接名稱，GIS是連接。

--創(chuàng)建需要訪問(wèn)的oracle中對(duì)應(yīng)表的結(jié)構(gòu)
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指定表中的字段，而是通過(guò)順序映射的方式，后面會(huì)進(jìn)行測(cè)試說(shuō)明。

11、現(xiàn)在通過(guò)外部表即可查看Oracle過(guò)來(lái)的數(shù)據(jù)。

如果需要對(duì)創(chuàng)建的內(nèi)容進(jìn)行刪除，可以使用下面語(yǔ)句：

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

11、數(shù)據(jù)同步測(cè)試。

在oracle數(shù)據(jù)庫(kù)中實(shí)時(shí)插入一條記錄

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

插入數(shù)據(jù)后注意提交，然后查詢確認(rèn)。

在PG庫(kù)中進(jìn)行查詢確認(rèn)：

可以看到，數(shù)據(jù)可以實(shí)時(shí)的同步過(guò)去。

12、表映射測(cè)試。

例如現(xiàn)在的測(cè)試表中有三個(gè)字段，我在PG庫(kù)中如果只用到第一個(gè)和第三個(gè)字段，那我的外部表這樣去構(gòu)建：

--創(chuàng)建需要訪問(wèn)的oracle中對(duì)應(yīng)表的結(jié)構(gòu)
create foreign  table ORACLEDATA_TEST_PG_2
 (
   ID   numeric(10) not null,
   XZQDM        VARCHAR(30)
 ) server oradb_110 options(schema 'GIS',table 'ORACLEDATA_TEST');

然后查詢數(shù)據(jù)：

從結(jié)果中可以看出，我們選擇的xzqdm獲取到的并非是xzqdm的值，而是xzqmc的值，其為根據(jù)順序映射的，并非是通過(guò)字段名稱映射。

13、性能方面

初步測(cè)試了一下，對(duì)于大數(shù)據(jù)量性能還是比較低的，這塊沒(méi)有進(jìn)行嚴(yán)格的測(cè)試，后面有機(jī)會(huì)可以再補(bǔ)充。

參考資料：

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

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

到此這篇關(guān)于PostgreSQL通過(guò)oracle_fdw訪問(wèn)Oracle數(shù)據(jù)的實(shí)現(xiàn)步驟的文章就介紹到這了,更多相關(guān)PostgreSQL訪問(wèn)Oracle數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論