探索

不同時間點還原

閱讀時間約 15 分鐘

🪨Stone
  1. 把一個cluster底下的所有資料夾都刪掉，再還原
  2. 在同一個server開另外一個新的cluster用原本的備份檔還原
  3. 之前演練過了，可以還原任何時間點 啊這個作的規劃是異常演練規劃嗎？是塞一大筆資料然後看要還原多久嗎？我具體需要做什麼東西出來給你勒？

情境 1：要還原的時間點資料還在本地 pg_wal中

這通常發生在：剛誤刪資料，且該交易非常新，pg_wal 段還沒被切換（switch）或尚未被 pgBackRest 成功歸檔到遠端。

  • 還原邏輯： 結合 pgBackRest 倉庫的數據 + 本地存活的 WAL 檔案。

  • 操作：

    1. 保護現場： 立刻停止資料庫，將 /var/lib/pgsql/data/pg_wal/ 目錄整個複製出來（例如存到 /tmp/wal_rescue）。

    2. 執行 Restore： 使用 pgBackRest 還原最近的一個備份。

    3. 手動補齊： 還原後，將 /tmp/wal_rescue 裡面的所有檔案拷貝回還原後的 pg_wal/ 目錄中。

    4. 設定目標： 在還原參數中加入 --type=time 指定精確的誤刪前時間點。

  • 結果： 資料庫會先從 Repository 下載 Archive WAL，最後銜接本地的 WAL 段，達成「零資料遺失」還原。

情境 2：只有一個 Full Backup（且 WAL 已歸檔）

這是最標準的 PITR (Point-In-Time Recovery) 情境。你能還原到多晚的時間，取決於你的 archive-push 是否成功將最後的 WAL 傳送到 repo。

  • 還原邏輯： 依靠 Full Backup 作為地基，再由 pgBackRest 自動拉取 Archive WAL 進行重播。

  • 操作：

    pgbackrest --stanza=your_db restore --type=time \
--target="2025-12-23 11:00:00" --delta

  • 結果： 資料庫會恢復到該 Full Backup 狀態，然後一路執行 WAL 直到 11:00:00 停止。

情境 3：只有 Full Backup，但「沒有任何archived WAL」

如果您關閉了 archive_mode，或者 WAL 倉庫損毀了。

  • 還原邏輯： 只能還原到「備份完成的那一刻」。

  • 限制： 您無法指定任何備份時間點之後的時間。資料庫啟動後，數據會停留在備份結束時的一致性狀態（Consistent Point）。

  • 缺點： 備份之後到災難發生前的所有交易全部遺失。

情境 4：有多個備份（Full + Incremental/Differential）

如果平時有做增量備份，pgBackRest 會自己找最快的還原路徑。

  • 還原邏輯：

    1. 自動選擇距離目標時間點「最近且更早」的一個備份（可能是週日的 Full 或週二的 Incremental）。

    2. 只還原該備份後的差異檔案。

    3. 再重播剩下的 WAL。

  • 優點： 速度最快。比起只用 Full Backup，它減少了需要「重播」的 WAL 數量（重播 WAL 是單執行緒，通常比拷貝檔案慢）。

補充情境 5：Database Instance 完全毀損，且無本地 pg_wal

當整台伺服器起火或硬碟壞掉時。

  • 還原邏輯： 100% 依賴 pgBackRest 倉庫（Repository）。

  • 操作關鍵： 在新伺服器安裝同版本的 PostgreSQL，設定好 pgbackrest.conf 指向遠端倉庫（如 S3 或備份伺服器），直接執行 restore。

  • 限制： 您最多只能還原到「最後一個成功歸檔到倉庫」的 WAL 時間點。

總結對照表

情境必備組件還原精確度資料遺失風險
本地 WAL 還在Full Backup + Archive WAL + 本地 pg_wal極高 (秒級)近乎零
標準 PITRFull Backup + Archive WAL高 (秒級)遺失最後未歸檔的部分
僅有備份檔Full Backup僅限備份當下遺失備份後的所有資料
增量備份Incremental + Archive WAL高 (秒級)速度最快，風險同 PITR

