主題: PostgreSQL

PostgreSQL 備份入門：pg_dump、WAL 與 PITR 到底怎麼分工？

用誤刪訂單的情境理解 RPO／RTO、SQL 與 custom archive 的還原工具、WAL 向前重播，以及 PITR 的停止邊界與隔離還原驗收。

假設上午九點，一個缺少 WHERE 的刪除操作已經提交。訂單不見了，副本也跟著同步。備份資料夾裡有幾個檔案，但現在該開哪一個？能回到八點五十九分嗎？會不會順手把後來的正常訂單也弄丟？

我會先問「要找回哪個時間點、服務多久能恢復」，再決定備份工具。Crunchy Data 的備份入門文適合先建立整體概念；下面的工具行為以 PostgreSQL 18 文件為準。

RPO 看資料，RTO 看服務

RPO 是 Recovery Point Objective，能接受的資料遺失時間範圍。RTO 是 Recovery Time Objective，能接受服務中斷多久。它們是事前訂的目標，演練結果才告訴我們有沒有做到。

假設服務在 20:00 中斷，復原後的資料只到 19:52，核心功能在 20:35 恢復。這次資料缺口是 8 分鐘，停機是 35 分鐘。如果原本目標是 RPO 5 分鐘、RTO 60 分鐘，就只有後者達標。還原指令跑得快，不能補回缺少的資料；備份很新，也不保證服務很快能恢復。Microsoft Learn有這兩種需求的定義與取捨。

計時也要講清楚。資料庫載入完成後，若還要調整權限、接回檔案儲存、確認登入與建立訂單，這些工作都會影響服務恢復時間。

pg_dump 匯出的是資料庫的邏輯內容

邏輯備份保存重建資料庫所需的定義與資料；實體備份保存資料庫叢集的檔案。PostgreSQL 的 cluster 在這裡是由同一個 server 管理的一組資料庫，不一定是多台主機。

pg_dump屬於前者，通常一次處理一個 database。它使用一致的快照，所以「10:00 建立快照，10:08 匯出結束」不表示 10:05 新提交的訂單會出現在裡面。匯出完成時間與資料截止點要分開記。

範圍也能調整。--schema-only 匯出結構，--data-only 匯出資料。前者適合研究結構，後者可用於已有相容結構的載入流程，但都不是單靠一個旗標就完成的完整復原方案。結構中的函式或設定也可能敏感，不能把「沒有資料列」直接當成可以公開。

一般讀寫可與 pg_dump 並行，但匯出仍會消耗資源。它取得的 ACCESS SHARE lock，會和需要 ACCESS EXCLUSIVE 的操作衝突。若同時安排某些 migration，就可能有人等鎖。每種 ALTER TABLE 的鎖不同，不能一概而論；排程前應核對鎖模式。

檔名不決定還原工具

先看產生方式與內容格式，再看副檔名：

把 custom archive 從 lesson.dump 改名成 lesson.sql，內容完全沒變。反過來，叫做 .backup 的檔案也可能是純文字 SQL。SQL Dump 文件說明兩種格式的還原路徑。

若已確認手上是 custom archive，可以先執行這個不連線寫入資料庫的檢查：

pg_restore --list lesson.dump

它列出 archive 的目錄，不會證明每個物件都能還原。pg_restore 文件將這個步驟與實際載入分開。ANALYZE則對資料庫收集查詢規劃器的統計資料，不能拿來查看備份檔。

以下只是已建立、空白且可丟棄的 restore_lab 資料庫範例，兩種格式擇一使用，不要對正式資料庫照貼：

# 純文字 SQL
psql -X --set=ON_ERROR_STOP=on --dbname=restore_lab --file=lesson.sql

# custom archive
pg_restore --exit-on-error --dbname=restore_lab lesson.dump

遇錯停止與全部回滾不同，先前成功的指令可能已提交。要另外設計交易、清理與重試流程，也要確認版本與相依項目。備份只能來自可信來源，還原可能執行來源端放進去的程式碼。本文沒有對正式資料庫執行這些指令。

WAL 是可以重播的變更紀錄

WAL 是 Write-Ahead Logging。簡化來說，PostgreSQL 會先把相關變更寫進日誌，再把修改過的資料頁寫到持久儲存；當機後便能重播必要紀錄，恢復一致狀態。提交成功前要等到哪個持久化階段，則受設定影響。WAL 原理與提交設定有更精確的說明。

