阿里數(shù)據(jù)庫團(tuán)隊(duì)PostgreSQL實(shí)踐課件

阿里數(shù)據(jù)庫團(tuán)隊(duì)PostgreSQL實(shí)踐楊洋洋(陽帥)yang_yang8708@163.com阿里巴巴數(shù)據(jù)庫團(tuán)隊(duì)阿里數(shù)據(jù)庫團(tuán)隊(duì)PostgreSQL實(shí)踐楊洋洋(陽帥)1提綱IDB產(chǎn)品介紹PostgreSQL實(shí)踐原因?qū)嵺`步驟實(shí)踐總結(jié)提綱IDB產(chǎn)品介紹2IDB定位HA備份恢復(fù)性能壓測自動化調(diào)度數(shù)據(jù)質(zhì)量安全審計(jì)監(jiān)控告警安裝配置元數(shù)據(jù)…用戶服務(wù)層數(shù)據(jù)庫服務(wù)平臺(iDB)資源申請數(shù)據(jù)訪問開發(fā)設(shè)計(jì)生產(chǎn)變更分庫分表SQL審核流程管控權(quán)限管控運(yùn)維組件層淘寶天貓支付寶余額寶口碑芝麻信用釘釘1688速賣通高德地圖UC阿里云菜鳥物流…AliSQLMongoDBPostgreSQLOceanBase……Oracle基礎(chǔ)技術(shù)層SQLServerIDB定位HA備份恢復(fù)性能壓測自動化調(diào)度數(shù)據(jù)質(zhì)量安全審計(jì)監(jiān)3IDB是數(shù)據(jù)庫團(tuán)隊(duì)自主研發(fā)的一個(gè)數(shù)據(jù)庫服務(wù)產(chǎn)品，是集團(tuán)去O的重要實(shí)施平臺。先后實(shí)現(xiàn)對Oracle,MySQL,Oceanbase,SqlServer,PostgreSQL等數(shù)據(jù)庫的查詢，數(shù)據(jù)變更，結(jié)構(gòu)變更的支持。并且支持分庫分表的操作。IDB是數(shù)據(jù)庫團(tuán)隊(duì)自主研發(fā)的一個(gè)數(shù)據(jù)庫服務(wù)產(chǎn)品，是集團(tuán)去O的4阿里數(shù)據(jù)庫團(tuán)隊(duì)PostgreSQL實(shí)踐52010年淘寶啟動“去IOE”項(xiàng)目2010年淘寶啟動“去IOE”項(xiàng)目6PostgreSQL實(shí)踐原因

IDB幫助集團(tuán)實(shí)現(xiàn)了去O，但是自己還在使用Oracle。2014年底，這臺Oracle服務(wù)器已經(jīng)集團(tuán)僅剩的幾臺服務(wù)器之一，需要承擔(dān)高昂的License費(fèi)用。去O勢在必行，用什么來替換它呢？MySQL? 需要對應(yīng)用和SQL做大量改造。達(dá)夢? 功能與Oracle兼容，穩(wěn)定性達(dá)不到要求PostgreSQL? 改造成本低，安全穩(wěn)定PostgreSQL實(shí)踐原因

IDB幫助集團(tuán)實(shí)現(xiàn)了去O，但是7實(shí)踐步驟實(shí)踐評估：可行性分析、工作量評估。制定方案：確定遷移的重點(diǎn)和難點(diǎn)，制定遷移方案。應(yīng)用改造：

SQL改造，代碼改造。數(shù)據(jù)遷移：結(jié)構(gòu)遷移，數(shù)據(jù)遷移?；貧w與測試：功能回歸、性能測試。性能調(diào)優(yōu)：針對上線后的性能問題進(jìn)行分析和優(yōu)化。實(shí)踐步驟實(shí)踐評估：可行性分析、工作量評估。8SQL改造時(shí)間間隔Oracle時(shí)間相減得到間隔，單位為天。PG時(shí)間相減得到interval值，需要轉(zhuǎn)換為具體的時(shí)間值。Oracle中sysdate+intervalxxxxunit(precision)類型的數(shù)據(jù)，PG中用now()+‘時(shí)間間隔字符串’的方式來實(shí)現(xiàn)。Now()+‘1day’Now()+‘14400’::interval別名設(shè)置：避免使用關(guān)鍵字role,data,label,type,name作別名，或者加上as關(guān)鍵字SQL改造時(shí)間間隔9SQL改造常量參數(shù)需要進(jìn)行強(qiáng)制類型轉(zhuǎn)換，否則類型則unknown，框架無法自動解析類型。selectid,‘new’::varcharstatusfromt;這樣才能保證應(yīng)用程序中獲取到的status數(shù)據(jù)類型為String。不強(qiáng)制轉(zhuǎn)換獲取到的是Object對象，會導(dǎo)致JavaBean或者DO對象的屬性值設(shè)置失敗。SQL改造常量參數(shù)需要進(jìn)行強(qiáng)制類型轉(zhuǎn)換，否則類型則unkno10SQL改造函數(shù)替換：將Oracle部分PG不支持的函數(shù)替換為PG的等價(jià)函數(shù)。分組合并：Oracle中wm_concat(xxx)轉(zhuǎn)換成PG中的string_agg(xxx,‘,’)over(partitionbyXoderbyY)來實(shí)現(xiàn)。Oracle中的regexp_replace(xxx,reg)在PG需要加上第三個(gè)參數(shù)“,”Bitand(A,B)

：替換成運(yùn)算符&,A&Bsysdate：now,current_timestampNVL：coalesceDECODE：casewhenthenelseendSQL改造函數(shù)替換：將Oracle部分PG不支持的函數(shù)替換為11SQL改造OracleMergeInto插入或者更新的SQL改造PostgreSQL需要聯(lián)合使用with查詢,updatereturning,insert來進(jìn)行選擇性更新或者插入。SQL改造OracleMergeInto插入或者更新的12withdataas(select#id#::integerid,#name#::varcharname,#status#::varcharstatus),

upsertas(updatemy_userdstsetname=,gmt_modified=now(),status=d.statusfromdatadwhereisnotnullandd.id=dst.idreturningdst.*)

insertintomy_user(id,name,status) selectnextVal('seq_my_user'),,d.status fromdatad wherenotexists(select1frommy_userdstwheredst.id=d.id)withdataas(select#id#::int13SQL改造整數(shù)參數(shù)傳入空字符串的處理使用#id#::numeric時(shí)，程序傳遞空字符串’’作為參數(shù)，則會出現(xiàn)invalidinputsyntaxfortypenumeric錯(cuò)誤需要使用to_number(#param#)（EDB）Select*fromtwherestatus=‘new’andpid=to_number(#param#)SQL改造整數(shù)參數(shù)傳入空字符串的處理使用#id#::nu14SQL改造分頁OracleSELECT*from(SELECTrownumASrn,t.*FROMtWHEREcondition ANDrownum<={end})wherern>={start}PGSELECT*fromtwhereconditionoffset{start}limit{pagesize}SQL改造分頁15SQL改造部分?jǐn)?shù)據(jù)類型轉(zhuǎn)換Oracle布爾值char(1)‘Y’/’N’轉(zhuǎn)換成PG類型booleanClob大字段類型轉(zhuǎn)換成PG類型varchar虛擬列增加實(shí)際列或者使用視圖Oracle\u0000字符PG不允許存儲，過濾掉再保存修改字段類型時(shí)可以使用USING{表達(dá)式}進(jìn)行字段值轉(zhuǎn)換如altertableuseraltercolumnis_deletedtypebooleanUSINGcaseis_deletedwhen'Y'thentrueelsefalseend;altertableuseraltercolumnis_deletedtypebooleanUSINGis_deleted='Y';SQL改造部分?jǐn)?shù)據(jù)類型轉(zhuǎn)換16SQL改造遞歸查詢STARTWITH

CONNECTBY轉(zhuǎn)換成WITHRECURSIVE查詢例如select

*from

empstartwith

empno=7connect

by

mgr=prior

empno;轉(zhuǎn)換成PG的遞歸SQL：withrecursiver_empras(selecta.id,,a.pidfromempawhereid=1001unionallselectb.id,,b.pidfromemp

binnerjoinronr.id=b.pid)selectid,namefromrSQL改造遞歸查詢17應(yīng)用程序改造字段名的大小轉(zhuǎn)換與映射問題。Oracle字段名默認(rèn)大寫，PG字段名默認(rèn)小寫。全部大寫大小寫混合區(qū)分大小寫區(qū)分大小寫SQL字段名USER_NAMEUser_Age“User_Id”“use_desc”O(jiān)racle得到的元數(shù)據(jù)USER_NAMEUSER_AGEUser_Iduse_descPG得到的元數(shù)據(jù)user_nameuser_ageUser_Iduse_desc應(yīng)用程序改造字段名的大小轉(zhuǎn)換與映射問題。全部大寫大小寫混合區(qū)18應(yīng)用程序改造Oracle與PG元數(shù)據(jù)大小寫差異解決辦法批量修改SQL文件，為字段加入大寫別名（操作性差，工作量大）Selectuser_id“USER_ID”fromtwhere…;批量修改代碼，將程序中獲取數(shù)據(jù)值時(shí)將字段名小寫。（操作性差，工作量大）row.getInt(“user_id”)…;應(yīng)用程序改造Oracle與PG元數(shù)據(jù)大小寫差異解決辦法19應(yīng)用程序改造OraclePG默認(rèn)元數(shù)據(jù)大小寫差異解決辦法持久層框架改造，修改數(shù)據(jù)庫字段到JavaBean的映射方法(Mapping)SQL：selectuser_namefromtwhereuser_id=?Oracle元數(shù)據(jù)：USER_NAME程序獲取數(shù)據(jù)：row.get(“USER_NAME”)PG元數(shù)據(jù)：user_name，原來的程序無法獲取到數(shù)據(jù)：SQL:user_name->PG:user_name->框架轉(zhuǎn)換：”USER_NAME”->原來的程序正常獲取到數(shù)據(jù)。應(yīng)用程序改造OraclePG默認(rèn)元數(shù)據(jù)大小寫差異解決辦法20應(yīng)用程序改造持久層框架改造改造示例：rsmd＝ResultSet.getMetaData()for(inti=0,n=rsmd.getColumnCount();i<n;i++){StringcolumnName=rsmd.getColumnName(i+1);if(delegate.isUseColumnLabel()){StringcolumnLabel=rsmd.getColumnLabel(i+1);

if(isPostgreSQL){if(columnLabel.equals(columnLabel.toLowerCase())){//小寫的字段名直接改大寫。

columnName=columnLabel.toUpperCase();}else{

columnName=columnLabel;

//”user_ID”大小寫敏感字段保留

}}else{columnName=columnLabel;}}應(yīng)用程序改造持久層框架改造改造示例：21應(yīng)用程序改造游標(biāo)/流式數(shù)據(jù)處理需要開啟事物PG-JDBC驅(qū)動默認(rèn)加載所有行，如果結(jié)果集大會造成應(yīng)用(JAVA)內(nèi)存溢出(OutOfMemory)使用游標(biāo)或者設(shè)置fetchSize需要開啟事物應(yīng)用程序改造游標(biāo)/流式數(shù)據(jù)處理需要開啟事物22應(yīng)用程序改造配合表數(shù)據(jù)類型的改造進(jìn)行修改。javaBean.setDeleted(“Y”.equals(row.getString(‘IS_DELETE’))javaBean.setDeleted(row.getBoolean(‘IS_DELETE’))通知下游數(shù)據(jù)使用方進(jìn)行程序改造。應(yīng)用程序改造配合表數(shù)據(jù)類型的改造進(jìn)行修改。23性能優(yōu)化模糊查詢：如果沒有修改PG庫的locale，使用like’abc%’查詢時(shí)，默認(rèn)會掃描所有行，即使有索引也不走索引，引發(fā)性能問題。原因是要查詢的數(shù)據(jù)類型和索引的數(shù)據(jù)類型不匹配。解決方式：重建索引，為索引列指定pattern_ops模式，如varchar_pattern_ops。

createindexidx_t_name

ont(name varchar_pattern_ops);性能優(yōu)化模糊查詢：24性能優(yōu)化避免長事物讀取大量數(shù)據(jù)需要使用事物來防止溢出，但是使用長事物可能造成性能問題。長事物會導(dǎo)致vacuum進(jìn)程無法回收已經(jīng)刪除數(shù)據(jù)的存儲空間，新的數(shù)據(jù)寫入只能使用新的數(shù)據(jù)塊上，導(dǎo)致磁盤空間持續(xù)增長。解決辦法：數(shù)據(jù)庫上監(jiān)控長事物程序上排查長事物產(chǎn)生的原因并進(jìn)行修復(fù)性能優(yōu)化避免長事物25性能優(yōu)化受長事務(wù)影響膨脹的表的處理小表可使用vacuumfull來處理。大表使用pg_reorg來進(jìn)行在線空間收縮，不鎖表，不影響業(yè)務(wù)。性能優(yōu)化受長事務(wù)影響膨脹的表的處理26性能優(yōu)化分頁排序優(yōu)化部分Oracle的復(fù)雜SQL使用到PG上會產(chǎn)生性能問題，多層子查詢只在最外層排序分頁的時(shí)候性能影響明顯，盡量在子查詢里進(jìn)行關(guān)聯(lián)，過濾，分頁。當(dāng)表和子查詢多時(shí)，表的join順序沒有Oracle優(yōu)化得好，可能會走錯(cuò)索引，所以盡量避免子查詢，使用join來做。性能優(yōu)化分頁排序優(yōu)化27數(shù)據(jù)遷移確定遷移工具制定遷移方案制定遷移腳本，開始遷移驗(yàn)證遷移后的數(shù)據(jù)正確性數(shù)據(jù)遷移確定遷移工具28數(shù)據(jù)遷移工具選擇MTKOra2PG，DBConvertDSQL(分布式數(shù)據(jù)庫SQL引擎),DTS（阿里云的數(shù)據(jù)遷移服務(wù)），數(shù)據(jù)遷移工具選擇29數(shù)據(jù)遷移制定遷移方案：MTK+DSQL(分布式數(shù)據(jù)庫SQL引擎)MTK遷移任務(wù)可以并行到表級別DSQL遷移任務(wù)可以并行到行級別，解決大表遷移時(shí)間長的問題。重要的數(shù)據(jù)使用觸發(fā)器記錄變更，全量遷移完成之后再做增量遷移數(shù)據(jù)遷移制定遷移方案：MTK+DSQL(分布式數(shù)據(jù)庫SQ30數(shù)據(jù)遷移制定遷移腳本元數(shù)據(jù)遷移Schema,表結(jié)構(gòu)，視圖，序列，……制定數(shù)據(jù)遷移任務(wù)腳本大表單獨(dú)使用任務(wù)優(yōu)先遷移如任務(wù)一：runMTK.sht1&

任務(wù)二：runMTK.sht2&小表批量遷移如任務(wù)三：runMTK.sht7,t8,t9,t10&數(shù)據(jù)遷移制定遷移腳本31數(shù)據(jù)遷移先建立索引再進(jìn)行數(shù)據(jù)遷移建立索引比較耗時(shí)，每個(gè)索引創(chuàng)建時(shí)間與接近遷移一次數(shù)據(jù)時(shí)間一個(gè)帶7個(gè)索引的樣本表進(jìn)行測試樣本（21G/）同步數(shù)據(jù)時(shí)間(分鐘)創(chuàng)建索引時(shí)間(分鐘)CPU總耗時(shí)先數(shù)據(jù)后索引111306%(<60%)/4%(100%)>2H先索引后數(shù)據(jù)（多線程寫）33014%(<70%)<40數(shù)據(jù)遷移先建立索引再進(jìn)行數(shù)據(jù)遷移樣本（21G/）同步數(shù)據(jù)時(shí)間32遷移性能我們進(jìn)行了3輪演練，比較性能，進(jìn)行方案改進(jìn)遷移性能我們進(jìn)行了3輪演練，比較性能，進(jìn)行方案改進(jìn)33開始遷移400+G的數(shù)據(jù)，分兩批遷移，實(shí)時(shí)性要求不高的表先遷移實(shí)時(shí)性，一致性要求高的表后遷移，200G數(shù)據(jù)耗時(shí)約1個(gè)小時(shí)。開始遷移400+G的數(shù)據(jù)，分兩批遷移，34應(yīng)用數(shù)據(jù)源切換啟動改造后的應(yīng)用代碼，使用PG數(shù)據(jù)源應(yīng)用功能回歸。應(yīng)用性能測試。根據(jù)運(yùn)行情況進(jìn)行應(yīng)用代碼，應(yīng)用SQL與數(shù)據(jù)庫優(yōu)化。應(yīng)用數(shù)據(jù)源切換啟動改造后的應(yīng)用代碼，使用PG數(shù)據(jù)源35阿里數(shù)據(jù)庫團(tuán)隊(duì)PostgreSQL實(shí)踐36周邊設(shè)施建設(shè)主備復(fù)制跨機(jī)房一主一備高可用性監(jiān)控AliMonitor備份恢復(fù)方案每天一全備WAL日志增量備份備份壓縮同步到OSS保存如果發(fā)生故障，由OSS拉取全備數(shù)據(jù)恢復(fù)，再應(yīng)用增量數(shù)據(jù)，恢復(fù)服務(wù)。周邊設(shè)施建設(shè)主備復(fù)制37Oracle切換到PG的實(shí)踐總結(jié)可行性調(diào)研相比其他數(shù)據(jù)庫，PostgreSQL更適合成為Oracle替代品。研發(fā)成本從調(diào)研到落地時(shí)間少于1個(gè)月。投入研發(fā)資源2人，DBA資源1人。9%以下的SQL改造5%以下的應(yīng)用代碼改造改動的代碼和SQL中，80%以上改造類型相同機(jī)器資源配置與原Oracle相同，性能經(jīng)過優(yōu)化能滿足應(yīng)用需求改造SQL修改總量SELECT76820UPDATE11142Oracle切換到PG的實(shí)踐總結(jié)可行性調(diào)研改造SQL修改總量38目前單機(jī)單節(jié)點(diǎn)能滿足我們應(yīng)用需求TPS500時(shí)CPU占用率僅5%目前單機(jī)單節(jié)點(diǎn)能滿足我們應(yīng)用需求39PG實(shí)踐總結(jié)風(fēng)險(xiǎn)及穩(wěn)定性風(fēng)險(xiǎn)可控穩(wěn)定從上線到現(xiàn)在1年半只出現(xiàn)了一次停服狀態(tài)，原因是大量持續(xù)寫入，且WAL日志未及時(shí)遷移走，導(dǎo)致磁盤被寫滿。擴(kuò)容后立即恢復(fù)了服務(wù)。沒有出現(xiàn)數(shù)據(jù)丟失PG實(shí)踐總結(jié)風(fēng)險(xiǎn)及穩(wěn)定性40誠邀入伙數(shù)據(jù)庫內(nèi)核研發(fā)專家Java研發(fā)專家數(shù)據(jù)庫架構(gòu)師3年以上工作經(jīng)驗(yàn)，數(shù)據(jù)庫與云計(jì)算感興趣發(fā)送簡歷到：zhengsheng.yezs@微博：葉正盛_yzsind誠邀入伙數(shù)據(jù)庫內(nèi)核研發(fā)專家41阿里數(shù)據(jù)庫團(tuán)隊(duì)PostgreSQL實(shí)踐楊洋洋(陽帥)yang_yang8708@163.com阿里巴巴數(shù)據(jù)庫團(tuán)隊(duì)阿里數(shù)據(jù)庫團(tuán)隊(duì)PostgreSQL實(shí)踐楊洋洋(陽帥)42提綱IDB產(chǎn)品介紹PostgreSQL實(shí)踐原因?qū)嵺`步驟實(shí)踐總結(jié)提綱IDB產(chǎn)品介紹43IDB定位HA備份恢復(fù)性能壓測自動化調(diào)度數(shù)據(jù)質(zhì)量安全審計(jì)監(jiān)控告警安裝配置元數(shù)據(jù)…用戶服務(wù)層數(shù)據(jù)庫服務(wù)平臺(iDB)資源申請數(shù)據(jù)訪問開發(fā)設(shè)計(jì)生產(chǎn)變更分庫分表SQL審核流程管控權(quán)限管控運(yùn)維組件層淘寶天貓支付寶余額寶口碑芝麻信用釘釘1688速賣通高德地圖UC阿里云菜鳥物流…AliSQLMongoDBPostgreSQLOceanBase……Oracle基礎(chǔ)技術(shù)層SQLServerIDB定位HA備份恢復(fù)性能壓測自動化調(diào)度數(shù)據(jù)質(zhì)量安全審計(jì)監(jiān)44IDB是數(shù)據(jù)庫團(tuán)隊(duì)自主研發(fā)的一個(gè)數(shù)據(jù)庫服務(wù)產(chǎn)品，是集團(tuán)去O的重要實(shí)施平臺。先后實(shí)現(xiàn)對Oracle,MySQL,Oceanbase,SqlServer,PostgreSQL等數(shù)據(jù)庫的查詢，數(shù)據(jù)變更，結(jié)構(gòu)變更的支持。并且支持分庫分表的操作。IDB是數(shù)據(jù)庫團(tuán)隊(duì)自主研發(fā)的一個(gè)數(shù)據(jù)庫服務(wù)產(chǎn)品，是集團(tuán)去O的45阿里數(shù)據(jù)庫團(tuán)隊(duì)PostgreSQL實(shí)踐462010年淘寶啟動“去IOE”項(xiàng)目2010年淘寶啟動“去IOE”項(xiàng)目47PostgreSQL實(shí)踐原因

