數據庫

從Oracle到PostgreSQL,某保險公司遷移實踐

廣告
廣告

摘要:去O一直是金融保險行業永恒的話題,但去O的難度之大也只有真正經歷過的人才知其中的艱辛。此次筆者結合實際去O工作,對去O過程中碰到的DBLINK、SEQUENCE最大值、空串、SQL語句中的別名等等近50個問題進行探討,絕對是干貨滿滿,誠意十足!

Oracle ACE-A,Oracle 10g OCM,SOUG(中國南方ORACLE用戶組)聯合發起人,ITPUB 專家。目前任職于某公司,擔任技術經理職位,負責公司的軟件基礎設施的建設和維護工作。

PostgreSQL VS Oracle

站在開發和應用的角度看,底層數據庫搭建再好,但如果應用在上面跑不起來,跑的不好,新數據庫平臺就會變成一座鬼城。

今天,我分享的內容分四大部分,一是技術選型。第二、三是今天的重點,怎么讓應用真正在新的數據庫平臺上面跑起來,這是要探究的一個問題。會涉及到一些比較細節的東西,尤其對于開發人員可能會更加關注一些,當然對于DBA來說,也是需要去注意的。

金融行業技術選型有個特點,當你向公司推薦一個數據庫產品時,領導肯定會問個問題,同行業有沒有在使用的?

我為什么會選擇PostgreSQL?一個很重要的原因是不少同行在用,比如,平安科技。那拋開同行業,PostgreSQL自身有哪些優勢?

我們做了PostgreSQL與Oracle的對比,就單體模式來說, PostgreSQL完全不輸給Oracle,可以做到完全實時的同步,單體保持數據同步方面一點問題都沒有。

分片模式, Oracle提供了一個Sharding模式。相對應的PostgreSQL有XC/XL解決方案。當然咯,這個方案也不是完美的,比如說GTM可能會成為性能的瓶頸點,很有可能會成為一個瓶頸。

另外,Oracle有個非常強大的功能RAC,這個在PostgreSQL里是沒有的。不過PostgreSQL有讀寫分離的解決方案,在讀多寫少的場景下,能達到不錯的吞吐量。

目前,不管是商業的還是開源的數據庫,基本上逃不脫這四種架構。

看下來,PostgreSQL和Oracle是非常像的,包括架構以及數據安全方面,都與Oracle非常像。這也是我們選擇PostgreSQL重要的原因。同時,PostgreSQL提供了豐富的可選架構,能滿足各種不同場景。PostgreSQL有效遵循SQL標準,讓應用遷移難度降低,PostgreSQL有豐富的可選組件,極易擴展等。

在這里順便也說一下我的觀點,現在都在說分布式,但分布式真的適合所有場景嗎?我看未必。而且什么是分布式,我覺得這個問題也值得深入思考下。

讓應用先跑起來

能不能讓應用正確的跑起來,這是事關生存的問題。事關你新系統能不能在公司里面生存下來的問題,這個是非常關鍵的問題。

下面,涉及到很多往PostgreSQL遷應用時遇到的問題,總結如下:

1、   字符集問題:

PostgreSQL服務端是不支持GBK的,我們用UTF8。PostgreSQL還有個編碼EUC CN,這個我們之前測過很多次,有很多生僻字是無法編碼的。

比如“瑄”在EUC_CN下就無法編碼。因此,不推薦使用EUC_CN。

2、多行注釋問題:

/* some comments

/* other comments

/*******************/

? 上述注釋在Oracle中是合法的

? 在POSTGRESQL中是非法的

? — 合法的PostgreSQL注釋格式:

? — This is a standard SQL comment

? /* multiline comment

? * with nesting: /* nested comment */

? */

? 可以使用PLY(Python-Lex-Yacc)將注釋自動改寫掉

3、NUMERIC類型問題:

? 上述聲明在Oracle中是合法的

? 但在POSTGRESQL中是非法的

? POSTGRESQL不支持負值的scale

? 也不支持scale大于precision

? 負值scale的解決方法:

{ 使用觸發器,在觸發器中調用round函數 }

SELECT round(123.6, -2);

 round

——-

   100

(1 row)

scale大于precision的解決方法:

{ NUMBER(2,3) => NUMERIC(3,3) }

{ 增加CHECK (col < 0.1)            }