🛠 演練目標達成清單

  1. 資料庫損毀還原：透過 rm -rf 清空測試目錄後執行 restore，驗證了從零開始重建資料庫的能力。

  2. 損毀前還原 (PITR)：利用 --type=time 與 --target 參數，成功讓資料庫回到特定時間點的「唯讀檢查」狀態。

🚧 演練過程中遇到的問題與對策

以下是您在操作中遇到的關鍵卡點及其解決方法，這對於未來編寫 SOP 非常有價值：

1. 權限與路徑障礙

  • 問題：無法使用 cd 或 ls 進入 /var/lib/postgresql/ 目錄，且 sudo rm -rf * 無法清空檔案。

  • 原因：該目錄權限為 700，僅 postgres 使用者可存取；* 號在 sudo 下會因權限不足無法展開。

  • 對策：使用 sudo -u postgres -i 切換身分，或使用 sudo bash -c "rm -rf ..." 來強制穿透權限。

2. WAL 歸檔超時 (Error 082)

  • 問題：備份或檢查時出現 WAL segment ... was not archived before timeout。

  • 原因：測試實例的 archive_command 指向錯誤的 Stanza，或資料庫尚未重啟套用設定。

  • 對策：修正 postgresql.conf 中的 --stanza 名稱，重啟服務，並執行 SELECT pg_switch_wal(); 強制推送日誌。

3. 設定路徑錯誤 (Error 037)

  • 問題：出現 option 'pg1-path' must be specified。

  • 原因：pgBackRest 收到相對路徑的 WAL 但不知實體對應位置。

  • 對策：在 pgbackrest.conf 的 Stanza 區段明確加上 pg1-path 與 pg1-port。

4. 還原時間點超出範圍 (Fatal Error)

  • 問題：還原時顯示 recovery ended before target was reached。

  • 原因：設定的 --target 時間太晚，該時段的 WAL 日誌尚未歸檔到儲存庫中，或已隨目錄清空而遺失。

  • 對策：查閱日誌確認最後一個有效時間點，調整 --target 時間重新還原。

5. pgAdmin 長時間轉圈圈

  • 問題：還原後連線資料庫，查詢一直顯示「正在等待查詢完成」。

  • 原因：使用了 --target-action=pause，資料庫停在目標點且處於唯讀暫停狀態。

  • 對策：執行 SELECT pg_wal_replay_resume(); 解除暫停，讓資料庫恢復正常運作。

📝 進階演練心得：promote vs pause

  • pause (本次採用)：還原後「停看聽」，方便檢查資料，但會導致 pgAdmin 轉圈圈或 systemctl 報錯。

  • promote：還原後直接開機變主機，適合追求 RTO（復原時間目標）的正式災難接管。

這是一個非常實務的切入點。在醫院或企業環境中，我們最常遇到的不是「伺服器整台燒掉」，而是**「某筆資料被誤刪，需要從備份中撈回來，但不能影響目前運作中的主資料庫」**。

這就是您提到的「異地還原」或「偏移還原」的核心價值。

🛠️ 「現在的備份還原到其他 Cluster」的做法

當您想要把現有的 test-backup 備份，還原到一個全新的路徑（例如 Port 5434 的 remote 實例）時，最精簡且不改設定檔的做法是使用 指令參數覆蓋。

1. 核心還原指令

您不需要修改 /etc/pgbackrest/pgbackrest.conf，直接在執行 restore 時指定目的地：

Bash

sudo -u postgres pgbackrest --stanza=test-backup \
  --pg1-path=/var/lib/postgresql/16/remote \
  --delta \
  restore

2. 進階：結合「撈資料」的時間點控制

通常您是為了補回某個時間點的資料，所以會結合 PITR 參數：