IDB幫助集團(tuán)實(shí)現(xiàn)了去O，但是自己還在使用Oracle。2014年底，這臺Oracle服務(wù)器已經(jīng)集團(tuán)僅剩的幾臺服務(wù)器之一，需要承擔(dān)高昂的License費(fèi)用。去O勢在必行，用什么來替換它呢？MySQL? 需要對應(yīng)用和SQL做大量改造。達(dá)夢? 功能與Oracle兼容，穩(wěn)定性達(dá)不到要求PostgreSQL? 改造成本低，安全穩(wěn)定PostgreSQL實(shí)踐原因

IDB幫助集團(tuán)實(shí)現(xiàn)了去O，但是48實(shí)踐步驟實(shí)踐評估：可行性分析、工作量評估。制定方案：確定遷移的重點(diǎn)和難點(diǎn)，制定遷移方案。應(yīng)用改造：

SQL改造，代碼改造。數(shù)據(jù)遷移：結(jié)構(gòu)遷移，數(shù)據(jù)遷移?；貧w與測試：功能回歸、性能測試。性能調(diào)優(yōu)：針對上線后的性能問題進(jìn)行分析和優(yōu)化。實(shí)踐步驟實(shí)踐評估：可行性分析、工作量評估。49SQL改造時(shí)間間隔Oracle時(shí)間相減得到間隔，單位為天。PG時(shí)間相減得到interval值，需要轉(zhuǎn)換為具體的時(shí)間值。Oracle中sysdate+intervalxxxxunit(precision)類型的數(shù)據(jù)，PG中用now()+‘時(shí)間間隔字符串’的方式來實(shí)現(xiàn)。Now()+‘1day’Now()+‘14400’::interval別名設(shè)置：避免使用關(guān)鍵字role,data,label,type,name作別名，或者加上as關(guān)鍵字SQL改造時(shí)間間隔50SQL改造常量參數(shù)需要進(jìn)行強(qiáng)制類型轉(zhuǎn)換，否則類型則unknown，框架無法自動解析類型。selectid,‘new’::varcharstatusfromt;這樣才能保證應(yīng)用程序中獲取到的status數(shù)據(jù)類型為String。不強(qiáng)制轉(zhuǎn)換獲取到的是Object對象，會導(dǎo)致JavaBean或者DO對象的屬性值設(shè)置失敗。SQL改造常量參數(shù)需要進(jìn)行強(qiáng)制類型轉(zhuǎn)換，否則類型則unkno51SQL改造函數(shù)替換：將Oracle部分PG不支持的函數(shù)替換為PG的等價(jià)函數(shù)。分組合并：Oracle中wm_concat(xxx)轉(zhuǎn)換成PG中的string_agg(xxx,‘,’)over(partitionbyXoderbyY)來實(shí)現(xiàn)。Oracle中的regexp_replace(xxx,reg)在PG需要加上第三個(gè)參數(shù)“,”Bitand(A,B)

