博客 / 詳情

返回

PG預寫式日誌解碼的藝術與應用

本文整理自 IvorySQL 2025 生態大會暨 PostgreSQL 高峯論壇的演講分享，演講嘉賓：李傳成，walminer 作者。

本文內容主要包括：

  • 邏輯解碼的基本原理
  • 高級邏輯解碼特性
  • walminer 數據恢復實戰
  • walminer pgto server 實戰

邏輯解碼的基本原理

數據庫 INSERT 操作的 WAL 日誌解析與使用流程

lichuancheng1.png

物理使用（二進制回放流程）

當數據庫執行INSERT操作時，WAL 日誌會記錄以下關鍵信息，用於物理層面的數據回放：

  • RelfileNode：定位數據文件名（對應數據庫中存儲表數據的物理文件，如帶後綴的表文件標識）。
  • PageNo：定位數據頁號（確定數據在物理文件中的具體頁位置）。
  • offSet：定位頁內偏移（確定數據在頁內的具體位置）。
  • Data：存儲 INSERT 操作的二進制數據內容。

流程邏輯為：通過RelfileNode找到數據文件 → 由PageNo定位到文件內的頁 → 藉助offSet確定頁內數據位置 → 最終對Data進行二進制拷貝，完成物理層面的日誌回放（如備庫同步、數據庫重啓恢復時的底層數據還原）。

邏輯使用（可讀數據解析流程）

若需將 WAL 日誌解析為人類可讀的邏輯數據，流程如下：

  1. 確定表名：通過RelfileNode關聯數據庫數據字典（如 PostgreSQL 的系統表），查詢其對應的模式（Schema）和表名。
  2. 確定字段列表：根據表名，從系統表（如pg_attribute）中獲取該表的字段定義（字段名、類型等）。
  3. 數據解釋：基於字段列表，解析Data中存儲的二進制數據，還原出 INSERT 操作的具體數據內容（如“插入了哪些列、對應什麼值”）。
  4. 結果表現：通過解碼插件（如wal2json、test_decoding的格式化輸出），將解析結果以可讀形式呈現（如 JSON、文本格式）。

數據庫 DELETE 操作的 WAL 日誌解析與使用流程

lichuancheng2.png

WAL 日誌結構説明

DELETE 操作的 WAL 記錄由以下核心元素構成：

  • DELETE 標識：明確操作類型為刪除。
  • RelfileNode：用於定位數據文件的唯一標識。
  • PageNo：數據頁在物理文件中的編號。
  • offSet：頁內偏移量，精準定位待刪除行。
  • Data（可選）：內容隨 WAL 級別（物理/邏輯）動態變化，邏輯日誌中通常包含主鍵定位信息。

物理使用：底層刪除標記流程

物理層面的 WAL 回放用於實現數據的底層刪除標記，流程如下：

  1. 定位數據文件：通過RelfileNode確定對應的物理數據文件。
  2. 定位數據頁：藉助PageNo找到文件內的目標數據頁。
  3. 定位行偏移：通過offSet定位頁內待刪除的行。
  4. 標記刪除：對目標行的頭部設置xmax（事務標識），完成物理層面的刪除標記（該流程用於數據庫恢復、備庫同步等場景，保障底層數據結構的一致性）。

邏輯使用：可讀刪除條件解析流程

若需將 DELETE 日誌解析為人類可讀的邏輯操作，流程如下：

  1. 確定表名：通過RelfileNode關聯數據字典（如 PostgreSQL 系統表），確定操作的目標表。
  2. 確定主鍵字段列表：DELETE 操作僅需主鍵字段即可唯一定位行，因此需解析表的主鍵定義。
  3. 主鍵解釋：從日誌中提取主鍵的具體值，還原出“刪除哪一行”的邏輯條件。
  4. 結果表現：通過解碼插件（如wal2json）將結果格式化為可讀形式（例如“DELETE FROM 表名 WHERE 主鍵=XXX”）。

數據庫 UPDATE 操作的 WAL 日誌解析與性能影響

lichuancheng3.png

UPDATE 的 WAL 日誌結構（“舊行刪除 + 新行插入”的組合設計）

UPDATE 操作的 WAL 記錄包含以下核心元素，體現“舊行標記刪除、新行插入”的底層邏輯：

  • UPDATE 標識：明確操作類型為更新。
  • RelfileNode：定位數據文件的唯一標識。
  • PageNo Old/offSet Old：定位舊行在物理文件中的頁和偏移。
  • PageNo New/offSet New：定位新行在物理文件中的頁和偏移。
  • New Data / Delta Data：存儲新行的完整數據或僅變更的增量數據。
  • Identity Data（可選）：獨立存儲用於條件判斷的標識字段（如主鍵）。