DECLARE

    c  NUMBER := 1;

    pi NUMBER := 3.142;

    r  NUMBER := 10;

BEGIN

    FOR i IN 1..10000 LOOP

        c := pi * (r * r) + (mod(r, c) * pi + i);

    END LOOP;

END;

/

上述代碼在Oracle中是沒有任何問題

DO $$

DECLARE

    c  NUMERIC := 1;

    pi NUMERIC := 3.142;

    r  NUMERIC := 10;

BEGIN

    FOR i IN 1..10000 LOOP

        c := pi * (r * r) + (mod(r, c) * pi + i);

    END LOOP;

END;

$$ LANGUAGE plpgsql;

ERROR:  value overflows numeric format

解決方法: 顯式指定NUMERIC的精度

DO $$

DECLARE

    c  NUMERIC(32,2) := 1;

    pi NUMERIC := 3.142;

    r  NUMERIC := 10;

BEGIN

    FOR i IN 1..10000 LOOP

        c := pi * (r * r) + (mod(r, c) * pi + i);

    END LOOP;

END;

$$ LANGUAGE plpgsql;

4、VARCHAR類型問題

上面是經常碰到的VARCHAR問題,值已經超出了目標長度,肯定會報錯。但在PostgreSQL里面不一樣,會截斷但不報錯,這要特別注意,因為沒有報錯,你的應用如果沒有注意到這個問題,很有可能你的數據就丟失了,計算結果就出錯了,那這個就是很嚴重的問題了。

5、CHAR類型問題

在PostgreSQL中,CHAR類型的長度是實際有效字符的長度,這個和Oracle很不一樣。這個在應用中如果不注意的話,就會報很多錯誤。而且,有時候查起來非常困難。當然我們也可以通過重載函數的方式來模擬Oracle中的行為。

6、SEQUENCE最大值問題

?POSTGRESQL的SEQUENCE最大值:9223372036854775807(bigint)

?而Oracle中的SEQUENCE最大值可達28位十進制值

?一般情況下POSTGRESQL的SEQUENCE是足夠的

?但可能也存在一些特殊情況:

{LISCODE.SEQ_YBTBATTRANS_ID ‘10000000000000072561’ }

{ 該值明顯已超出最大值}                    

{ 使用NUMERIC類型,配合觸發器使用}

通常情況下PostgreSQL中SEQUENCE足夠使用。但上面這個值已經超出最大值,目前,我的解決辦法是把它用NUMERIC類型配合觸發器使用,用觸發器模擬序列類型,如果你是頻繁插入,性能下降會非常嚴重,這是需要注意的問題。

7、類型轉換

# CREATE TABLE t1 (id VARCHAR(32));

# SELECT * FROM t1 WHERE id = 27;

ERROR:  operator does not exist: character varying = integer

LINE 1: SELECT * FROM t1 WHERE id = 27;

                         ^

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

CREATE CAST (varchar AS integer)

WITH INOUT

AS  IMPLICIT;

# SELECT * FROM t1 WHERE id = 27;

 id

—-

(0 rows)

# EXPLAIN verbose SELECT * FROM t1 WHERE id = 27;

                        QUERY PLAN                        

———————————————————–

 Seq Scan on public.t1  (cost=0.00..22.95 rows=4 width=82)

   Output: id

   Filter: ((t1.id)::integer = 27)

(3 rows)

? 善用CAST

? 根據自己的需求,繪制類型轉換矩陣

如果對Oracle熟悉,就會知道Oracle是由明確的類型轉換矩陣的,在PostgreSQL里,這方面就差一點。但PostgreSQL提供了自定義創建CAST的特性。在我們實際遷移過程當中,如果你能夠把CAST利用好是能解決很大一部分問題的。

7、操作符重載

比如說SELECT 3/5是個雷,除了它本身3對應的是INT4,5對應的也是INT4,所以它本身是一個非整型數字,但是,它也是會被截斷掉。進行一個重載就可以解決,我們重新定義一個函數,可以用系統的numeric div,這樣用戶不需要做任何的修改就可以達到跟Oracle一樣的效果。

總結一下,操作符重載是PostgreSQL提供的一個非常好的特性,善用操作符重載可以解決一些兼容性問題,以及前面說的CAST是可以解決很多監管系統問題的,在這個過程中也是有很多問題需要注意的。