：替換成運(yùn)算符&,A&Bsysdate：now,current_timestampNVL：coalesceDECODE：casewhenthenelseendSQL改造函數(shù)替換：將Oracle部分PG不支持的函數(shù)替換為52SQL改造OracleMergeInto插入或者更新的SQL改造PostgreSQL需要聯(lián)合使用with查詢,updatereturning,insert來進(jìn)行選擇性更新或者插入。SQL改造OracleMergeInto插入或者更新的53withdataas(select#id#::integerid,#name#::varcharname,#status#::varcharstatus),

upsertas(updatemy_userdstsetname=,gmt_modified=now(),status=d.statusfromdatadwhereisnotnullandd.id=dst.idreturningdst.*)

insertintomy_user(id,name,status) selectnextVal('seq_my_user'),,d.status fromdatad wherenotexists(select1frommy_userdstwheredst.id=d.id)withdataas(select#id#::int54SQL改造整數(shù)參數(shù)傳入空字符串的處理使用#id#::numeric時(shí)，程序傳遞空字符串’’作為參數(shù)，則會出現(xiàn)invalidinputsyntaxfortypenumeric錯(cuò)誤需要使用to_number(#param#)（EDB）Select*fromtwherestatus=‘new’andpid=to_number(#param#)SQL改造整數(shù)參數(shù)傳入空字符串的處理使用#id#::nu55SQL改造分頁OracleSELECT*from(SELECTrownumASrn,t.*FROMtWHEREcondition ANDrownum<={end})wherern>={start}PGSELECT*fromtwhereconditionoffset{start}limit{pagesize}SQL改造分頁56SQL改造部分?jǐn)?shù)據(jù)類型轉(zhuǎn)換Oracle布爾值char(1)‘Y’/’N’轉(zhuǎn)換成PG類型booleanClob大字段類型轉(zhuǎn)換成PG類型varchar虛擬列增加實(shí)際列或者使用視圖Oracle\u0000字符PG不允許存儲，過濾掉再保存修改字段類型時(shí)可以使用USING{表達(dá)式}進(jìn)行字段值轉(zhuǎn)換如altertableuseraltercolumnis_deletedtypebooleanUSINGcaseis_deletedwhen'Y'thentrueelsefalseend;altertableuseraltercolumnis_deletedtypebooleanUSINGis_deleted='Y';SQL改造部分?jǐn)?shù)據(jù)類型轉(zhuǎn)換57SQL改造遞歸查詢STARTWITH

CONNECTBY轉(zhuǎn)換成WITHRECURSIVE查詢例如select

*from

empstartwith

empno=7connect

by

mgr=prior

empno;轉(zhuǎn)換成PG的遞歸SQL：withrecursiver_empras(selecta.id,,a.pidfromempawhereid=1001unionallselectb.id,,b.pidfromemp

binnerjoinronr.id=b.pid)selectid,namefromrSQL改造遞歸查詢58應(yīng)用程序改造字段名的大小轉(zhuǎn)換與映射問題。Oracle字段名默認(rèn)大寫，PG字段名默認(rèn)小寫。全部大寫大小寫混合區(qū)分大小寫區(qū)分大小寫SQL字段名USER_NAMEUser_Age“User_Id”“use_desc”O(jiān)racle得到的元數(shù)據(jù)USER_NAMEUSER_AGEUser_Iduse_descPG得到的元數(shù)據(jù)user_nameuser_ageUser_Iduse_desc應(yīng)用程序改造字段名的大小轉(zhuǎn)換與映射問題。全部大寫大小寫混合區(qū)59應(yīng)用程序改造Oracle與PG元數(shù)據(jù)大小寫差異解決辦法批量修改SQL文件，為字段加入大寫別名（操作性差，工作量大）Selectuser_id“USER_ID”fromtwhere…;批量修改代碼，將程序中獲取數(shù)據(jù)值時(shí)將字段名小寫。（操作性差，工作量大）row.getInt(“user_id”)…;應(yīng)用程序改造Oracle與PG元數(shù)據(jù)大小寫差異解決辦法60應(yīng)用程序改造OraclePG默認(rèn)元數(shù)據(jù)大小寫差異解決辦法持久層框架改造，修改數(shù)據(jù)庫字段到JavaBean的映射方法(Mapping)SQL：selectuser_namefromtwhereuser_id=?Oracle元數(shù)據(jù)：USER_NAME程序獲取數(shù)據(jù)：row.get(“USER_NAME”)PG元數(shù)據(jù)：user_name，原來的程序無法獲取到數(shù)據(jù)：SQL:user_name->PG:user_name->框架轉(zhuǎn)換：”USER_NAME”->原來的程序正常獲取到數(shù)據(jù)。應(yīng)用程序改造OraclePG默認(rèn)元數(shù)據(jù)大小寫差異解決辦法61應(yīng)用程序改造持久層框架改造改造示例：rsmd＝ResultSet.getMetaData()for(inti=0,n=rsmd.getColumnCount();i<n;i++){StringcolumnName=rsmd.getColumnName(i+1);if(delegate.isUseColumnLabel()){StringcolumnLabel=rsmd.getColumnLabel(i+1);

if(isPostgreSQL){if(columnLabel.equals(columnLabel.toLowerCase())){//小寫的字段名直接改大寫。

columnName=columnLabel.toUpperCase();}else{

columnName=columnLabel;

//”user_ID”大小寫敏感字段保留

}}else{columnName=columnLabel;}}應(yīng)用程序改造持久層框架改造改造示例：62應(yīng)用程序改造游標(biāo)/流式數(shù)據(jù)處理需要開啟事物PG-JDBC驅(qū)動默認(rèn)加載所有行，如果結(jié)果集大會造成應(yīng)用(JAVA)內(nèi)存溢出(OutOfMemory)使用游標(biāo)或者設(shè)置fetchSize需要開啟事物應(yīng)用程序改造游標(biāo)/流式數(shù)據(jù)處理需要開啟事物63應(yīng)用程序改造配合表數(shù)據(jù)類型的改造進(jìn)行修改。javaBean.setDeleted(“Y”.equals(row.getString(‘IS_DELETE’))javaBean.setDeleted(row.getBoolean(‘IS_DELETE’))通知下游數(shù)據(jù)使用方進(jìn)行程序改造。應(yīng)用程序改造配合表數(shù)據(jù)類型的改造進(jìn)行修改。64性能優(yōu)化模糊查詢：如果沒有修改PG庫的locale，使用like’abc%’查詢時(shí)，默認(rèn)會掃描所有行，即使有索引也不走索引，引發(fā)性能問題。原因是要查詢的數(shù)據(jù)類型和索引的數(shù)據(jù)類型不匹配。解決方式：重建索引，為索引列指定pattern_ops模式，如varchar_pattern_ops。

createindexidx_t_name

ont(name varchar_pattern_ops);性能優(yōu)化模糊查詢：65性能優(yōu)化避免長事物讀取大量數(shù)據(jù)需要使用事物來防止溢出，但是使用長事物可能造成性能問題。長事物會導(dǎo)致vacuum進(jìn)程無法回收已經(jīng)刪除數(shù)據(jù)的存儲空間，新的數(shù)據(jù)寫入只能使用新的數(shù)據(jù)塊上，導(dǎo)致磁盤空間持續(xù)增長。解決辦法：數(shù)據(jù)庫上監(jiān)控長事物程序上排查長事物產(chǎn)生的原因并進(jìn)行修復(fù)性能優(yōu)化避免長事物66性能優(yōu)化受長事務(wù)影響膨脹的表的處理小表可使用vacuumfull來處理。大表使用pg_reorg來進(jìn)行在線空間收縮，不鎖表，不影響業(yè)務(wù)。性能優(yōu)化受長事務(wù)影響膨脹的表的處理67性能優(yōu)化分頁排序優(yōu)化部分Oracle的復(fù)雜SQL使用到PG上會產(chǎn)生性能問題，多層子查詢只在最外層排序分頁的時(shí)候性能影響明顯，盡量在子查詢里進(jìn)行關(guān)聯(lián)，過濾，分頁。當(dāng)表和子查詢多時(shí)，表的join順序沒有Oracle優(yōu)化得好，可能會走錯(cuò)索引，所以盡量避免子查詢，使用join來做。性能優(yōu)化分頁排序優(yōu)化68數(shù)據(jù)遷移確定遷移工具制定遷移方案制定遷移腳本，開始遷移驗(yàn)證遷移后的數(shù)據(jù)正確性數(shù)據(jù)遷移確定遷移工具69數(shù)據(jù)遷