PostgreSQL跨庫操作Oracle利器-Oracle_fdw

Oracle_fdw是PG的一個(gè)外部數(shù)據(jù)接口团南,可以使PostgreSQL輕松跨庫操作Oracle。Oracle_fdw的作用有以下兩點(diǎn):

  • PG可以跨庫增刪改查Oracle中的表拷橘,可以查詢Oracle的視圖,可以使PG中的表和Oracle中表/視圖作Join查詢赌厅,類似dblink的功能。
  • 快速將Oralce表遷移進(jìn)入PostgreSQL。
    本文簡(jiǎn)單介紹下Oracle_fdw的安裝和使用毒嫡。

一 Oracle_fdw安裝

官方地址:http://pgxn.org/dist/oracle_fdw/ ,選擇一個(gè)版本下載。

1.1 安裝Oracle Instant Client

oralce官網(wǎng)下載 '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
#設(shè)置環(huán)境變量
vi /etc/profile
#邊界內(nèi)容如下:
#oracle_home一定要寫,否則編譯會(huì)報(bào)錯(cuò)
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用戶環(huán)境變量

[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

沒報(bào)錯(cuò)的話,代表安裝成功了局雄,有時(shí)候會(huì)報(bào)一找不到.h頭文件的錯(cuò)誤墩邀,比如:

fatal err:oci.h:No such file or directory
#或者
fatal err:stdio.h:No such file or directory
錯(cuò)誤截圖1.png

都證明ORACLE_HOME沒指定或沒有正確配置享完,需檢查環(huán)境變量及其文件對(duì)應(yīng)是否正確彼绷。

二 創(chuàng)建oracle_fdw擴(kuò)展

postgres=# create extension oracle_fdw;
CREATE EXTENSION

代表創(chuàng)建成功,如果遇到下面這個(gè)問題:

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文件了猜旬,有時(shí)候編譯成功了,還是會(huì)缺不少文件,用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)

對(duì)于這些not found的so文件,我們?cè)贠RACLE_HOME目錄中發(fā)現(xiàn)是存在的倦蚪,如下圖:

ORACLE_HOME.png

因此需要手動(dòng)建立一下軟連接:

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

再次創(chuàng)建oracle_fdw:

postgres=# create extension oracle_fdw;
CREATE EXTENSION

應(yīng)該就能創(chuàng)建成功了个束。

三 使用oracle_fdw

3.1 配置foreign server

foreign server其實(shí)就是在pg中配置跨庫操作的對(duì)端數(shù)據(jù)庫連接參數(shù)沪悲,這里示例,配置一個(gè)遠(yuǎn)程oracle數(shù)據(jù)庫連接參數(shù)涉馁。

postgres=# create server oradb_test foreign data wrapper oracle_fdw options(dbserver '192.168.0.215:1521/ora_db');
postgres=# grant usage on foreign server oradb_test to postgres;
postgres=# create user mapping for postgres server oradb_test  options(user 'MG_APP',password 'QWERasdf');

有時(shí)候,我們配置的oracle連接參數(shù)發(fā)生變化,需要修改連接參數(shù)叶沛。

3.1.1 ip,端口溉箕,實(shí)例變化

postgres=#  alter server oradb_test options(set dbserver '192.168.0.214:1521/ora_db');

3.1.2 user,password變化

postgres=# alter user mapping for postgres server oradb_test options (set user 'MG_APP1',set password 'QWERasdf');

3.2 配置foreign table

fdw對(duì)表和視圖,只能手動(dòng)單獨(dú)配置抗楔,配置完數(shù)據(jù)庫連接參數(shù)连躏,再配置下要訪問的oracle的表或視圖的數(shù)據(jù)結(jié)構(gòu)。注意,配置外表的schema與table 對(duì)于oralce郑气,都是要求大寫腰池,小寫不認(rèn)識(shí)尾组。

postgres=# create foreign table 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_test  options(schema 'MG_APP',table 'GTSJHD');
postgres=# select * from GTSJHD123 limit 10;