物理使用：底層更新的二進制流程

物理層面的 WAL 回放嚴格遵循“舊行標記刪除 + 新行二進制插入”的步驟：

  1. 舊行標記刪除：通過PageNo Old和offSet Old定位舊行，設置其xmax（事務標識），完成邏輯刪除標記。
  2. 新行二進制寫入：通過PageNo New和offSet New定位新行位置，將New Data/Delta Data以二進制形式直接拷貝寫入；若為 Delta Data（增量數據），則先提取舊行基礎數據，覆蓋變更字段後再完成新行寫入。

邏輯使用：數據解析與日誌膨脹風險

邏輯層面需將 UPDATE 日誌解析為可讀的更新操作，同時需關注邏輯日誌級別下的 WAL 膨脹問題：

  1. 表與字段定位：通過RelfileNode關聯數據字典確定表名，再從系統表中獲取該表的全數字段列表（邏輯日誌級別下，無論實際更新字段多少，均會存儲全字段數據）。

  2. 新數據與標識數據解釋：

    • New Data：邏輯日誌級別下，即使僅更新 1 個字段，也會存儲表中所有字段的新值（這是 WAL 日誌膨脹的核心誘因）。
    • Identity Data：獨立存儲用於條件判斷的標識字段（如主鍵）；若表的標識級別配置為full，還會額外存儲所有舊字段值，進一步放大日誌體積。
  3. 日誌膨脹案例：以“100 字段表僅更新 1 個字段”為例，邏輯日誌會存儲 100 個新字段值 +（若為 full 標識級別）100 箇舊字段值 + 獨立的 Identity Data，導致 WAL 日誌膨脹率達 200%以上，對存儲和性能影響顯著。

WAL 記錄的內容變種

INSERT 操作在不同 WAL 級別下的日誌結構與差異

lichuancheng4.png

1. 核心概念説明
  • FPI（Full Page Image）：全頁鏡像，PostgreSQL 在 WAL 中記錄的整頁數據，用於應對“頁面撕裂”場景的恢復一致性。
  • replica 級別/系統表：面向物理複製或系統表操作的 WAL 配置，聚焦底層數據的物理一致性。
  • logical 級別：面向邏輯解碼（如數據審計、邏輯複製）的 WAL 配置，需解析出人類可讀的邏輯操作。
2. replica 級別（或系統表）的 INSERT 日誌結構

根據是否包含 FPI，日誌結構分為兩種：

  • 不帶 FPI：日誌包含INSERT標識、RelfileNode（數據文件標識）、PageNo（頁號）、offSet（頁內偏移）、Data（新插入的二進制數據）。該結構用於常規插入場景，依賴 WAL 的增量記錄保障一致性。
  • 帶 FPI：日誌包含INSERT標識、RelfileNode、PageNo、offSet、FPI（整頁數據鏡像）。此時不存儲Data，恢復時直接通過 FPI 覆蓋整頁，適用於“頁面撕裂風險高”的場景（如 checkpoint 間隔大時）。
3. logical 級別（邏輯解碼）的 INSERT 日誌結構

邏輯級別下的 INSERT 日誌存在數據冗餘設計：

  • 帶 FPI 時，日誌同時包含FPI（整頁數據）和Data（新插入數據）。從設計合理性看，FPI 本身已包含頁面數據，本可直接用於邏輯解碼的信息提取，卻額外存儲Data，造成 WAL 日誌膨脹。
  • 該冗餘對性能的影響與checkpoint配置強相關：若checkpoint配置密集（觸發 FPI 的場景少），則性能影響微乎其微；若checkpoint間隔大（FPI 頻繁觸發），則可能因冗餘加劇 WAL 寫入壓力，此時優化該設計（複用 FPI 進行邏輯解碼）可帶來一定性能提升。

DELETE 操作在不同 WAL 級別下的日誌結構與差異

lichuancheng5.png

1. 核心概念回顧
  • FPI（Full Page Image）：全頁鏡像，用於物理層面的頁面一致性恢復。
  • Identity Data：標識數據（如主鍵），用於邏輯解碼時唯一定位被刪除的行。
  • replica 級別/系統表：聚焦物理複製或系統表操作的 WAL 配置，保障底層數據物理一致性。
  • logical 級別：面向邏輯解碼的 WAL 配置，需解析出可讀的刪除條件。