WAL 不是一份可直接交給 psql 的 SQL 歷史，也不是通用的撤銷日誌。單看主機上有 pg_wal 目錄，更不能推論已保留數週歷史。舊 segment 可能被回收，要有可用的基礎備份、必要 WAL 的連續保存與監控，才能規劃歷史復原。

PITR 從舊狀態向前重播，停在要保留的位置

PITR 是 Point-in-Time Recovery，時間點復原。典型流程是先載入合適的實體基礎備份，再重播同一還原鏈的 WAL，停在目標位置。pg_basebackup可以建立實體基礎備份；pg_dump 的邏輯匯出不能接上 WAL 當成這種復原的起點。

以抽象時間線說明，假設基礎備份已能恢復成 08:00 的一致狀態，後續 WAL 連續且匹配：

08:00 可用的基礎狀態
  → 重播 08:30 的正常訂單
  → 停在 09:00 錯誤交易提交之前
  × 不重播已提交的誤刪

手上若只有 12:00 的基礎狀態，就不能期待重播 WAL 把它倒退到 08:59。能復原的範圍必須落在這份備份與 WAL 鏈實際涵蓋的區間內。連續封存文件也說明它復原的是整個 cluster，不是直接替換正式環境某張表。

停止在哪裡，需要另外確認。錯誤紀錄只精確到秒時，「回到九點」仍可能包含錯誤交易。recovery_target_inclusive 會影響是否包含剛好等於目標的提交，預設為 on。核對實際提交、時區與包含邊界後，還要在隔離環境檢查資料。重播到 WAL 尾端雖然能拿到較新的資料，卻也可能把錯誤刪除一起重播。Recovery Target 文件是這裡的設定依據。

副本同步很快，也可能很快同步誤刪

如果錯誤 DELETE 已提交，副本也已套用，直接切換副本不會找回舊資料。副本與歷史復原解決的是不同需求。事故發現得晚，還要確認保存期間是否涵蓋那麼久以前的狀態。

找回舊資料後也不要整張蓋回去。事故之後可能又有合法新訂單或修改。先在隔離環境復原，再比對識別值、內容差異、外鍵關聯與新資料，才能規劃合併。把整個正式資料庫退回過去，會同時放棄那個時間點之後的正常變更。

演練要驗證應用程式真的能用

還原演練時，我會檢查：

• 備份可讀、還原沒有未處理錯誤，重要資料與 constraints 正確，不能只比總筆數。
• 目標環境具備必要角色、owner、GRANT 與 extension。單一 database 的 dump 不會自動建立全叢集角色，extension 也可能需要主機上的相容支援檔案。
• 以應用程式角色驗證讀寫與核心流程，接回必要的設定和檔案儲存。隔離演練先停用會寄信、扣款或呼叫正式 webhook 的背景工作。
• 記錄取得備份、載入、重播、修正相依項目與功能驗證各花多久，再和 RPO／RTO 比較。若需要三小時，目標卻是一小時，就得找出瓶頸、調整方案並重測。
• 檢查備份與正式環境的共同故障風險。放在另一個儲存空間，但同一組權限仍可刪除全部副本時，隔離仍不夠。

角色與物件權限可對照權限文件，extension 的主機需求可對照CREATE EXTENSION。資料庫還原完畢，只完成了服務復原的一部分。

我學到什麼

• 我會先用資料時間點與服務中斷時間分別檢查 RPO／RTO，再談備份頻率與工具。
• 我會依內容格式選 psql 或 pg_restore。列得出 archive 目錄，只是開始檢查，不能當成復原證據。
• 我把 PITR 理解成從基礎狀態向前重播。要救誤刪資料，就要確認停止邊界，並保護事故後的合法變更。
• 我會把角色、相依項目與應用程式功能放進演練驗收。這些觀念仍需要實作量測，才能知道自己的系統能否達標。

外部參考資料

• PostgreSQL 18：Backup and Restore，依需求選擇備份方法。
• PostgreSQL 18：pg_dump、pg_restore 與 psql，核對格式、參數與錯誤處理。
• PostgreSQL 18：Continuous Archiving and PITR，確認基礎備份與 WAL 的關係。

延伸學習

• Philip Hurst／Crunchy Data：Introduction to Postgres Backups，先讀白話總覽，再回官方文件查細節。
• Sonia Valeja／PGConf India 2025：pgBackRest，了解工具如何管理備份與還原。講者與活動可由 Percona 原始活動頁核對，適合概念建立後再看。