Bash

sudo -u postgres pgbackrest --stanza=test-backup \
  --pg1-path=/var/lib/postgresql/16/remote \
  --type=time \
  --target="2026-01-19 07:00:00" \
  --target-action=pause \
  --delta restore

  • --pg1-path：告訴 pgBackRest「不要還原到預設路徑，請改塞到 /16/remote」。

  • --target-action=pause：還原後先「暫停」，讓您進去 5434 慢慢撈資料，確認無誤後再刪掉這個臨時實例。

🧐 您的問題與觀念統整

您在討論中提出了一些非常具備運維直覺的問題，這裡幫您系統性地整理：

Q1：為什麼還原到其他路徑不需要新增 Stanza？

  • 答案：因為 Stanza 是「備份的定義」而非「還原的限制」。

  • 解釋：Stanza 告訴系統資料從哪裡來、要備份到哪。還原時，pgBackRest 已經擁有備份檔了，它只需要您下指令告訴它「目的地在哪」。只要您能指定 --pg1-path，它就能把資料倒進任何您有權限的路徑。

Q2：pg1-path 是什麼？有 pg2-path 嗎？

  • 答案：pg-path 定義了資料庫檔案的實體位置。

  • pg1-path：主實例的路徑。

  • pg2-path：通常用於主從架構（High Availability）。當您有兩台伺服器同步時，pgBackRest 可以設定 pg2-path 指向從庫，平時從「從庫」抓備份以減輕「主庫」的效能壓力。

Q3：有人會準備一台空的 Server 專門等待主庫壞掉再還原嗎？

  • 答案：極少見。

  • 解釋：如您所言，正式環境通常會做「主從即時同步（Streaming Replication）」。但「還原到新路徑」的操作，通常用於以下非災難性場景：

    • 外科手術式救援：主庫正常運行，但要找回兩天前被刪除的某一筆記錄。

    • 升級演練：在旁邊開一個 100% 真實資料的分身，測試新的 SQL 語法或索引。

    • 稽核抽查：還原某個歷史時間點的狀態供金管或審計單位查詢。

💡 本次演練的下一步建議

既然您已經了解如何將資料偏移還原到 Port 5434，接下來最實務的練習是：「如何跨庫補資料」。

想像一下：

  1. Port 5433 (主庫)：目前運作中，但少了一筆 patient_id=99 的資料。

  2. Port 5434 (還原庫)：您剛才透過 pgBackRest 救回來的歷史快照。

您想學如何透過 postgres_fdw 指令，直接在一個 SQL 視窗內，把 5434 的那筆資料「抓進」5433 嗎？這在醫院運維補資料時非常強大。

灌資料演練

cghadmin@cghpsql:~$ sudo -u postgres pgbench -i -s 70 -p 5433 postgres
[sudo] password for cghadmin:
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
7000000 of 7000000 tuples (100%) done (elapsed 7.70 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 10.45 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 7.80 s, vacuum 0.22 s, primary keys 2.42 s).
cghadmin@cghpsql:~$ time sudo -u postgres pgbackrest --stanza=test-backup --type=full backup
2026-01-21 06:42:22.997 P00   INFO: backup command begin 2.50: --exec-id=1582397-ce90d415 --log-level-console=info --log-level-file=detail --pg1-path=/var/lib/postgresql/16/test --pg1-port=5433 --process-max=2 --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=test-backup --type=full
2026-01-21 06:42:23.703 P00   INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes
2026-01-21 06:46:42.305 P00   INFO: backup start archive = 000000030000000000000041, lsn = 0/41000028
2026-01-21 06:46:42.305 P00   INFO: check archive for prior segment 000000030000000000000040
2026-01-21 06:46:54.937 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2026-01-21 06:46:55.137 P00   INFO: backup stop archive = 000000030000000000000041, lsn = 0/41000170
2026-01-21 06:46:55.141 P00   INFO: check archive for segment(s) 000000030000000000000041:000000030000000000000041
2026-01-21 06:46:55.156 P00   INFO: new backup label = 20260121-064223F
2026-01-21 06:46:55.202 P00   INFO: full backup size = 1GB, file total = 1581
2026-01-21 06:46:55.202 P00   INFO: backup command end: completed successfully (272207ms)
2026-01-21 06:46:55.202 P00   INFO: expire command begin 2.50: --exec-id=1582397-ce90d415 --log-level-console=info --log-level-file=detail --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=test-backup
2026-01-21 06:46:55.210 P00   INFO: repo1: 16-1 remove archive, start = 000000010000000000000001, stop = 000000010000000000000008
2026-01-21 06:46:55.211 P00   INFO: expire command end: completed successfully (9ms)