2. replica 級別（或系統表）的 DELETE 日誌結構

根據是否包含 FPI，日誌結構分為兩種：

  • 不帶 FPI：日誌包含DELETE標識、RelfileNode（數據文件標識）、PageNo（頁號）、offSet（頁內偏移）。該結構下，DELETE 操作僅需定位行後設置xmax（事務標識）即可完成物理層面的刪除標記，無需額外數據存儲。
  • 帶 FPI：在上述基礎上加入FPI（全頁鏡像）。此時通過 FPI 覆蓋整頁來保障“頁面撕裂”場景下的物理一致性，恢復時直接以全頁鏡像還原數據。
3. logical 級別（邏輯解碼）的 DELETE 日誌結構

邏輯級別下的 DELETE 日誌需滿足“可讀刪除條件”的解析需求，結構如下：

  • 不帶 FPI：日誌包含DELETE、RelfileNode、PageNo、offSet，並額外加入Identity Data（標識數據，如主鍵）。Identity Data用於邏輯解碼時明確“刪除哪一行”的條件（如DELETE FROM 表名 WHERE 主鍵=XXX）。
  • 帶 FPI：同時包含FPI（保障物理恢復）和Identity Data（服務邏輯解碼）。FPI 滿足底層頁面一致性，Identity Data 滿足邏輯行定位需求，二者結合支撐物理與邏輯的雙重場景。

UPDATE 操作在不同 WAL 級別下的日誌結構與邏輯解碼要點

lichuancheng6.png

1. replica 級別（或系統表）的 UPDATE 日誌結構

UPDATE 在 replica 級別下的日誌可理解為“INSERT + DELETE”的物理層面組合，結構分為兩種：

  • 不帶 FPI：日誌包含UPDATE標識、RelfileNode（數據文件標識）、PageNo New/offSet New（新行定位）、PageNo Old/offSet Old（舊行定位）、New Delta（新行的增量數據）。僅記錄變更的增量信息，保障物理複製的高效性。
  • 帶 FPI：將New Delta替換為FPI（全頁鏡像），通過整頁覆蓋實現“頁面撕裂”場景下的物理一致性恢復，此時不存儲增量數據，直接依賴 FPI 完成新行的二進制寫入。
2. logical 級別（邏輯解碼）的 UPDATE 日誌結構（按“是否帶 FPI”“是否更新標識列”細分）

邏輯級別下的 UPDATE 日誌因標識列是否更新和是否啓用 FPI呈現複雜差異，這也是邏輯解碼易踩坑的核心場景：

  • 不帶 FPI：

    • 未更新標識列：日誌包含UPDATE、RelfileNode、新舊PageNo/offSet、New Data（新行全量數據）。因標識列未更新，邏輯解碼時可直接從New Data中提取標識列（如主鍵），無需額外存儲舊數據。
    • 更新標識列：在上述基礎上新增Identity Data（標識數據，如更新後的主鍵）。此時標識列被修改，需單獨存儲新標識以明確“更新後的數據歸屬”，邏輯解碼時通過Identity Data定位新行的標識條件。

  • 帶 FPI：

    • 未更新標識列：日誌包含UPDATE、RelfileNode、新舊PageNo/offSet、New Data、FPI。FPI保障物理層面的頁面一致性，New Data服務邏輯解碼的新行數據解析。
    • 更新標識列：在上述基礎上再新增Identity Data，同時滿足“物理頁面恢復（FPI）”“新行數據解析（New Data）”“標識列變更定位（Identity Data）”三重需求。
3. 邏輯解碼的避坑要點

在解析 logical 級別 UPDATE 日誌時，需重點關注標識列是否更新：

  • 若未更新標識列，New Data中已包含標識信息，無需額外依賴舊數據即可定位行。
  • 若更新標識列，需通過Identity Data明確新標識，否則易因標識列變更導致數據關聯錯誤。

這一設計細節是 PostgreSQL 為平衡“邏輯可讀性”與“存儲效率”的權衡，也是邏輯解碼開發中需重點理解的技術坑點——只有明確標識列的更新狀態，才能準確解析“更新了哪一行、更新後的數據是什麼”的邏輯語義。

高級邏輯解碼實現

我們常遇到以下三個問題：

  1. logical 日誌級別帶來的 wal 膨脹
  2. UNDO 語句生成
  3. DDL 變更捕獲

如何解決這 3 個問題呢？那麼就要依靠高級邏輯解碼實現。

lichuancheng7.png

