戰(zhàn)：低業(yè)務(wù)中斷、可校驗(yàn)、可回退的完整方案)
搞Oracle到PostgreSQL遷移這件事圈子里聊得最多的不是工具選哪個(gè)而是怎么讓業(yè)務(wù)別停太久、數(shù)據(jù)到底搬對(duì)沒(méi)有、出了問(wèn)題能不能跑回去這三個(gè)靈魂拷問(wèn)。我最近完整跟完了一個(gè)核心交易系統(tǒng)的遷移項(xiàng)目正好把整套打法梳理一遍給準(zhǔn)備上車的朋友當(dāng)個(gè)參考。先說(shuō)結(jié)論Oracle遷PostgreSQL難點(diǎn)從來(lái)不在怎么導(dǎo)數(shù)據(jù)而在遷移鏈條的整體設(shè)計(jì)。你只有把低業(yè)務(wù)中斷、可校驗(yàn)、可回退當(dāng)成一個(gè)整體目標(biāo)來(lái)拆解才能避免那種數(shù)據(jù)導(dǎo)完了、業(yè)務(wù)起不來(lái)、回也回不去的尷尬局面。1. 遷移項(xiàng)目啟動(dòng)前先想清楚怎么切再動(dòng)手1.1 遷移背景與目標(biāo)拆解我們這個(gè)項(xiàng)目是從Oracle 11g遷到PostgreSQL 14源庫(kù)大概有3TB數(shù)據(jù)核心業(yè)務(wù)表最大的一張超過(guò)8億行還有不少用到了CLOB、分區(qū)、存儲(chǔ)過(guò)程的老業(yè)務(wù)。業(yè)務(wù)側(cè)給的要求很明確停機(jī)窗口最多4小時(shí)切換后一個(gè)月內(nèi)如果發(fā)現(xiàn)重大問(wèn)題必須能回退并且遷移完成后要拿出讓業(yè)務(wù)信服的數(shù)據(jù)一致性證明。這三條要求翻譯成技術(shù)語(yǔ)言就是低業(yè)務(wù)中斷不能搞先停庫(kù)全量導(dǎo)出再全量導(dǎo)入的老套路必須走全量增量同步的雙軌方案把真正的停機(jī)時(shí)間壓縮到只需要處理增量追趕和切換驗(yàn)證這兩個(gè)環(huán)節(jié)。可校驗(yàn)遷移完成后要用行數(shù)、校驗(yàn)和、抽樣比對(duì)等手段證明數(shù)據(jù)沒(méi)丟、沒(méi)多、沒(méi)錯(cuò)。不是拿兩條SQL對(duì)一下數(shù)量就完事而是要能經(jīng)得起業(yè)務(wù)和審計(jì)的追問(wèn)?？苫赝饲袚Q后保留完整的回退通道包括源庫(kù)持續(xù)運(yùn)行、反向同步機(jī)制、回退演練預(yù)案。一旦業(yè)務(wù)驗(yàn)證不通過(guò)能在一個(gè)小時(shí)內(nèi)切回Oracle且不丟新增數(shù)據(jù)。這三個(gè)目標(biāo)不是并列關(guān)系而是互相牽制的。比如為了可回退你可能需要在一段時(shí)間內(nèi)同時(shí)維護(hù)兩套庫(kù)的寫入為了低中斷你的增量同步工具必須足夠穩(wěn)定否則追趕不上就會(huì)無(wú)限拉長(zhǎng)停機(jī)窗口為了可校驗(yàn)又必須在遷移過(guò)程中保留足夠的審計(jì)痕跡方便回退時(shí)對(duì)賬。1.2 遷移前的源庫(kù)調(diào)研與風(fēng)險(xiǎn)清單很多項(xiàng)目死在第一步?jīng)]搞清楚源庫(kù)里到底有什么就開始動(dòng)手。我建議遷移前至少花一到兩周做一次徹底的源庫(kù)體檢輸出一份風(fēng)險(xiǎn)清單。我們當(dāng)時(shí)重點(diǎn)排查了以下幾類東西對(duì)象清單表、索引、約束、序列、視圖、物化視圖、存儲(chǔ)過(guò)程、函數(shù)、包、觸發(fā)器、同義詞、DB Link。每一項(xiàng)都要統(tǒng)計(jì)數(shù)量并評(píng)估改造工作量。千萬(wàn)別小看同義詞和DB Link業(yè)務(wù)SQL里如果大量使用遷移后全都要改。SQL使用情況抓取AWR報(bào)告和近一段時(shí)間的慢SQL重點(diǎn)看用了哪些Oracle獨(dú)有語(yǔ)法比如CONNECT BY層次查詢、()外連接、ROWNUM分頁(yè)、NVL、SYSDATE、MERGE、LISTAGG等。這些是SQL改造的重災(zāi)區(qū)。數(shù)據(jù)類型分布NUMBER、VARCHAR2、DATE、CLOB、BLOB、LONG、RAW、ROWID等類型的字段數(shù)量和最大長(zhǎng)度。尤其是CLOB和LONG遷移后要用TEXT替代但業(yè)務(wù)代碼里對(duì)CLOB的操作方式可能需要調(diào)整。存儲(chǔ)過(guò)程復(fù)雜度統(tǒng)計(jì)有多少存儲(chǔ)過(guò)程、總代碼行數(shù)、用到了哪些Oracle獨(dú)有包DBMS_SQL、DBMS_JOB、DBMS_ALERT、UTL_FILE等。這部分改造成本往往被嚴(yán)重低估。我們項(xiàng)目里有1300多個(gè)存儲(chǔ)過(guò)程最終花了整個(gè)項(xiàng)目40%的工時(shí)在改寫上面。依賴關(guān)系應(yīng)用與數(shù)據(jù)庫(kù)的交互方式包括連接方式JDBC、OCI、ODBC、事務(wù)隔離級(jí)別、會(huì)話級(jí)參數(shù)設(shè)置、臨時(shí)表使用情況。應(yīng)用層的連接串、驅(qū)動(dòng)、SQL寫法都要納入改造范圍。體檢報(bào)告出來(lái)后要讓業(yè)務(wù)方、DBA、開發(fā)三方一起評(píng)審明確哪些東西可以原樣遷哪些必須改造遷哪些是僵尸對(duì)象可以果斷拋棄。這一步做扎實(shí)了后面所有的方案才有依據(jù)。2. 低業(yè)務(wù)中斷的核心打法全量增量雙軌遷移2.1 遷移工具選型與取舍工具選型沒(méi)有標(biāo)準(zhǔn)答案核心看你的源庫(kù)版本、目標(biāo)庫(kù)版本、數(shù)據(jù)總量、同步實(shí)時(shí)性要求和團(tuán)隊(duì)熟悉度。我們當(dāng)時(shí)對(duì)比了三條路線Ora2Pg開源免費(fèi)Perl寫的能把表結(jié)構(gòu)、數(shù)據(jù)、序列、視圖、存儲(chǔ)過(guò)程整體轉(zhuǎn)換支持直接連Oracle導(dǎo)出再導(dǎo)入PostgreSQL。優(yōu)點(diǎn)是自動(dòng)化程度高缺點(diǎn)是大表導(dǎo)出用COPY方式對(duì)源庫(kù)有一定壓力而且存儲(chǔ)過(guò)程轉(zhuǎn)換質(zhì)量一般只能轉(zhuǎn)個(gè)骨架復(fù)雜邏輯基本還得人工改。pgloader同樣開源從Oracle讀數(shù)據(jù)到PostgreSQL非常快用的也是COPY協(xié)議支持并行加載。但DDL遷移能力弱主要用來(lái)搬數(shù)據(jù)。自研抽取同步腳本用Python寫一套基于JDBC的全量導(dǎo)出和增量抽取腳本配合調(diào)度平臺(tái)實(shí)現(xiàn)斷點(diǎn)續(xù)傳和數(shù)據(jù)校驗(yàn)。靈活性最高但開發(fā)量不小。我們最終采用了Ora2Pg轉(zhuǎn)結(jié)構(gòu)pgloader搬全量自研增量同步腳本Debezium做變更捕獲的組合方案。結(jié)構(gòu)轉(zhuǎn)換用Ora2Pg生成初始DDL再人工修全量數(shù)據(jù)用pgloader并行加載增量部分考慮到源庫(kù)是Oracle 11gDebezium的Oracle插件要裝額外組件最后是自己寫了一套基于日志的增量抽取程序。這里給個(gè)建議如果是Oracle 12c及以上Debezium是較好的增量同步方案社區(qū)活躍且支持?jǐn)帱c(diǎn)續(xù)傳11g就得掂量掂量了。2.2 全量遷移的并行與限速策略全量數(shù)據(jù)遷移最容易翻車的地方是導(dǎo)太猛把源庫(kù)IO打滿業(yè)務(wù)直接告警導(dǎo)太慢又趕不上停機(jī)窗口。所以并行度和限速必須提前設(shè)計(jì)。我們按表維度拆分任務(wù)用了一個(gè)簡(jiǎn)單的規(guī)則小于100萬(wàn)行的表單表單任務(wù)順序執(zhí)行100萬(wàn)到1000萬(wàn)行的表按主鍵范圍拆成4到8個(gè)分片并行超過(guò)1000萬(wàn)行的表按主鍵范圍拆成16到32個(gè)分片每個(gè)分片獨(dú)立連接、獨(dú)立事務(wù)。同時(shí)設(shè)置了全局并發(fā)上限保證源庫(kù)的活躍會(huì)話數(shù)不超過(guò)20。pgloader本身有個(gè)batch concurrency參數(shù)可以控制并行度但實(shí)測(cè)下來(lái)對(duì)Oracle源庫(kù)支持一般所以我更推薦自己寫一個(gè)簡(jiǎn)單的調(diào)度器用一張任務(wù)表記錄每個(gè)分片的狀態(tài)調(diào)度線程從里面撈待執(zhí)行的任務(wù)分發(fā)給工作線程工作線程處理完更新狀態(tài)出錯(cuò)自動(dòng)重試三次還是失敗就進(jìn)入人工處理隊(duì)列。這里有個(gè)非常關(guān)鍵的細(xì)節(jié)全量遷移必須在增量同步啟動(dòng)之前完成并且要記錄全量導(dǎo)出的數(shù)據(jù)快照時(shí)間點(diǎn)。我們的做法是全量開始前先在源庫(kù)開啟歸檔日志和補(bǔ)充日志然后啟動(dòng)增量抽取程序?qū)崟r(shí)解析日志寫入Kafka全量導(dǎo)出的每張表在導(dǎo)出前記錄一個(gè)SCNSystem Change Number快照導(dǎo)出完成后用這個(gè)SCN到增量同步里做數(shù)據(jù)拼接。這樣才能保證全量導(dǎo)出期間產(chǎn)生的增量變更不會(huì)丟。2.3 增量同步與業(yè)務(wù)雙寫設(shè)計(jì)增量同步的目標(biāo)是讓PostgreSQL和Oracle的數(shù)據(jù)差距保持在分鐘級(jí)以內(nèi)這樣停機(jī)切換時(shí)只需要把最后幾分鐘的增量追上就能關(guān)窗口。我們的增量鏈路是Oracle歸檔日志 → LogMiner解析 → Kafka → 自研消費(fèi)者程序 → 應(yīng)用到PostgreSQL。為什么用LogMiner而不是直接用OGG主要是因?yàn)镺GG授權(quán)成本和部署復(fù)雜度在當(dāng)時(shí)的環(huán)境下不劃算。LogMiner有幾個(gè)坑要提前踩必須提前開啟最小補(bǔ)充日志Supplemental Log否則LogMiner拿不到更新前的字段值UPDATE語(yǔ)句沒(méi)法正確重放。LogMiner解析出來(lái)的DDL語(yǔ)句不能直接拿到PostgreSQL執(zhí)行需要做語(yǔ)法轉(zhuǎn)換比如ALTER TABLE xxx ADD (col NUMBER)要改成ALTER TABLE xxx ADD COLUMN col NUMERIC。大事務(wù)解析會(huì)產(chǎn)生大量Redo記錄LogMiner內(nèi)存可能扛不住要設(shè)置合理的UTL_FILE落地目錄分段讀取。增量應(yīng)用端的沖突處理也需要設(shè)計(jì)。雙軌運(yùn)行期間如果應(yīng)用還在繼續(xù)寫Oracle我們的增量程序會(huì)把這些變更實(shí)時(shí)投遞到PostgreSQL兩邊數(shù)據(jù)保持一致。但如果某個(gè)時(shí)刻增量應(yīng)用出現(xiàn)問(wèn)題、落后太多或者業(yè)務(wù)在驗(yàn)證階段直接在PostgreSQL上面做了修改就會(huì)產(chǎn)生回環(huán)沖突。我們的策略是切換之前所有寫操作只走OraclePostgreSQL只接受增量投遞的歷史數(shù)據(jù)切換之后應(yīng)用層切到PostgreSQLOracle那邊不再接收業(yè)務(wù)寫流量。這套單向?qū)?、雙向讀的策略在切換前把沖突可能性降到最低。2.4 停機(jī)窗口內(nèi)的切換步驟清單就算增量同步再穩(wěn)定切換當(dāng)天也必須有SOP每一步到什么狀態(tài)、由誰(shuí)確認(rèn)都要提前定義好。我們的切換清單大概是這樣的業(yè)務(wù)側(cè)發(fā)布維護(hù)通知應(yīng)用層開啟只讀模式或直接停止寫流量等待增量同步追上確認(rèn)Oracle和PostgreSQL的數(shù)據(jù)延遲為0停止Oracle側(cè)的寫操作記錄停止時(shí)間點(diǎn)增量程序消費(fèi)完Kafka里所有消息應(yīng)用完最后一筆變更執(zhí)行數(shù)據(jù)校驗(yàn)?zāi)_本下一節(jié)細(xì)說(shuō)確認(rèn)兩邊數(shù)據(jù)一致關(guān)閉增量同步程序防止PostgreSQL繼續(xù)接收Oracle的變更應(yīng)用層修改數(shù)據(jù)庫(kù)連接配置切換到PostgreSQL執(zhí)行一批關(guān)鍵的冒煙測(cè)試SQL確認(rèn)核心業(yè)務(wù)功能正常觀察5到10分鐘確認(rèn)無(wú)異常后對(duì)外宣布切換完成。整個(gè)切換過(guò)程耗時(shí)大概1.5小時(shí)算上全量數(shù)據(jù)校驗(yàn)、應(yīng)用重連、緩存預(yù)熱最終停機(jī)窗口控制在3小時(shí)左右滿足4小時(shí)的要求。這里要注意第5步的數(shù)據(jù)校驗(yàn)不能等全部跑完才判斷我們?cè)谇袚Q前就做過(guò)多次全量預(yù)校驗(yàn)在增量持續(xù)運(yùn)行的情況下切換當(dāng)天只做增量部分的差異性校驗(yàn)和隨機(jī)抽表比對(duì)。3. 數(shù)據(jù)校驗(yàn)方案讓業(yè)務(wù)信服的三個(gè)層次3.1 第一層行數(shù)與總量校驗(yàn)最基礎(chǔ)的校驗(yàn)是行數(shù)比對(duì)。對(duì)每張表執(zhí)行SELECT COUNT(*)兩邊對(duì)不上就說(shuō)明有問(wèn)題。但COUNT在大表上很慢動(dòng)輒幾分鐘甚至更久。我們當(dāng)時(shí)是用pg_comparison這類工具直接對(duì)比但更通用的做法是按主鍵范圍分段統(tǒng)計(jì)行數(shù)每段一個(gè)任務(wù)并行跑最后匯總。如果源表和目標(biāo)表都沒(méi)有主鍵Oracle里確實(shí)存在這種爛表那校驗(yàn)就只能退而求其次按所有字段做GROUP BY后再COUNT或者用COUNT(*)配合SUM(HASH)來(lái)粗查。這類表數(shù)量不多還好如果很多強(qiáng)烈建議在建表階段就幫它們補(bǔ)上主鍵或唯一約束否則后續(xù)的數(shù)據(jù)一致性驗(yàn)證基本沒(méi)法做。行數(shù)校驗(yàn)是必要條件但不是充分條件行數(shù)一樣不代表數(shù)據(jù)一樣所以還要做第二層。3.2 第二層字段級(jí)CRC校驗(yàn)與哈希比對(duì)第二層是對(duì)每一行的所有字段拼起來(lái)做哈希兩邊比對(duì)哈希值是否一致。具體做法是在源庫(kù)Oracle寫一段SQL把每行需要比對(duì)的字段用DBMS_CRYPTO.HASH或STANDARD_HASH算出一個(gè)哈希值SELECT id, STANDARD_HASH(col1 || | || col2 || | || col3, SHA256) AS row_hash FROM my_table;在目標(biāo)庫(kù)PostgreSQL用同樣的拼接規(guī)則算哈希SELECT id, encode(sha256(convert_to(col1 || | || col2 || | || col3, UTF8)), hex) AS row_hash FROM my_table;兩邊按主鍵關(guān)聯(lián)對(duì)比row_hash是否一致把所有不一致的主鍵ID撈出來(lái)交給業(yè)務(wù)確認(rèn)。這里有幾個(gè)坑必須注意拼接字符串里的分隔符不能和字段值本身沖突。我一般用兩個(gè)特殊字符比如||~|~||并提前檢查字段值里是否包含這些字符。字段值里的NULL要統(tǒng)一處理。Oracle里空字符串就是NULL而PostgreSQL里空字符串和NULL是不同的。我們統(tǒng)一約定NULL或空串都替換成一個(gè)特殊標(biāo)記比如NULL。浮點(diǎn)類型BINARY_DOUBLE、NUMBER帶小數(shù)在Oracle和PostgreSQL二進(jìn)制存儲(chǔ)上有差異直接拼接字符串做哈希可能出現(xiàn)看起來(lái)一樣、哈希不一樣的誤報(bào)。處理方法是對(duì)數(shù)值類型先做格式化統(tǒng)一保留到小數(shù)點(diǎn)后6位或更多確保兩邊字符串一致。大數(shù)據(jù)量下表字段特別多時(shí)哈希計(jì)算會(huì)非常消耗源庫(kù)CPU。我們當(dāng)時(shí)在源庫(kù)用只讀快照或者備庫(kù)上跑哈希校驗(yàn)避免影響生產(chǎn)。3.3 第三層業(yè)務(wù)規(guī)則抽樣比對(duì)哈希校驗(yàn)?zāi)鼙ＷC技術(shù)層面的數(shù)據(jù)一致但業(yè)務(wù)方更關(guān)心他們用起來(lái)對(duì)不對(duì)。所以我們額外設(shè)計(jì)了一套業(yè)務(wù)規(guī)則的抽樣比對(duì)從核心業(yè)務(wù)表里隨機(jī)抽取若干個(gè)ID把該ID關(guān)聯(lián)的訂單、明細(xì)、流水全查出來(lái)業(yè)務(wù)方人工核對(duì)把一批統(tǒng)計(jì)報(bào)表SQL分別在Oracle和PostgreSQL上跑一遍比對(duì)聚合結(jié)果是否一致把幾個(gè)典型的復(fù)雜查詢多表關(guān)聯(lián)、子查詢、窗口函數(shù)在新庫(kù)上執(zhí)行業(yè)務(wù)方確認(rèn)執(zhí)行結(jié)果和口徑符合預(yù)期。這個(gè)環(huán)節(jié)的意義不止于查數(shù)據(jù)還在于讓業(yè)務(wù)方建立對(duì)遷移結(jié)果的信任。技術(shù)團(tuán)隊(duì)說(shuō)一百句校驗(yàn)過(guò)了不如業(yè)務(wù)方自己抽幾個(gè)單子確認(rèn)來(lái)得踏實(shí)。3.4 校驗(yàn)工具與自動(dòng)化我們整個(gè)校驗(yàn)體系是跑在一個(gè)自研的校驗(yàn)平臺(tái)上的支持配置表清單、校驗(yàn)類型、閾值和通知渠道。技術(shù)上不復(fù)雜核心就是一個(gè)任務(wù)調(diào)度器加一堆校驗(yàn)?zāi)_本。不過(guò)如果你沒(méi)有自研條件有幾個(gè)現(xiàn)成工具可以參考pg_comparison專門做PostgreSQL與PostgreSQL/Oracle等多庫(kù)數(shù)據(jù)對(duì)比支持行數(shù)、全量、抽樣對(duì)比能輸出差異報(bào)告。DataGrip/DBeaver的數(shù)據(jù)對(duì)比功能適合小表、交互式核對(duì)不適合大批量自動(dòng)化。xtc校驗(yàn)工具一些開源實(shí)現(xiàn)里有基于CRC或MD5的文件級(jí)校驗(yàn)?zāi)芰Φ苯佑迷跀?shù)據(jù)庫(kù)遷移上還是得改造。我的建議是工具只是輔助校驗(yàn)方案和口徑才是核心。先把口徑和字段映射規(guī)則定義清楚工具只是幫你把規(guī)則跑起來(lái)。4. 可回退機(jī)制給業(yè)務(wù)一顆定心丸4.1 回退方案設(shè)計(jì)的兩種路線回退最理想的方案是雙活也就是切換后Oracle和PostgreSQL都保持可寫應(yīng)用層按比例把流量分?jǐn)偟絻蛇吥骋粋?cè)出問(wèn)題可以隨時(shí)把流量全部切到另一側(cè)。但雙活對(duì)數(shù)據(jù)一致性的要求極高寫入沖突處理的復(fù)雜度也很大普通項(xiàng)目很難真正做到。我們實(shí)際采用的是切換后保留源庫(kù)只讀反向同步增量的半雙活方案。具體來(lái)說(shuō)切換時(shí)Oracle側(cè)停止業(yè)務(wù)寫入但數(shù)據(jù)庫(kù)實(shí)例不關(guān)閉歸檔日志繼續(xù)保留切換后增量同步程序反向工作監(jiān)聽PostgreSQL的變更用PostgreSQL的邏輯復(fù)制功能把變更同步回Oracle如果業(yè)務(wù)驗(yàn)證期間發(fā)現(xiàn)重大問(wèn)題需要回退我們先把PostgreSQL寫入停止等最后一批變更同步到Oracle然后應(yīng)用連接切回Oracle就能在較短時(shí)間內(nèi)恢復(fù)服務(wù)。這個(gè)方案的關(guān)鍵在于反向同步的可靠性和時(shí)延。PostgreSQL的邏輯復(fù)制Logical Replication機(jī)制比較成熟但把邏輯復(fù)制的變更重放到Oracle端還是得靠自研程序或中間件。我們當(dāng)時(shí)是寫了一個(gè)消費(fèi)者從PostgreSQL的WAL里解析出變更再合成SQL語(yǔ)句應(yīng)用到Oracle。這里有個(gè)現(xiàn)實(shí)問(wèn)題PostgreSQL邏輯復(fù)制默認(rèn)只適合PostgreSQL到PostgreSQL跨庫(kù)重放得自己處理類型映射和SQL方言差異工作量不小。4.2 回退窗口與觀察期設(shè)計(jì)回退不是無(wú)限期的業(yè)務(wù)方也不能想什么時(shí)候退就什么時(shí)候退。我們?cè)陧?xiàng)目計(jì)劃里明確了一個(gè)回退觀察期切換后一個(gè)月內(nèi)業(yè)務(wù)如果發(fā)現(xiàn)重大數(shù)據(jù)或功能問(wèn)題啟動(dòng)回退流程超過(guò)一個(gè)月認(rèn)為遷移已經(jīng)穩(wěn)定不再支持自動(dòng)回退Oracle源庫(kù)進(jìn)入只讀歸檔狀態(tài)。這個(gè)觀察期不是隨便定的是根據(jù)業(yè)務(wù)體量和風(fēng)險(xiǎn)容忍度協(xié)商出來(lái)的。觀察期越長(zhǎng)反向同步的維護(hù)成本和資源成本越高太短業(yè)務(wù)不放心。我見(jiàn)過(guò)有些項(xiàng)目觀察期只有一周結(jié)果第四天暴雷回退的時(shí)候反同步數(shù)據(jù)量太大差點(diǎn)沒(méi)退干凈。一般來(lái)說(shuō)業(yè)務(wù)量大的核心系統(tǒng)觀察期建議不低于兩周最好是一個(gè)月非核心系統(tǒng)可以適當(dāng)縮短。4.3 回退演練不能等到出事才練回退方案寫了幾頁(yè)紙但真正出事的時(shí)候能不能按流程執(zhí)行只有練過(guò)才知道。我們?cè)谡角袚Q之前做了兩次完整的回退演練每次都能暴露問(wèn)題。第一次演練暴露出一個(gè)經(jīng)典問(wèn)題切換后業(yè)務(wù)在PostgreSQL上生成了大量新數(shù)據(jù)回退時(shí)要把這些數(shù)據(jù)反向同步回Oracle結(jié)果因?yàn)楸斫Y(jié)構(gòu)里有個(gè)字段類型映射不一致導(dǎo)致批量INSERT失敗。這類問(wèn)題如果不演練真到回退的時(shí)候就是災(zāi)難。第二次演練我們提前修正了映射又測(cè)了流量切換的腳本確認(rèn)應(yīng)用重啟后能讀到Oracle的最新數(shù)據(jù)整個(gè)回退耗時(shí)控制在40分鐘內(nèi)。所以我的建議是回退演練必須包含數(shù)據(jù)層和應(yīng)用層兩層驗(yàn)證。數(shù)據(jù)層驗(yàn)證反向同步是否完整、一致應(yīng)用層驗(yàn)證連接切換、緩存清理、配置修改是否順利。只測(cè)數(shù)據(jù)不測(cè)應(yīng)用回退后業(yè)務(wù)還是起不來(lái)。5. 常見(jiàn)問(wèn)題與排查技巧實(shí)錄整個(gè)遷移過(guò)程中我們踩過(guò)的坑和排查過(guò)的問(wèn)題不少挑幾個(gè)有代表性的寫在這里供大家參考。5.1 Oracle的CONNECT BY在PostgreSQL里怎么改Oracle的層次查詢CONNECT BY PRIOR是遷移的高頻改造點(diǎn)。PostgreSQL沒(méi)有直接對(duì)應(yīng)的語(yǔ)法要用遞歸CTEWITH RECURSIVE改寫。比如Oracle里SELECT emp_id, mgr_id, LEVEL FROM emp START WITH mgr_id IS NULL CONNECT BY PRIOR emp_id mgr_id;改成PostgreSQLWITH RECURSIVE emp_tree AS ( SELECT emp_id, mgr_id, 1 AS level FROM emp WHERE mgr_id IS NULL UNION ALL SELECT e.emp_id, e.mgr_id, t.level 1 FROM emp e JOIN emp_tree t ON e.mgr_id t.emp_id ) SELECT emp_id, mgr_id, level FROM emp_tree;這里注意遞歸CTE的性能調(diào)優(yōu)空間比較大如果樹的深度和寬度都很大比如超過(guò)10層、幾十萬(wàn)節(jié)點(diǎn)建議提前用真實(shí)數(shù)據(jù)量壓測(cè)必要時(shí)加search_depth之類的優(yōu)化手段。5.2ROWNUM和ROW_NUMBER()的等價(jià)改寫Oracle里用ROWNUM做分頁(yè)很常見(jiàn)SELECT * FROM ( SELECT t.*, ROWNUM rn FROM big_table t ) WHERE rn BETWEEN 101 AND 200;PostgreSQL里直接用LIMIT/OFFSETSELECT * FROM big_table ORDER BY id LIMIT 100 OFFSET 100;這里有個(gè)容易踩的隱形坑Oracle的ROWNUM是在排序之前生成的所以如果原SQL里寫了ORDER BY再套R(shí)OWNUM語(yǔ)義上是先取前N行再排序和PostgreSQL的LIMIT語(yǔ)義先排序再取前N行不一樣。改寫時(shí)必須先確認(rèn)原SQL的業(yè)務(wù)意圖不然數(shù)據(jù)就錯(cuò)了。5.3 CLOB字段遷移到TEXT后的性能問(wèn)題Oracle的CLOB在PostgreSQL里一般對(duì)應(yīng)TEXT類型?？雌饋?lái)簡(jiǎn)單但實(shí)測(cè)發(fā)現(xiàn)一個(gè)現(xiàn)象某些業(yè)務(wù)對(duì)CLOB字段做頻繁的UPDATE和SELECT在Oracle里有專門的LOB段和空間管理性能還行到PostgreSQL的TEXT上如果寫入的文本很大幾十KB到幾MBTOAST機(jī)制會(huì)觸發(fā)壓縮和外部存儲(chǔ)性能會(huì)有波動(dòng)。我們的處理辦法把大文本字段單獨(dú)拆到一張擴(kuò)展表里和主表用外鍵關(guān)聯(lián)需要時(shí)才JOIN出來(lái)。如果業(yè)務(wù)確實(shí)要頻繁讀寫大文本還可以把external存儲(chǔ)打開避免每次查詢都加載整個(gè)大字段。5.4 空字符串與NULL的語(yǔ)義差異Oracle里和NULL是等價(jià)的SELECT NVL(, x) FROM dual返回x。PostgreSQL里不是NULL。這一條差異在數(shù)據(jù)遷移時(shí)特別容易產(chǎn)生臟數(shù)據(jù)。我們?cè)谛ｒ?yàn)階段發(fā)現(xiàn)不少表的某些字段兩邊值看起來(lái)不一致查了半天才發(fā)現(xiàn)是源庫(kù)存在空字符串遷移時(shí)我們統(tǒng)一轉(zhuǎn)換成了NULL但業(yè)務(wù)代碼里有用判斷的邏輯導(dǎo)致行為變了。后來(lái)我們定了規(guī)范Oracle到PostgreSQL遷移時(shí)空字符串按照Oracle語(yǔ)義統(tǒng)一轉(zhuǎn)NULL同時(shí)業(yè)務(wù)代碼里所有 的判斷改為IS NULL。這個(gè)規(guī)范在改造階段就要同步給開發(fā)團(tuán)隊(duì)。5.5 分區(qū)表遷移的正確姿勢(shì)Oracle的分區(qū)表遷移到PostgreSQL有幾種方案用PostgreSQL原生分區(qū)表基于聲明式分區(qū)或者用繼承分區(qū)或者用pg_partman這類擴(kuò)展。我們的經(jīng)驗(yàn)如果分區(qū)數(shù)量不大幾十到幾百個(gè)用原生聲明式分區(qū)就夠了如果分區(qū)數(shù)量上千原生分區(qū)的元數(shù)據(jù)管理會(huì)有壓力可以考慮用pg_partman管理自動(dòng)建分區(qū)。遷移時(shí)的一個(gè)常見(jiàn)坑Oracle分區(qū)表的全局索引和分區(qū)索引在PostgreSQL里沒(méi)有完全對(duì)應(yīng)的概念需要根據(jù)業(yè)務(wù)SQL的過(guò)濾條件重新設(shè)計(jì)索引策略。不要照搬Oracle的索引結(jié)構(gòu)否則查詢性能會(huì)很差。5.6 序列遷移的斷檔問(wèn)題Oracle的SEQUENCE不保證連續(xù)PostgreSQL的SEQUENCE也一樣。但兩邊序列的CACHE和INCREMENT配置不同步可能導(dǎo)致ID沖突或斷檔。我們遷移時(shí)把Oracle序列的LAST_NUMBER和INCREMENT_BY記錄到PostgreSQL序列的同名參數(shù)上還要考慮增量同步階段兩邊應(yīng)用各拿各的序列號(hào)等切換后PostgreSQL序列起始值必須比Oracle當(dāng)前值大很多否則切過(guò)來(lái)后新生成的ID可能和同步回來(lái)的歷史數(shù)據(jù)撞車。這個(gè)問(wèn)題真的很隱蔽但如果爆了就是主鍵沖突直接拖垮切換。建議切換前用腳本檢查所有序列的當(dāng)前值確保PostgreSQL側(cè)的值遠(yuǎn)高于Oracle側(cè)。5.7 常見(jiàn)問(wèn)題速查表問(wèn)題現(xiàn)象常見(jiàn)原因排查/處理辦法校驗(yàn)時(shí)哈希不一致字段拼接順序不一致、NULL/空串沒(méi)統(tǒng)一、浮點(diǎn)精度差異統(tǒng)一拼接規(guī)則字段值做標(biāo)準(zhǔn)化增量同步延遲持續(xù)增大Kafka消費(fèi)能力不足、目標(biāo)庫(kù)應(yīng)用能力差、大事務(wù)卡住增加消費(fèi)者并行度排查大事務(wù)必要時(shí)手動(dòng)補(bǔ)數(shù)切換后應(yīng)用報(bào)驅(qū)動(dòng)錯(cuò)誤JDBC連接串或驅(qū)動(dòng)版本不兼容Postgres JDBC驅(qū)動(dòng)與Oracle JDBC驅(qū)動(dòng)用法不同檢查URL參數(shù)、schema設(shè)置存儲(chǔ)過(guò)程執(zhí)行失敗PL/SQL和PL/pgSQL語(yǔ)法差異人工改寫重點(diǎn)處理游標(biāo)、動(dòng)態(tài)SQL、異常處理查詢性能嚴(yán)重下降統(tǒng)計(jì)信息未收集、索引缺失、優(yōu)化器差異切換后立即執(zhí)行ANALYZE根據(jù)執(zhí)行計(jì)劃補(bǔ)索引回退時(shí)反向同步失敗類型映射不一致、主鍵沖突、循環(huán)依賴提前演練建立主鍵沖突處理規(guī)則類型映射表要用同一份我自己走完這個(gè)項(xiàng)目后最深的體會(huì)是數(shù)據(jù)庫(kù)遷移拼的不是單點(diǎn)技術(shù)有多強(qiáng)而是流程管理和風(fēng)險(xiǎn)把控。這三個(gè)目標(biāo)——低中斷、可校驗(yàn)、可回退——每一個(gè)都對(duì)應(yīng)著一整套方案和工具鏈而它們之間又互相影響。建議準(zhǔn)備動(dòng)手的朋友先把你現(xiàn)有的停機(jī)窗口、數(shù)據(jù)量、業(yè)務(wù)容忍度都擺出來(lái)用這些邊界條件去倒推每一步該怎么做。方案設(shè)計(jì)得越細(xì)執(zhí)行的時(shí)候才越穩(wěn)。再補(bǔ)充一點(diǎn)遷移過(guò)程中所有腳本和命令一定要先在小環(huán)境里完整演練一遍帶上真實(shí)數(shù)據(jù)量的1%到5%壓測(cè)一下性能別等到正式切換才發(fā)現(xiàn)導(dǎo)出速度、校驗(yàn)耗時(shí)、增量延遲這些數(shù)字完全不可控。 Oracle遷移PostgreSQL實(shí)戰(zhàn)：低業(yè)務(wù)中斷、可校驗(yàn)、可回退的完整方案
搞Oracle到PostgreSQL遷移這件事圈子里聊得最多的不是工具選哪個(gè)而是怎么讓業(yè)務(wù)別停太久、數(shù)據(jù)到底搬對(duì)沒(méi)有、出了問(wèn)題能不能跑回去這三個(gè)靈魂拷問(wèn)。我最近完整跟完了一個(gè)核心交易系統(tǒng)的遷移項(xiàng)目正好把整套打法梳理一遍給準(zhǔn)備上車的朋友當(dāng)個(gè)參考。先說(shuō)結(jié)論Oracle遷PostgreSQL難點(diǎn)從來(lái)不在怎么導(dǎo)數(shù)據(jù)而在遷移鏈條的整體設(shè)計(jì)。你只有把低業(yè)務(wù)中斷、可校驗(yàn)、可回退當(dāng)成一個(gè)整體目標(biāo)來(lái)拆解才能避免那種數(shù)據(jù)導(dǎo)完了、業(yè)務(wù)起不來(lái)、回也回不去的尷尬局面。1. 遷移項(xiàng)目啟動(dòng)前先想清楚怎么切再動(dòng)手1.1 遷移背景與目標(biāo)拆解我們這個(gè)項(xiàng)目是從Oracle 11g遷到PostgreSQL 14源庫(kù)大概有3TB數(shù)據(jù)核心業(yè)務(wù)表最大的一張超過(guò)8億行還有不少用到了CLOB、分區(qū)、存儲(chǔ)過(guò)程的老業(yè)務(wù)。業(yè)務(wù)側(cè)給的要求很明確停機(jī)窗口最多4小時(shí)切換后一個(gè)月內(nèi)如果發(fā)現(xiàn)重大問(wèn)題必須能回退并且遷移完成后要拿出讓業(yè)務(wù)信服的數(shù)據(jù)一致性證明。這三條要求翻譯成技術(shù)語(yǔ)言就是低業(yè)務(wù)中斷不能搞先停庫(kù)全量導(dǎo)出再全量導(dǎo)入的老套路必須走全量增量同步的雙軌方案把真正的停機(jī)時(shí)間壓縮到只需要處理增量追趕和切換驗(yàn)證這兩個(gè)環(huán)節(jié)。可校驗(yàn)遷移完成后要用行數(shù)、校驗(yàn)和、抽樣比對(duì)等手段證明數(shù)據(jù)沒(méi)丟、沒(méi)多、沒(méi)錯(cuò)。不是拿兩條SQL對(duì)一下數(shù)量就完事而是要能經(jīng)得起業(yè)務(wù)和審計(jì)的追問(wèn)?？苫赝饲袚Q后保留完整的回退通道包括源庫(kù)持續(xù)運(yùn)行、反向同步機(jī)制、回退演練預(yù)案。一旦業(yè)務(wù)驗(yàn)證不通過(guò)能在一個(gè)小時(shí)內(nèi)切回Oracle且不丟新增數(shù)據(jù)。這三個(gè)目標(biāo)不是并列關(guān)系而是互相牽制的。比如為了可回退你可能需要在一段時(shí)間內(nèi)同時(shí)維護(hù)兩套庫(kù)的寫入為了低中斷你的增量同步工具必須足夠穩(wěn)定否則追趕不上就會(huì)無(wú)限拉長(zhǎng)停機(jī)窗口為了可校驗(yàn)又必須在遷移過(guò)程中保留足夠的審計(jì)痕跡方便回退時(shí)對(duì)賬。1.2 遷移前的源庫(kù)調(diào)研與風(fēng)險(xiǎn)清單很多項(xiàng)目死在第一步?jīng)]搞清楚源庫(kù)里到底有什么就開始動(dòng)手。我建議遷移前至少花一到兩周做一次徹底的源庫(kù)體檢輸出一份風(fēng)險(xiǎn)清單。我們當(dāng)時(shí)重點(diǎn)排查了以下幾類東西對(duì)象清單表、索引、約束、序列、視圖、物化視圖、存儲(chǔ)過(guò)程、函數(shù)、包、觸發(fā)器、同義詞、DB Link。每一項(xiàng)都要統(tǒng)計(jì)數(shù)量并評(píng)估改造工作量。千萬(wàn)別小看同義詞和DB Link業(yè)務(wù)SQL里如果大量使用遷移后全都要改。SQL使用情況抓取AWR報(bào)告和近一段時(shí)間的慢SQL重點(diǎn)看用了哪些Oracle獨(dú)有語(yǔ)法比如CONNECT BY層次查詢、()外連接、ROWNUM分頁(yè)、NVL、SYSDATE、MERGE、LISTAGG等。這些是SQL改造的重災(zāi)區(qū)。數(shù)據(jù)類型分布NUMBER、VARCHAR2、DATE、CLOB、BLOB、LONG、RAW、ROWID等類型的字段數(shù)量和最大長(zhǎng)度。尤其是CLOB和LONG遷移后要用TEXT替代但業(yè)務(wù)代碼里對(duì)CLOB的操作方式可能需要調(diào)整。存儲(chǔ)過(guò)程復(fù)雜度統(tǒng)計(jì)有多少存儲(chǔ)過(guò)程、總代碼行數(shù)、用到了哪些Oracle獨(dú)有包DBMS_SQL、DBMS_JOB、DBMS_ALERT、UTL_FILE等。這部分改造成本往往被嚴(yán)重低估。我們項(xiàng)目里有1300多個(gè)存儲(chǔ)過(guò)程最終花了整個(gè)項(xiàng)目40%的工時(shí)在改寫上面。依賴關(guān)系應(yīng)用與數(shù)據(jù)庫(kù)的交互方式包括連接方式JDBC、OCI、ODBC、事務(wù)隔離級(jí)別、會(huì)話級(jí)參數(shù)設(shè)置、臨時(shí)表使用情況。應(yīng)用層的連接串、驅(qū)動(dòng)、SQL寫法都要納入改造范圍。體檢報(bào)告出來(lái)后要讓業(yè)務(wù)方、DBA、開發(fā)三方一起評(píng)審明確哪些東西可以原樣遷哪些必須改造遷哪些是僵尸對(duì)象可以果斷拋棄。這一步做扎實(shí)了后面所有的方案才有依據(jù)。2. 低業(yè)務(wù)中斷的核心打法全量增量雙軌遷移2.1 遷移工具選型與取舍工具選型沒(méi)有標(biāo)準(zhǔn)答案核心看你的源庫(kù)版本、目標(biāo)庫(kù)版本、數(shù)據(jù)總量、同步實(shí)時(shí)性要求和團(tuán)隊(duì)熟悉度。我們當(dāng)時(shí)對(duì)比了三條路線Ora2Pg開源免費(fèi)Perl寫的能把表結(jié)構(gòu)、數(shù)據(jù)、序列、視圖、存儲(chǔ)過(guò)程整體轉(zhuǎn)換支持直接連Oracle導(dǎo)出再導(dǎo)入PostgreSQL。優(yōu)點(diǎn)是自動(dòng)化程度高缺點(diǎn)是大表導(dǎo)出用COPY方式對(duì)源庫(kù)有一定壓力而且存儲(chǔ)過(guò)程轉(zhuǎn)換質(zhì)量一般只能轉(zhuǎn)個(gè)骨架復(fù)雜邏輯基本還得人工改。pgloader同樣開源從Oracle讀數(shù)據(jù)到PostgreSQL非常快用的也是COPY協(xié)議支持并行加載。但DDL遷移能力弱主要用來(lái)搬數(shù)據(jù)。自研抽取同步腳本用Python寫一套基于JDBC的全量導(dǎo)出和增量抽取腳本配合調(diào)度平臺(tái)實(shí)現(xiàn)斷點(diǎn)續(xù)傳和數(shù)據(jù)校驗(yàn)。靈活性最高但開發(fā)量不小。我們最終采用了Ora2Pg轉(zhuǎn)結(jié)構(gòu)pgloader搬全量自研增量同步腳本Debezium做變更捕獲的組合方案。結(jié)構(gòu)轉(zhuǎn)換用Ora2Pg生成初始DDL再人工修全量數(shù)據(jù)用pgloader并行加載增量部分考慮到源庫(kù)是Oracle 11gDebezium的Oracle插件要裝額外組件最后是自己寫了一套基于日志的增量抽取程序。這里給個(gè)建議如果是Oracle 12c及以上Debezium是較好的增量同步方案社區(qū)活躍且支持?jǐn)帱c(diǎn)續(xù)傳11g就得掂量掂量了。2.2 全量遷移的并行與限速策略全量數(shù)據(jù)遷移最容易翻車的地方是導(dǎo)太猛把源庫(kù)IO打滿業(yè)務(wù)直接告警導(dǎo)太慢又趕不上停機(jī)窗口。所以并行度和限速必須提前設(shè)計(jì)。我們按表維度拆分任務(wù)用了一個(gè)簡(jiǎn)單的規(guī)則小于100萬(wàn)行的表單表單任務(wù)順序執(zhí)行100萬(wàn)到1000萬(wàn)行的表按主鍵范圍拆成4到8個(gè)分片并行超過(guò)1000萬(wàn)行的表按主鍵范圍拆成16到32個(gè)分片每個(gè)分片獨(dú)立連接、獨(dú)立事務(wù)。同時(shí)設(shè)置了全局并發(fā)上限保證源庫(kù)的活躍會(huì)話數(shù)不超過(guò)20。pgloader本身有個(gè)batch concurrency參數(shù)可以控制并行度但實(shí)測(cè)下來(lái)對(duì)Oracle源庫(kù)支持一般所以我更推薦自己寫一個(gè)簡(jiǎn)單的調(diào)度器用一張任務(wù)表記錄每個(gè)分片的狀態(tài)調(diào)度線程從里面撈待執(zhí)行的任務(wù)分發(fā)給工作線程工作線程處理完更新狀態(tài)出錯(cuò)自動(dòng)重試三次還是失敗就進(jìn)入人工處理隊(duì)列。這里有個(gè)非常關(guān)鍵的細(xì)節(jié)全量遷移必須在增量同步啟動(dòng)之前完成并且要記錄全量導(dǎo)出的數(shù)據(jù)快照時(shí)間點(diǎn)。我們的做法是全量開始前先在源庫(kù)開啟歸檔日志和補(bǔ)充日志然后啟動(dòng)增量抽取程序?qū)崟r(shí)解析日志寫入Kafka全量導(dǎo)出的每張表在導(dǎo)出前記錄一個(gè)SCNSystem Change Number快照導(dǎo)出完成后用這個(gè)SCN到增量同步里做數(shù)據(jù)拼接。這樣才能保證全量導(dǎo)出期間產(chǎn)生的增量變更不會(huì)丟。2.3 增量同步與業(yè)務(wù)雙寫設(shè)計(jì)增量同步的目標(biāo)是讓PostgreSQL和Oracle的數(shù)據(jù)差距保持在分鐘級(jí)以內(nèi)這樣停機(jī)切換時(shí)只需要把最后幾分鐘的增量追上就能關(guān)窗口。我們的增量鏈路是Oracle歸檔日志 → LogMiner解析 → Kafka → 自研消費(fèi)者程序 → 應(yīng)用到PostgreSQL。為什么用LogMiner而不是直接用OGG主要是因?yàn)镺GG授權(quán)成本和部署復(fù)雜度在當(dāng)時(shí)的環(huán)境下不劃算。LogMiner有幾個(gè)坑要提前踩必須提前開啟最小補(bǔ)充日志Supplemental Log否則LogMiner拿不到更新前的字段值UPDATE語(yǔ)句沒(méi)法正確重放。LogMiner解析出來(lái)的DDL語(yǔ)句不能直接拿到PostgreSQL執(zhí)行需要做語(yǔ)法轉(zhuǎn)換比如ALTER TABLE xxx ADD (col NUMBER)要改成ALTER TABLE xxx ADD COLUMN col NUMERIC。大事務(wù)解析會(huì)產(chǎn)生大量Redo記錄LogMiner內(nèi)存可能扛不住要設(shè)置合理的UTL_FILE落地目錄分段讀取。增量應(yīng)用端的沖突處理也需要設(shè)計(jì)。雙軌運(yùn)行期間如果應(yīng)用還在繼續(xù)寫Oracle我們的增量程序會(huì)把這些變更實(shí)時(shí)投遞到PostgreSQL兩邊數(shù)據(jù)保持一致。但如果某個(gè)時(shí)刻增量應(yīng)用出現(xiàn)問(wèn)題、落后太多或者業(yè)務(wù)在驗(yàn)證階段直接在PostgreSQL上面做了修改就會(huì)產(chǎn)生回環(huán)沖突。我們的策略是切換之前所有寫操作只走OraclePostgreSQL只接受增量投遞的歷史數(shù)據(jù)切換之后應(yīng)用層切到PostgreSQLOracle那邊不再接收業(yè)務(wù)寫流量。這套單向?qū)?、雙向讀的策略在切換前把沖突可能性降到最低。2.4 停機(jī)窗口內(nèi)的切換步驟清單就算增量同步再穩(wěn)定切換當(dāng)天也必須有SOP每一步到什么狀態(tài)、由誰(shuí)確認(rèn)都要提前定義好。我們的切換清單大概是這樣的業(yè)務(wù)側(cè)發(fā)布維護(hù)通知應(yīng)用層開啟只讀模式或直接停止寫流量等待增量同步追上確認(rèn)Oracle和PostgreSQL的數(shù)據(jù)延遲為0停止Oracle側(cè)的寫操作記錄停止時(shí)間點(diǎn)增量程序消費(fèi)完Kafka里所有消息應(yīng)用完最后一筆變更執(zhí)行數(shù)據(jù)校驗(yàn)?zāi)_本下一節(jié)細(xì)說(shuō)確認(rèn)兩邊數(shù)據(jù)一致關(guān)閉增量同步程序防止PostgreSQL繼續(xù)接收Oracle的變更應(yīng)用層修改數(shù)據(jù)庫(kù)連接配置切換到PostgreSQL執(zhí)行一批關(guān)鍵的冒煙測(cè)試SQL確認(rèn)核心業(yè)務(wù)功能正常觀察5到10分鐘確認(rèn)無(wú)異常后對(duì)外宣布切換完成。整個(gè)切換過(guò)程耗時(shí)大概1.5小時(shí)算上全量數(shù)據(jù)校驗(yàn)、應(yīng)用重連、緩存預(yù)熱最終停機(jī)窗口控制在3小時(shí)左右滿足4小時(shí)的要求。這里要注意第5步的數(shù)據(jù)校驗(yàn)不能等全部跑完才判斷我們?cè)谇袚Q前就做過(guò)多次全量預(yù)校驗(yàn)在增量持續(xù)運(yùn)行的情況下切換當(dāng)天只做增量部分的差異性校驗(yàn)和隨機(jī)抽表比對(duì)。3. 數(shù)據(jù)校驗(yàn)方案讓業(yè)務(wù)信服的三個(gè)層次3.1 第一層行數(shù)與總量校驗(yàn)最基礎(chǔ)的校驗(yàn)是行數(shù)比對(duì)。對(duì)每張表執(zhí)行SELECT COUNT(*)兩邊對(duì)不上就說(shuō)明有問(wèn)題。但COUNT在大表上很慢動(dòng)輒幾分鐘甚至更久。我們當(dāng)時(shí)是用pg_comparison這類工具直接對(duì)比但更通用的做法是按主鍵范圍分段統(tǒng)計(jì)行數(shù)每段一個(gè)任務(wù)并行跑最后匯總。如果源表和目標(biāo)表都沒(méi)有主鍵Oracle里確實(shí)存在這種爛表那校驗(yàn)就只能退而求其次按所有字段做GROUP BY后再COUNT或者用COUNT(*)配合SUM(HASH)來(lái)粗查。這類表數(shù)量不多還好如果很多強(qiáng)烈建議在建表階段就幫它們補(bǔ)上主鍵或唯一約束否則后續(xù)的數(shù)據(jù)一致性驗(yàn)證基本沒(méi)法做。行數(shù)校驗(yàn)是必要條件但不是充分條件行數(shù)一樣不代表數(shù)據(jù)一樣所以還要做第二層。3.2 第二層字段級(jí)CRC校驗(yàn)與哈希比對(duì)第二層是對(duì)每一行的所有字段拼起來(lái)做哈希兩邊比對(duì)哈希值是否一致。具體做法是在源庫(kù)Oracle寫一段SQL把每行需要比對(duì)的字段用DBMS_CRYPTO.HASH或STANDARD_HASH算出一個(gè)哈希值SELECT id, STANDARD_HASH(col1 || | || col2 || | || col3, SHA256) AS row_hash FROM my_table;在目標(biāo)庫(kù)PostgreSQL用同樣的拼接規(guī)則算哈希SELECT id, encode(sha256(convert_to(col1 || | || col2 || | || col3, UTF8)), hex) AS row_hash FROM my_table;兩邊按主鍵關(guān)聯(lián)對(duì)比row_hash是否一致把所有不一致的主鍵ID撈出來(lái)交給業(yè)務(wù)確認(rèn)。這里有幾個(gè)坑必須注意拼接字符串里的分隔符不能和字段值本身沖突。我一般用兩個(gè)特殊字符比如||~|~||并提前檢查字段值里是否包含這些字符。字段值里的NULL要統(tǒng)一處理。Oracle里空字符串就是NULL而PostgreSQL里空字符串和NULL是不同的。我們統(tǒng)一約定NULL或空串都替換成一個(gè)特殊標(biāo)記比如NULL。浮點(diǎn)類型BINARY_DOUBLE、NUMBER帶小數(shù)在Oracle和PostgreSQL二進(jìn)制存儲(chǔ)上有差異直接拼接字符串做哈希可能出現(xiàn)看起來(lái)一樣、哈希不一樣的誤報(bào)。處理方法是對(duì)數(shù)值類型先做格式化統(tǒng)一保留到小數(shù)點(diǎn)后6位或更多確保兩邊字符串一致。大數(shù)據(jù)量下表字段特別多時(shí)哈希計(jì)算會(huì)非常消耗源庫(kù)CPU。我們當(dāng)時(shí)在源庫(kù)用只讀快照或者備庫(kù)上跑哈希校驗(yàn)避免影響生產(chǎn)。3.3 第三層業(yè)務(wù)規(guī)則抽樣比對(duì)哈希校驗(yàn)?zāi)鼙ＷC技術(shù)層面的數(shù)據(jù)一致但業(yè)務(wù)方更關(guān)心他們用起來(lái)對(duì)不對(duì)。所以我們額外設(shè)計(jì)了一套業(yè)務(wù)規(guī)則的抽樣比對(duì)從核心業(yè)務(wù)表里隨機(jī)抽取若干個(gè)ID把該ID關(guān)聯(lián)的訂單、明細(xì)、流水全查出來(lái)業(yè)務(wù)方人工核對(duì)把一批統(tǒng)計(jì)報(bào)表SQL分別在Oracle和PostgreSQL上跑一遍比對(duì)聚合結(jié)果是否一致把幾個(gè)典型的復(fù)雜查詢多表關(guān)聯(lián)、子查詢、窗口函數(shù)在新庫(kù)上執(zhí)行業(yè)務(wù)方確認(rèn)執(zhí)行結(jié)果和口徑符合預(yù)期。這個(gè)環(huán)節(jié)的意義不止于查數(shù)據(jù)還在于讓業(yè)務(wù)方建立對(duì)遷移結(jié)果的信任。技術(shù)團(tuán)隊(duì)說(shuō)一百句校驗(yàn)過(guò)了不如業(yè)務(wù)方自己抽幾個(gè)單子確認(rèn)來(lái)得踏實(shí)。3.4 校驗(yàn)工具與自動(dòng)化我們整個(gè)校驗(yàn)體系是跑在一個(gè)自研的校驗(yàn)平臺(tái)上的支持配置表清單、校驗(yàn)類型、閾值和通知渠道。技術(shù)上不復(fù)雜核心就是一個(gè)任務(wù)調(diào)度器加一堆校驗(yàn)?zāi)_本。不過(guò)如果你沒(méi)有自研條件有幾個(gè)現(xiàn)成工具可以參考pg_comparison專門做PostgreSQL與PostgreSQL/Oracle等多庫(kù)數(shù)據(jù)對(duì)比支持行數(shù)、全量、抽樣對(duì)比能輸出差異報(bào)告。DataGrip/DBeaver的數(shù)據(jù)對(duì)比功能適合小表、交互式核對(duì)不適合大批量自動(dòng)化。xtc校驗(yàn)工具一些開源實(shí)現(xiàn)里有基于CRC或MD5的文件級(jí)校驗(yàn)?zāi)芰Φ苯佑迷跀?shù)據(jù)庫(kù)遷移上還是得改造。我的建議是工具只是輔助校驗(yàn)方案和口徑才是核心。先把口徑和字段映射規(guī)則定義清楚工具只是幫你把規(guī)則跑起來(lái)。4. 可回退機(jī)制給業(yè)務(wù)一顆定心丸4.1 回退方案設(shè)計(jì)的兩種路線回退最理想的方案是雙活也就是切換后Oracle和PostgreSQL都保持可寫應(yīng)用層按比例把流量分?jǐn)偟絻蛇吥骋粋?cè)出問(wèn)題可以隨時(shí)把流量全部切到另一側(cè)。但雙活對(duì)數(shù)據(jù)一致性的要求極高寫入沖突處理的復(fù)雜度也很大普通項(xiàng)目很難真正做到。我們實(shí)際采用的是切換后保留源庫(kù)只讀反向同步增量的半雙活方案。具體來(lái)說(shuō)切換時(shí)Oracle側(cè)停止業(yè)務(wù)寫入但數(shù)據(jù)庫(kù)實(shí)例不關(guān)閉歸檔日志繼續(xù)保留切換后增量同步程序反向工作監(jiān)聽PostgreSQL的變更用PostgreSQL的邏輯復(fù)制功能把變更同步回Oracle如果業(yè)務(wù)驗(yàn)證期間發(fā)現(xiàn)重大問(wèn)題需要回退我們先把PostgreSQL寫入停止等最后一批變更同步到Oracle然后應(yīng)用連接切回Oracle就能在較短時(shí)間內(nèi)恢復(fù)服務(wù)。這個(gè)方案的關(guān)鍵在于反向同步的可靠性和時(shí)延。PostgreSQL的邏輯復(fù)制Logical Replication機(jī)制比較成熟但把邏輯復(fù)制的變更重放到Oracle端還是得靠自研程序或中間件。我們當(dāng)時(shí)是寫了一個(gè)消費(fèi)者從PostgreSQL的WAL里解析出變更再合成SQL語(yǔ)句應(yīng)用到Oracle。這里有個(gè)現(xiàn)實(shí)問(wèn)題PostgreSQL邏輯復(fù)制默認(rèn)只適合PostgreSQL到PostgreSQL跨庫(kù)重放得自己處理類型映射和SQL方言差異工作量不小。4.2 回退窗口與觀察期設(shè)計(jì)回退不是無(wú)限期的業(yè)務(wù)方也不能想什么時(shí)候退就什么時(shí)候退。我們?cè)陧?xiàng)目計(jì)劃里明確了一個(gè)回退觀察期切換后一個(gè)月內(nèi)業(yè)務(wù)如果發(fā)現(xiàn)重大數(shù)據(jù)或功能問(wèn)題啟動(dòng)回退流程超過(guò)一個(gè)月認(rèn)為遷移已經(jīng)穩(wěn)定不再支持自動(dòng)回退Oracle源庫(kù)進(jìn)入只讀歸檔狀態(tài)。這個(gè)觀察期不是隨便定的是根據(jù)業(yè)務(wù)體量和風(fēng)險(xiǎn)容忍度協(xié)商出來(lái)的。觀察期越長(zhǎng)反向同步的維護(hù)成本和資源成本越高太短業(yè)務(wù)不放心。我見(jiàn)過(guò)有些項(xiàng)目觀察期只有一周結(jié)果第四天暴雷回退的時(shí)候反同步數(shù)據(jù)量太大差點(diǎn)沒(méi)退干凈。一般來(lái)說(shuō)業(yè)務(wù)量大的核心系統(tǒng)觀察期建議不低于兩周最好是一個(gè)月非核心系統(tǒng)可以適當(dāng)縮短。4.3 回退演練不能等到出事才練回退方案寫了幾頁(yè)紙但真正出事的時(shí)候能不能按流程執(zhí)行只有練過(guò)才知道。我們?cè)谡角袚Q之前做了兩次完整的回退演練每次都能暴露問(wèn)題。第一次演練暴露出一個(gè)經(jīng)典問(wèn)題切換后業(yè)務(wù)在PostgreSQL上生成了大量新數(shù)據(jù)回退時(shí)要把這些數(shù)據(jù)反向同步回Oracle結(jié)果因?yàn)楸斫Y(jié)構(gòu)里有個(gè)字段類型映射不一致導(dǎo)致批量INSERT失敗。這類問(wèn)題如果不演練真到回退的時(shí)候就是災(zāi)難。第二次演練我們提前修正了映射又測(cè)了流量切換的腳本確認(rèn)應(yīng)用重啟后能讀到Oracle的最新數(shù)據(jù)整個(gè)回退耗時(shí)控制在40分鐘內(nèi)。所以我的建議是回退演練必須包含數(shù)據(jù)層和應(yīng)用層兩層驗(yàn)證。數(shù)據(jù)層驗(yàn)證反向同步是否完整、一致應(yīng)用層驗(yàn)證連接切換、緩存清理、配置修改是否順利。只測(cè)數(shù)據(jù)不測(cè)應(yīng)用回退后業(yè)務(wù)還是起不來(lái)。5. 常見(jiàn)問(wèn)題與排查技巧實(shí)錄整個(gè)遷移過(guò)程中我們踩過(guò)的坑和排查過(guò)的問(wèn)題不少挑幾個(gè)有代表性的寫在這里供大家參考。5.1 Oracle的CONNECT BY在PostgreSQL里怎么改Oracle的層次查詢CONNECT BY PRIOR是遷移的高頻改造點(diǎn)。PostgreSQL沒(méi)有直接對(duì)應(yīng)的語(yǔ)法要用遞歸CTEWITH RECURSIVE改寫。比如Oracle里SELECT emp_id, mgr_id, LEVEL FROM emp START WITH mgr_id IS NULL CONNECT BY PRIOR emp_id mgr_id;改成PostgreSQLWITH RECURSIVE emp_tree AS ( SELECT emp_id, mgr_id, 1 AS level FROM emp WHERE mgr_id IS NULL UNION ALL SELECT e.emp_id, e.mgr_id, t.level 1 FROM emp e JOIN emp_tree t ON e.mgr_id t.emp_id ) SELECT emp_id, mgr_id, level FROM emp_tree;這里注意遞歸CTE的性能調(diào)優(yōu)空間比較大如果樹的深度和寬度都很大比如超過(guò)10層、幾十萬(wàn)節(jié)點(diǎn)建議提前用真實(shí)數(shù)據(jù)量壓測(cè)必要時(shí)加search_depth之類的優(yōu)化手段。5.2ROWNUM和ROW_NUMBER()的等價(jià)改寫Oracle里用ROWNUM做分頁(yè)很常見(jiàn)SELECT * FROM ( SELECT t.*, ROWNUM rn FROM big_table t ) WHERE rn BETWEEN 101 AND 200;PostgreSQL里直接用LIMIT/OFFSETSELECT * FROM big_table ORDER BY id LIMIT 100 OFFSET 100;這里有個(gè)容易踩的隱形坑Oracle的ROWNUM是在排序之前生成的所以如果原SQL里寫了ORDER BY再套R(shí)OWNUM語(yǔ)義上是先取前N行再排序和PostgreSQL的LIMIT語(yǔ)義先排序再取前N行不一樣。改寫時(shí)必須先確認(rèn)原SQL的業(yè)務(wù)意圖不然數(shù)據(jù)就錯(cuò)了。5.3 CLOB字段遷移到TEXT后的性能問(wèn)題Oracle的CLOB在PostgreSQL里一般對(duì)應(yīng)TEXT類型?？雌饋?lái)簡(jiǎn)單但實(shí)測(cè)發(fā)現(xiàn)一個(gè)現(xiàn)象某些業(yè)務(wù)對(duì)CLOB字段做頻繁的UPDATE和SELECT在Oracle里有專門的LOB段和空間管理性能還行到PostgreSQL的TEXT上如果寫入的文本很大幾十KB到幾MBTOAST機(jī)制會(huì)觸發(fā)壓縮和外部存儲(chǔ)性能會(huì)有波動(dòng)。我們的處理辦法把大文本字段單獨(dú)拆到一張擴(kuò)展表里和主表用外鍵關(guān)聯(lián)需要時(shí)才JOIN出來(lái)。如果業(yè)務(wù)確實(shí)要頻繁讀寫大文本還可以把external存儲(chǔ)打開避免每次查詢都加載整個(gè)大字段。5.4 空字符串與NULL的語(yǔ)義差異Oracle里和NULL是等價(jià)的SELECT NVL(, x) FROM dual返回x。PostgreSQL里不是NULL。這一條差異在數(shù)據(jù)遷移時(shí)特別容易產(chǎn)生臟數(shù)據(jù)。我們?cè)谛ｒ?yàn)階段發(fā)現(xiàn)不少表的某些字段兩邊值看起來(lái)不一致查了半天才發(fā)現(xiàn)是源庫(kù)存在空字符串遷移時(shí)我們統(tǒng)一轉(zhuǎn)換成了NULL但業(yè)務(wù)代碼里有用判斷的邏輯導(dǎo)致行為變了。后來(lái)我們定了規(guī)范Oracle到PostgreSQL遷移時(shí)空字符串按照Oracle語(yǔ)義統(tǒng)一轉(zhuǎn)NULL同時(shí)業(yè)務(wù)代碼里所有 的判斷改為IS NULL。這個(gè)規(guī)范在改造階段就要同步給開發(fā)團(tuán)隊(duì)。5.5 分區(qū)表遷移的正確姿勢(shì)Oracle的分區(qū)表遷移到PostgreSQL有幾種方案用PostgreSQL原生分區(qū)表基于聲明式分區(qū)或者用繼承分區(qū)或者用pg_partman這類擴(kuò)展。我們的經(jīng)驗(yàn)如果分區(qū)數(shù)量不大幾十到幾百個(gè)用原生聲明式分區(qū)就夠了如果分區(qū)數(shù)量上千原生分區(qū)的元數(shù)據(jù)管理會(huì)有壓力可以考慮用pg_partman管理自動(dòng)建分區(qū)。遷移時(shí)的一個(gè)常見(jiàn)坑Oracle分區(qū)表的全局索引和分區(qū)索引在PostgreSQL里沒(méi)有完全對(duì)應(yīng)的概念需要根據(jù)業(yè)務(wù)SQL的過(guò)濾條件重新設(shè)計(jì)索引策略。不要照搬Oracle的索引結(jié)構(gòu)否則查詢性能會(huì)很差。5.6 序列遷移的斷檔問(wèn)題Oracle的SEQUENCE不保證連續(xù)PostgreSQL的SEQUENCE也一樣。但兩邊序列的CACHE和INCREMENT配置不同步可能導(dǎo)致ID沖突或斷檔。我們遷移時(shí)把Oracle序列的LAST_NUMBER和INCREMENT_BY記錄到PostgreSQL序列的同名參數(shù)上還要考慮增量同步階段兩邊應(yīng)用各拿各的序列號(hào)等切換后PostgreSQL序列起始值必須比Oracle當(dāng)前值大很多否則切過(guò)來(lái)后新生成的ID可能和同步回來(lái)的歷史數(shù)據(jù)撞車。這個(gè)問(wèn)題真的很隱蔽但如果爆了就是主鍵沖突直接拖垮切換。建議切換前用腳本檢查所有序列的當(dāng)前值確保PostgreSQL側(cè)的值遠(yuǎn)高于Oracle側(cè)。5.7 常見(jiàn)問(wèn)題速查表問(wèn)題現(xiàn)象常見(jiàn)原因排查/處理辦法校驗(yàn)時(shí)哈希不一致字段拼接順序不一致、NULL/空串沒(méi)統(tǒng)一、浮點(diǎn)精度差異統(tǒng)一拼接規(guī)則字段值做標(biāo)準(zhǔn)化增量同步延遲持續(xù)增大Kafka消費(fèi)能力不足、目標(biāo)庫(kù)應(yīng)用能力差、大事務(wù)卡住增加消費(fèi)者并行度排查大事務(wù)必要時(shí)手動(dòng)補(bǔ)數(shù)切換后應(yīng)用報(bào)驅(qū)動(dòng)錯(cuò)誤JDBC連接串或驅(qū)動(dòng)版本不兼容Postgres JDBC驅(qū)動(dòng)與Oracle JDBC驅(qū)動(dòng)用法不同檢查URL參數(shù)、schema設(shè)置存儲(chǔ)過(guò)程執(zhí)行失敗PL/SQL和PL/pgSQL語(yǔ)法差異人工改寫重點(diǎn)處理游標(biāo)、動(dòng)態(tài)SQL、異常處理查詢性能嚴(yán)重下降統(tǒng)計(jì)信息未收集、索引缺失、優(yōu)化器差異切換后立即執(zhí)行ANALYZE根據(jù)執(zhí)行計(jì)劃補(bǔ)索引回退時(shí)反向同步失敗類型映射不一致、主鍵沖突、循環(huán)依賴提前演練建立主鍵沖突處理規(guī)則類型映射表要用同一份我自己走完這個(gè)項(xiàng)目后最深的體會(huì)是數(shù)據(jù)庫(kù)遷移拼的不是單點(diǎn)技術(shù)有多強(qiáng)而是流程管理和風(fēng)險(xiǎn)把控。這三個(gè)目標(biāo)——低中斷、可校驗(yàn)、可回退——每一個(gè)都對(duì)應(yīng)著一整套方案和工具鏈而它們之間又互相影響。建議準(zhǔn)備動(dòng)手的朋友先把你現(xiàn)有的停機(jī)窗口、數(shù)據(jù)量、業(yè)務(wù)容忍度都擺出來(lái)用這些邊界條件去倒推每一步該怎么做。方案設(shè)計(jì)得越細(xì)執(zhí)行的時(shí)候才越穩(wěn)。再補(bǔ)充一點(diǎn)遷移過(guò)程中所有腳本和命令一定要先在小環(huán)境里完整演練一遍帶上真實(shí)數(shù)據(jù)量的1%到5%壓測(cè)一下性能別等到正式切換才發(fā)現(xiàn)導(dǎo)出速度、校驗(yàn)耗時(shí)、增量延遲這些數(shù)字完全不可控。