這樣,將oracle中MG_APP.GTSJHD表“映射”到pg了示弓,可以查詢了讳侨。

3.3 跨庫增刪改

oracle_fdw不僅支持跨庫查詢,也支持跨庫增刪改跨跨。
首先在oracle創(chuàng)建一個(gè)測(cè)試表,并插入一個(gè)記錄:

create table test1(
     id int,
      name char(10)
);
insert into test1(id,name) values (1,'aa');
--提交下事務(wù)更新

其次囱皿,在pg中創(chuàng)建外部表:

create foreign table test1_1(
  id int options(key 'true') not null,
  name text
) server oradb_test  options(schema 'MG_APP',table 'TEST1');

注意勇婴,update或delete一定要設(shè)置options(key 'true'),就是設(shè)置外部表的主鍵嘱腥,否則會(huì)報(bào)錯(cuò)耕渴。

no primary key column specified for foreign Oracle table

測(cè)試增刪改

insert into test1_1 values (2,'BB');
update test1_1 set name='123' where id=2;
select * from test1_1;
delete from test1_1 where id=2;

四 可能遇到的錯(cuò)誤

4.1 OCIEnvCreate錯(cuò)誤

OCIEnvCreate錯(cuò)誤.png

解決方法:

  • 1 檢查 /etc/profile中ORACLE_HOME配置及其
    export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH

  • 2 檢查home/postgres/.bashrc也有:
    export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH


    環(huán)境變量.png
  • 3 postgres用戶下檢查oracle_fdw.so的執(zhí)行權(quán)限:


    image.png

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

  • 4 全部檢查完畢后一定要重啟pg服務(wù)橱脸。

4.2 client host name is not set

有時(shí)候報(bào)錯(cuò):ORA-24454: client host name is not set,這屬于非主流錯(cuò)誤分苇,原因是本機(jī)的hosts設(shè)置問題添诉。
可能遇到的錯(cuò)誤.png

編輯對(duì)應(yīng)服務(wù)器的hosts文件:


image.png

Root@后面的是服務(wù)器的名稱,所以如下配置:
image.png

保存退出即可解決問題医寿。

4.3 no primary key column specified for foreign Oracle table

外部表創(chuàng)建時(shí)栏赴,一定要綁定主鍵,如:

create foreign table test1_1(
  id int options(key 'true') not null,
  name text
) server oradb_test  options(schema 'MG_APP',table 'TEST1');