在物理複製中，使用磁盤上切實存在的 tuple 作為變更受體完成 delete 或者 update 操作，而在邏輯解碼中因為無法確定的在當前 wal 中找到 tuple 變更受體。因而需要額外記錄大量的新舊數據來完成邏輯變更數據的確定。

PG 依賴 FPW 體系，這意味着當前 WAL 記錄前序相對不遠的 WAL 中，一定存在本 WAL 修改的 page 的全頁。這將使在 replica wal 級別下做邏輯解碼變為可能。

lichuancheng8.png

基於 WAL 日誌的分層存儲與內存管理機制，邏輯解碼工具可通過以下流程，在 logical 級別下實現REDO SQL 與 UNDO SQL 的生成：

  1. FPI 內存緩存

    當工具解析到帶 FPI（全頁鏡像）的 WAL 記錄（如PAGE A FPI）時，會將該 FPI 對應的頁數據完整緩存至內存，建立“頁標識-全頁數據”的映射關係，為後續元組（tuple）定位提供基礎。

  2. 同頁操作的舊 tuple 定位

    當解析到針對同一 page（如 PAGE A）的寫入類 WAL 記錄（如PAGE A INSERT或PAGE A UPDATE）時，工具從內存緩存的 FPI 中定位到操作對應的舊 tuple（元組）（即“尋找 tuple 受體”的過程）。

  3. 新舊 tuple 拼裝與 SQL 生成
  • 基於舊 tuple 的結構，結合 WAL 記錄中的新數據（如 INSERT 的 Data、UPDATE 的 New Delta），拼裝出完整的新 tuple。
  • 利用舊 tuple 生成UNDO SQL（用於回滾操作），利用新 tuple 生成REDO SQL（用於重演操作），從而在 logical 級別下完成邏輯解碼，實現數據變更的語義級解析。

lichuancheng9.png

上述操作既能解析普通表，也能解析系統表，那麼我們也可以解析出一條語句來。

向 pg_class 中插入一條數據，其 oid 如上圖所示，向 pg_attribute 中插入一行數據，其字段如上圖所示。那麼我們即可拼出上圖最下方的 DDL。

基本上所有的 DDL 都可以通過這種方式去找到復原方法，這就是在 replica 級別完成 DDL 解碼的過程。

WALMINER

lichuancheng10.jpg

WALMINER 的核心技術與產品應用

一、核心技術優勢

WALMINER 的核心技術突破體現在以下四點：

  • replica 級別邏輯解碼與實例級批量處理：可在 replica 日誌級別完成邏輯解碼，且支持“一次讀取 WAL 日誌，完成多 DB 實例（如 DB1、DB2、DB3）的批量解碼”，效率顯著提升。
  • DB 級 DDL 動態識別與同步：無需預先生成數據字典，對新創建的數據庫、表等 DDL 操作可自動識別，業務側無需執行初始化或訂閲修改操作，適配性極強。
  • 無數據庫入侵設計：作為獨立工具，不依賴數據庫內部能力，可脱離 PG 運行環境部署，對數據庫資源無侵佔，避免了傳統解碼工具對數據庫性能的影響。
  • 多版本全兼容：單工具支持 PG 10 至 PG 18 全系列版本的 WAL 日誌解碼，降低了多版本環境下的工具適配成本。

二、產品應用場景

基於核心技術，WALMINER 衍生出CDC（變更數據捕獲）和數據恢復兩大產品方向：

1. CDC（變更數據捕獲）
  • PGto：端到端的數據同步 demo 方案，僅需兩條命令即可完成跨 PG 版本（如 PG 10→PG 17）的數據同步，操作極簡但暫為 demo 級別，適合測試場景。
  • PGto server：商用級服務化方案，功能等價於“wal2json”中間件，可通過接口獲取邏輯槽內的 SQL 變更，已在多家企業實現生產級落地。
2. 數據恢復
  • wal2sql：提供 WAL 日誌的 SQL 解碼命令，為數據恢復提供基礎解析能力。
  • search 工具：解決“海量 WAL 日誌中定位誤操作位點”的行業痛點，可快速鎖定數據誤操作的時間或邏輯位置。
  • blockrecover 命令：針對數據庫快照、穿刺等緊急場景的高效數據找回方案，依賴基礎備份+後續 WAL 日誌，恢復速度比數據庫原生方式快數十倍。

WALMINER 數據恢復實戰

場景：

APP 出現 bug，生成錯誤的 UPDATE SQL，在操作數據庫時導致表 A 更新大量無關數據行。