第一、POSTGRESQL本身它設置了很多類型轉換和操作符,這個一定要考慮是否有沖突。

第二、類型轉化的操作也需要相互配合,因為在調用操作符時,是要判斷類型轉換是否需要自己去做的,所以,這兩個是需要密切配合才能完美的使用好。

8、子查詢

Oracle當中子查詢不需要別名alias,但在PostgreSQL當中是不行的。

9、SELECT表達式別名問題

下種這種語句在Oracle里面沒有任何問題,但在PostgreSQL就會出問題。

究其原因,是PostgreSQL認為name是關鍵字,不能使用。但是這也有矛盾的地方。比如uncommited這個關鍵字和name是一樣的級別,但是uncommited就可以用來做別名。這個問題還需要研究。

9、SELECT INTO

這個問題也是需要特別注意,如果本身的代碼沒有這方面的錯誤處理,很有可能就會導致結果錯誤。

10、UPDATE語句問題

UPDATE語句中不能使用下面這個在Oracle可能很常見的寫法。INSERT也一樣。這個也不能說誰對誰錯,因為本身SQL標準中就不支持這樣的寫法的,PostgreSQL只是遵從了這個標準而已。

11、Oracle訪問PostgreSQL

在我們的系統中是有很多通過DBLINK來進行數據交互的,那遷移不太可能所有的系統一起簽掉,所以原有的DBLINK功能還得繼續保留。我們使用的方法是用Oracle Database Gateway。但是這里面有兩個問題,1是分布式事務的問題,在Oracle和Oracle之間沒有任何問題,是可以做分布式事務的,如果是PostgreSQL,這個時候是不支持的,Database Gateway還不支持異構數據庫間的2PC,這個要特別注意。

訪問的時候還會碰到性能問題,這個問題到目前為止我們也是一直沒有解決掉,這個語句非常簡單,這個庫我們已經給遷走了,遷到PostgreSQL里,通過DBLINK取PostgreSQL里面的數據。

我們發現,在生產環境當中,性能較差,但在測試環境里面性能很好。我們跟蹤了一下,發現同樣的一個SQL發過去,在Oracle雖然執行計劃是一樣的,但到達Database Gateway,再由Database Gateway出去后,生產環境它缺少一個WHERE條件。這個問題,我們找Oracle也溝通過好幾次,但他們也解決不了。我們用了好多辦法去復現測試環境,均告失敗,這個問題,也非常詭異,也值得深入研究。

12、PostgreSQL訪問Oracle

這是個非常強的現實需求。那我們使用oracle_fdw,Oracle fdw在日常的使用下沒有什么問題,而且性能也還不錯。但可能會碰到一個錯誤,發現這個錯誤的原因主要是Oracle fdw當中使用的事務級別是serializable,那如果有并發更新,就可能會報這樣的錯誤。

這就需要增加一個重試機制。

不過我們覺得Oracle_fdw事務級別設置的過于嚴格了,所以我們對源碼做了簡單的修改,把事務級別降下來.

修改后,實際使用中運行良好。

13、空串(”)、NULL問題

空串和NULL也是非常頭疼的問題,因為在Oracle當中空串和NULL是等價的,我個人更加偏向于PostgreSQL設計,更嚴謹。PostgreSQL里面空串是空串,NULL是NULL,但從Oracle牽引過來會碰到很多的問題。

比如說涉及到字符串判斷的地方都需要修改,有時候開發不會特別注意這方面的內容。還有涉及到字符串連接的地方都需要修改。另外,所有涉及到字符串轉為數值型的都需要修改,空串強制轉換會報錯。

在我們現在遷移項目中,如果都要修改的話,代碼里面差不多有幾十萬的修改量,這個是非常大的修改量,因為你修改了以后還要重新測試。

我們對PostgreSQL進行了定制化工作,從內核層面使空串與NULL進行等價。

大家可以看到,這是我們修改后的PostgreSQL執行情況,這個時候空串、NULL是等價的。

在做連接的時候,其實也是和Oracle里面的行為是一致的,包括函數的返回,比如說substr,正常應該返回空串,但是在Oracle返回的是NULL,我們修改了以后行為變成一致了。

類型轉換,空串的轉換也都沒有任何問題了。