real    4m32.235s
user    0m0.004s
sys     0m0.006s



cghadmin@cghpsql:~$ time sudo -u postgres pgbackrest --stanza=test-backup restore
2026-01-21 06:57:54.190 P00   INFO: restore command begin 2.50: --exec-id=1582597-6b6c62eb --log-level-console=info --log-level-file=detail --pg1-path=/var/lib/postgresql/16/test --process-max=2 --repo1-path=/var/lib/pgbackrest --stanza=test-backup
2026-01-21 06:57:54.203 P00   INFO: repo1: restore backup set 20260121-064223F, recovery will start at 2026-01-21 06:42:23
2026-01-21 06:57:58.776 P00   INFO: write updated /var/lib/postgresql/16/test/postgresql.auto.conf
2026-01-21 06:57:58.790 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2026-01-21 06:57:58.791 P00   INFO: restore size = 1GB, file total = 1581
2026-01-21 06:57:58.792 P00   INFO: restore command end: completed successfully (4604ms)

real    0m4.622s
user    0m0.003s
sys     0m0.006s

演練項目原始資料量備份耗時還原耗時系統負載 (CPU)
基準測試 A1.1 GB

2GB

還原

測試項目資料量備份耗時 (B)還原耗時 (B)與 1GB 相比之倍率
基準測試 B2.2 GB

3GB

還原

自動化腳本

PostgreSQL 備份還原自動化測試

使用連線資訊: 主機: 10.31.155.37 使用者: cghadmin 認證方式: 密碼 ✓ 成功連線到 10.31.155.37

使用自訂測試大小: 15GB 將執行 1 個測試項目…

  • 15GB (scale factor: 1024, 預期約 15360 MB) 自動開始測試…

============================================================ 開始測試: 15GB (scale factor: 1024)

============================================================

[步驟 1/6] 資料填充…

開始執行: 資料填充 (15GB) ✓ 完成: 資料填充 (15GB) - 耗時: 2分44秒, 平均 CPU: 0.00%, 峰值 CPU: 0.00%

[步驟 2/6] 記錄資料量大小… ✓ 資料庫大小: 15 GB

[步驟 3/6] 執行備份…

開始執行: 備份 (15GB) ✗ 失敗: 備份 (15GB) - 退出碼: 82 錯誤訊息: [sudo] password for cghadmin: ERROR: [082]: WAL segment 0000000F00000008000000ED was not archived before the 60000ms timeout HINT: check the archive_command to ensure that all options are corre

[步驟 4/6] 停止 PostgreSQL… ✓ PostgreSQL 已停止

[步驟 5/6] 刪除資料目錄… ✓ 資料目錄已刪除

[步驟 6/6] 執行還原…

開始執行: 還原 (15GB) ✓ 完成: 還原 (15GB) - 耗時: 0分13秒, 平均 CPU: 0.00%, 峰值 CPU: 0.00%

重新啟動 PostgreSQL… ✓ PostgreSQL 已啟動

✓ 測試報告已生成:

  • JSON: test_results.json
  • 文字報告: test_results_report.txt

✓ SSH 連線已關閉 #postgreSQL

關係圖譜

1

目錄