恢復步驟：

  1. 生成數據字典

lichuancheng11.png

執行一個命令行工具，指定數據字典的位置，指定用户名。

  1. 檢索誤操作位點

lichuancheng12.png

要利用 WALMINER 的search命令定位誤操作，可按以下流程操作：

（1） 命令參數配置：執行walminer search時，需指定 -D（第一步生成的數據字典文件）、-w（WAL 日誌的生產目錄和歸檔目錄列表）、-b（誤操作涉及的數據庫）、-r（誤操作涉及的“模式.表”，如public.t1）。

（2） 自動化分析輸出：工具會遍歷指定 WAL 日誌，輸出每個事務的詳細統計，包括事務 ID（xid）、LSN 範圍（start_lsn/end_lsn）、insert/update/delete 操作次數、提交 LSN 等（如示例中“xid=853 insert=901”“xid=856 update=10”等條目）。

（3） 人工判別誤操作：這是流程中唯一需要 DBA 人工介入的環節——需結合操作時間、類型（insert/update/delete）和數據量，從輸出的事務列表中識別出誤操作對應的事務。

  1. 精準生成 UNDO SQL

lichuancheng13.png

通過 wal2sql 命令，精準的找回 undo 語句，然後人為確定這些語句沒有問題，防止出現 bug。

  1. 恢復數據

lichuancheng14.png

只需通過psql -f命令執行生成的 SQL 恢復腳本（如wal2sql_result.sql），再通過常規 SQL 查詢驗證數據，整個數據恢復流程操作簡潔，即使是數據庫新手也能輕鬆完成。

誤操作觀察

lichuancheng15.png

wal2sql 命令 1：

walminer wal2sql -k 4 -m 1 -L 0/9023de8 -s 856 -D ~/wp/dic/walminer.dic -w [/data/pg_datas/pg15/archive_dir],[/data/pg_datas/pg15/data/pg_wal] -F 2 -t 2 -f ~/wal2sql_result.sql

wal2sql 命令 2：

walminer wal2sql -k 4 -m 1 -L 0/9023de8 -s 856 -D ~/wp/dic/walminer.dic -w [/data/pg_datas/pg15/archive_dir],[/data/pg_datas/pg15/data/pg_wal] -t 2 -f ~/wal2sql_result.sql

誤操作深度挖掘

lichuancheng16.png

如果誤操作了一個表之後又執行了正常的業務，應該如何去恢復數據？

WALMINER

lichuancheng17.png

WALMINER 的解決方案可按以下邏輯落地：

  1. 生成數據字典：先通過工具生成目標數據庫的數據字典，為後續解析提供元數據支撐。
  2. 執行日誌檢索：定位涉及bank_account表的 WAL 日誌範圍。

  3. 帶-c參數執行wal2sql命令：該參數會觸發 WALMINER 的分層事務分析能力，自動列出：

    • 原始誤操作事務（如將id=1,2,3設為balance=1的更新）；
    • 誤操作後對污染數據的後續更新（即“follow 操作”，如id=3的balance+100操作）。

用户可基於這些事務明細，人工判定最終應恢復的正確數據狀態（例如區分“誤操作前的原始值”“誤操作後的值”“後續業務更新後的值”）。後續將集成“自動推薦最優 UNDO SQL”功能，無需人工介入即可輸出最可能的回滾語句，進一步簡化數據恢復的決策流程。

walminer pgto server 實戰

pgto 使命

PGTO 致力於極簡的 CDC 部署，目前已實現一鍵式 CDC 部署，可以完成集簇級的數據同步,同時支持新建 DB 實例同步，新建表同步，支持雙向同步。

pgto 操作步驟

  1. 初始化 pgto
  2. 啓動 pgto CDC

pgto server 操作步驟

  1. 初始化 pgto server

lichuancheng18.png

  1. 創建訂閲

lichuancheng19.png

  1. 啓動 pgto server

lichuancheng20.png

server 運行後的訂閲方案：

lichuancheng21.png

  1. PGTO 插入測試數據

lichuancheng22.png

  1. PGTO server 消費

lichuancheng23.png

pgto server 優勢

  1. 低 wal 級別
  2. 生產庫 wal 堆積風險低
  3. 集簇級解析，不佔用數據庫資源
  4. DDL 識別
數據庫 , postgresql
user avatar tizuqiudexiangpica 頭像 u_12865 頭像 old_it 頭像
3 位用戶收藏了這個故事！

發佈 評論

Some HTML is okay.