技術(shù)上:oracle表主鍵建立與否無關(guān)靖秩,但是pg中的oracle foreign table一定要通過key參數(shù)须眷,申明主鍵乌叶,否則update,delete會(huì)報(bào)錯(cuò)。
工程上:oralce表一定要建立主鍵柒爸,pg中oracle foreign table的key就綁定到這個(gè)主鍵字段上。這樣會(huì)使數(shù)據(jù)更規(guī)范事扭。

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末捎稚,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子求橄,更是在濱河造成了極大的恐慌今野,老刑警劉巖,帶你破解...
    沈念sama閱讀 222,807評(píng)論 6 518
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件罐农,死亡現(xiàn)場(chǎng)離奇詭異条霜,居然都是意外死亡,警方通過查閱死者的電腦和手機(jī)涵亏,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,284評(píng)論 3 399
  • 文/潘曉璐 我一進(jìn)店門宰睡,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人气筋,你說我怎么就攤上這事拆内。” “怎么了宠默?”我有些...
    開封第一講書人閱讀 169,589評(píng)論 0 363
  • 文/不壞的土叔 我叫張陵麸恍,是天一觀的道長(zhǎng)。 經(jīng)常有香客問我搀矫,道長(zhǎng)抹沪,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 60,188評(píng)論 1 300
  • 正文 為了忘掉前任瓤球,我火速辦了婚禮融欧,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘冰垄。我一直安慰自己蹬癌,他們只是感情好虹茶,可當(dāng)我...
    茶點(diǎn)故事閱讀 69,185評(píng)論 6 398
  • 文/花漫 我一把揭開白布蝴罪。 她就那樣靜靜地躺著,像睡著了一般要门。 火紅的嫁衣襯著肌膚如雪廓啊。 梳的紋絲不亂的頭發(fā)上封豪,一...
    開封第一講書人閱讀 52,785評(píng)論 1 314
  • 那天谴轮,我揣著相機(jī)與錄音,去河邊找鬼吹埠。 笑死第步,一個(gè)胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的缘琅。 我是一名探鬼主播粘都,決...
    沈念sama閱讀 41,220評(píng)論 3 423
  • 文/蒼蘭香墨 我猛地睜開眼,長(zhǎng)吁一口氣:“原來是場(chǎng)噩夢(mèng)啊……” “哼刷袍!你這毒婦竟也來了翩隧?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 40,167評(píng)論 0 277
  • 序言:老撾萬榮一對(duì)情侶失蹤呻纹,失蹤者是張志新(化名)和其女友劉穎堆生,沒想到半個(gè)月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體雷酪,經(jīng)...
    沈念sama閱讀 46,698評(píng)論 1 320
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡顽频,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 38,767評(píng)論 3 343
  • 正文 我和宋清朗相戀三年,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了太闺。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片糯景。...
    茶點(diǎn)故事閱讀 40,912評(píng)論 1 353
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡,死狀恐怖省骂,靈堂內(nèi)的尸體忽然破棺而出蟀淮,到底是詐尸還是另有隱情钞澳,我是刑警寧澤轧粟,帶...
    沈念sama閱讀 36,572評(píng)論 5 351
  • 正文 年R本政府宣布策治,位于F島的核電站通惫,受9級(jí)特大地震影響履腋,放射性物質(zhì)發(fā)生泄漏遵湖。R本人自食惡果不足惜谋国,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 42,254評(píng)論 3 336
  • 文/蒙蒙 一烹卒、第九天 我趴在偏房一處隱蔽的房頂上張望逢勾。 院中可真熱鬧溺拱,春花似錦迫摔、人聲如沸句占。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,746評(píng)論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至至扰,卻和暖如春渊胸,著一層夾襖步出監(jiān)牢的瞬間翎猛,已是汗流浹背切厘。 一陣腳步聲響...
    開封第一講書人閱讀 33,859評(píng)論 1 274
  • 我被黑心中介騙來泰國(guó)打工培他, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人猛遍。 一個(gè)月前我還...
    沈念sama閱讀 49,359評(píng)論 3 379
  • 正文 我出身青樓懊烤,卻偏偏與公主長(zhǎng)得像,于是被迫代替她去往敵國(guó)和親畜隶。 傳聞我的和親對(duì)象是個(gè)殘疾皇子墩划,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,922評(píng)論 2 361

推薦閱讀更多精彩內(nèi)容

  • 背景: 閱讀新聞 12C CDB模式下RMAN備份與恢復(fù) [日期:2016-11-29] 來源:Linux社區(qū) 作...
    陽屯okyepd閱讀 3,511評(píng)論 0 7
  • About:PostgreSQL About 《PostgreSQL 源碼分析系列》 PostgreSQL 源碼分...
    ty4z2008閱讀 8,199評(píng)論 1 40
  • 最近公司購買了一臺(tái)新的服務(wù)器察净,我負(fù)責(zé)給這臺(tái)服務(wù)器安裝oracle ,磕磕絆絆的終于裝好了氢卡,但是卻花了不少時(shí)間译秦,算起...
    testerPM閱讀 9,164評(píng)論 3 8
  • Spring Cloud為開發(fā)人員提供了快速構(gòu)建分布式系統(tǒng)中一些常見模式的工具(例如配置管理,服務(wù)發(fā)現(xiàn)砚婆,斷路器装盯,智...
    卡卡羅2017閱讀 134,716評(píng)論 18 139
  • 說明:實(shí)驗(yàn)過的版本:CentOS 6.5 和 CentOS 7 1.硬件檢查 內(nèi)存大于1G(使用虛擬機(jī)安裝時(shí)內(nèi)存要...
    飛翼_U閱讀 2,389評(píng)論 0 3