從應用代碼當中執行,代碼當中包括綁定變量的方式,比如我們先選一下當前有沒有NULL值,當前是零,這里是空串,再去執行同樣的語句,會發現已經正確插進去了。這個是我們對PostgreSQL本身開源技術上做的一些小優化。

14、SYNONYM問題

PostgreSQL中是沒有SYNONYM這個概念的

1、可以通過調整search_path來解決

2、配合使用VIEW

15、列名大小寫問題

Oracle中的列名是大寫表示的

PostgreSQL中的列名是小寫表示的

在使用類似MyBatis這樣的工具時,需要將大寫轉為小寫,否則會導致你的列名找不到的問題,這個是需要特別注意的一個地方。

16、如何支持PACKAGE?

PostgreSQL中沒有PACKAGE這個概念

我們使用了schema來模擬。這是Oracle官方網站里的一個案例,大家可以自己看一下。

17、其他

其他的還有,decode函數,Wm conca為函數,dual,utl_file,dbms_pipe,dbms_output,package,

我們非常推薦使用orafce開源組件,功能挺強大的。

以上是在實際遷移系統過程中,可能會遇到的各種“坑”點,趟過所有這些“坑”,應用才可以完成生存的第一步:活著!

讓應用跑的更好、更快!

在數據庫系統里,我認為有兩個核心是最重要的。一個是事務管理器,還有一個叫做查詢處理器,這兩個其實是構成了一個關系與數據庫的核心。

其中,代價模型是查詢處理器中非常重要的內容,在我們沒辦法對查詢優化器做更多優化的工作時,那我們只能理解系統是怎么來估算執行成本的,那這個對于系統優化也好,SQL優化也好都有非常重要的意義。

(接下來的內容是PostgreSQL中走全表掃描及走索引的成本估算算法的詳細介紹,因內容比較難懂,感興趣的可下載其PPT進行研讀)。

另外一種優化方式

SELECT phone

  FROM lcaddress

 WHERE customerno IN (

          SELECT insuredno

               FROM lcinsured

              WHERE contno = ‘100005522831’

                AND sequenceno = 1

                AND addressno = lcaddress.addressno

          );

這個在Oracle里面只要幾十毫秒,但是POSTGRESQL里面幾十秒才出來。

對SQL進行簡單重寫

SELECT phone

  FROM lcaddress

 WHERE (customerno, addressno) IN (

          SELECT insuredno, addressno

               FROM lcinsured

              WHERE contno = ‘100005522831’

                AND sequenceno = 1

          );

重寫后SQL在Oracle在Plan不變,在PostgreSQL當中的執行計劃,已經與Oracle一致了。所以我們方式就是說以Oracle的執行計劃為藍本來優化SQL。

讓應用跑的更穩定、更安全

PostgreSQL高可用架構,通過PGpool進行數據庫的負載均衡,一主兩備的方式,主庫與備庫間通過stream的方式實現實時同步(配置策略為備庫1和備庫2只要有一個寫完成主庫就返回,防止因為某些原因備庫1\2均不能用時將主庫hang住,提高可用性),這樣在備庫當中任何一個出現問題不會影響主庫的數。

最后一句,備份重如山,對于搞IT的,尤其是做數據庫的,這個要時刻牢記在心,謝謝大家!

老魚,企業級老編一枚,你若有故事,歡迎聯系!

MongoDB 存儲引擎與內部原理

上一篇

96秒100億!如何抗住雙11高并發流量?

下一篇

你也可能喜歡

從Oracle到PostgreSQL,某保險公司遷移實踐

長按儲存圖像,分享給朋友

ITPUB 每周精要將以郵件的形式發放至您的郵箱


微信掃一掃

微信掃一掃
重庆时时后一8码方法 快乐扑克3豹子走势 挂机赚靠什么赚钱 博悦彩票安卓 账号注销还可以赚钱进去吗 海南飞鱼有黑彩吗 湖北十一选五胆拖对照表 福彩欢乐生肖属于重庆地方彩票吗 努力工作赚钱才是我生活 辽宁35选7开奖结果新 河北十一选五害人 江苏时时彩网址 买竞彩赚钱 981游戏中心捕鱼游戏下载 七乐彩复式过滤 贵州十一选五历史遗漏数 吉林十一选五走势图任选走势图