🐧 PostgreSQL 高效能備份策略：pg_dump / pg_dumpall / pg_basebackup / PITR 全比較與最佳實務指南

🐧 PostgreSQL 高效能備份策略：pg_dump / pg_dumpall / pg_basebackup / PITR 全比較與最佳實務指南

PostgreSQL 的備份機制功能強大，但彈性高也意味著複雜度高。
許多人會問：到底該用 pg_dump、pg_dumpall、pg_basebackup，還是直接上 PITR（Point In Time Recovery）？

本篇完整解析每一種備份方式的特性、效能影響、優缺點與適用情境，並提供企業級備份/還原範本與指令，可搭配你先前 50 萬筆資料實驗結果運用於實際專案（sitemap 已收錄：pg500k 基準測試）:contentReference[oaicite:2]{index=2}。

---

📌 一、全備份方式總覽：物理 vs 邏輯

PostgreSQL 備份分成兩大類：

方式	類型	描述	適用情境
pg_dump	邏輯	單一資料庫匯出（schema + data）	部分備份、跨版本遷移、資料範例匯出
pg_dumpall	邏輯	整個 PostgreSQL cluster 匯出（含 roles）	小型系統完整匯出
pg_basebackup	物理	完整備份 data directory（base + pg_wal）	HA、備援、建立 replica、日常例行備份
PITR	物理 + WAL	回復到任意時間點，需 WAL 持續備份	金融、支付系統、不可接受資料遺失

核心結論： 若你是一般服務 → 每日 pg_basebackup + WAL 即可。 若你是大型企業 → 必做 PITR。 若你要跨版本 → pg_dump。

---

📚 二、pg_dump：最常用的邏輯備份

適合部分資料庫備份或跨版本升級，例如：PostgreSQL 13 → 17。

1. 匯出單一 DB

pg_dump -U postgres -d mydb -F c -f mydb.dump

• -F c：Custom 格式，可用 pg_restore 還原
• -j：可多工，提高效能（大資料庫必用）

2. 多工備份

pg_dump -U postgres -d mydb -F c -j 8 -f mydb.dump

3. 還原

pg_restore -U postgres -d newdb -j 8 mydb.dump

邏輯備份特點：

• 跨版本相容
• 可選擇性備份 table/schema
• 不含全系統資訊（如 roles）

---

📦 三、pg_dumpall：完整邏輯備份（含 roles）

可把整個 PostgreSQL cluster（多 DB + roles）匯出成一個可讀 SQL。

pg_dumpall -U postgres > full_backup.sql

適合：

• 小型系統
• 測試環境搬移
• 備份 roles 與 global objects（pg_dump 不會備份）

不適合：

• 大型資料庫（SQL 恢復速度慢）
• 需要持續 WAL 的場景（沒有 WAL）

---

🚀 四、pg_basebackup：企業最常用的物理備份

直接複製 PostgreSQL data directory，適合：

• 備援建立（replica）
• HA（例如 Patroni、repmgr）
• 日常定時備份

1. 基本備份

pg_basebackup -U replica -D /backup/2025-12-03 -Fp -Xs -P

參數說明：

• -Fp：Plain 格式（或 -Ft tar）
• -Xs：包含 WAL
• -P：顯示進度

2. 使用壓縮備份（PostgreSQL 15+）

pg_basebackup -U replica -D backup -Ft -z -Z 6

3. 建立 replica（最常用）

pg_basebackup -h primary -D /var/lib/postgresql/17/main \
  -R -U replica -P -Fp -Xs

完成後自動產生 replica 設定（standby.signal）。

---

🕒 五、PITR — Point In Time Recovery

PITR 是將 restore 準確回到「任意秒」的技術，是企業不可或缺的備援策略。 尤其當你誤刪資料、UPDATE 錯誤、DROP TABLE、資料被汙染時，只有 PITR 能救。

啟用 WAL 備份

修改 postgresql.conf：

archive_mode = on
archive_command = 'cp %p /archived_wal/%f'
wal_level = replica
max_wal_senders = 10

觸發備份（供 PITR 使用）

SELECT pg_start_backup('daily_backup');

備份物理資料（用 pg_basebackup 或 rsync）。

SELECT pg_stop_backup();

進行還原到特定秒

在 recovery.conf（或 postgresql.auto.conf）加入：

restore_command = 'cp /archived_wal/%f %p'
recovery_target_time = '2025-12-03 13:12:45'

啟動 PostgreSQL：

pg_ctl start

資料庫將自動 replay WAL 回到指定秒。

---

📊 六、效能比較：哪種方式最快？（通用基準）

以你已建立的 50 萬筆測試資料量級為標準（參考 sitemap）:contentReference[oaicite:3]{index=3}。

方式	備份速度	還原速度	支援跨版本	適用情境
pg_dump	中	中～慢	✔	部分表 / schema 匯出、遷移
pg_dumpall	慢	慢	✔	小系統全備
pg_basebackup	快	快	✘	HA、日常備份、replica 建立
PITR	快	快～中（看 WAL）	✘	金融、支付、重要系統

如果你要建立 HA 或 replica：pg_basebackup 必用
如果你要跨版本：pg_dump 必用
如果你要精準到任意時間秒：PITR 是唯一選擇

---

🛡 七、企業級備份策略設計（強烈建議）

以下是最穩健的實務策略：

每日        ：pg_basebackup（完整物理備份）
每 5 分鐘   ：WAL 備份（PITR）
每週        ：pg_dump（關鍵表）
每週        ：完整還原演練
異地備份    ：S3 / NFS / PBS / rsync
自動化      ：systemd + cron + shell script

資料庫備份不是「做了」就算，而是：

你必須能成功還原 → 備份才算成立。

---

🧪 八、你可直接使用的備份腳本範本（可放 cron）

1. pg_basebackup 自動備份腳本

#!/bin/bash
DATE=$(date +"%Y-%m-%d")
DEST="/backup/pg/$DATE"

pg_basebackup -U replica -D $DEST -Fp -Xs -P -R
find /backup/pg/ -type d -mtime +7 -exec rm -rf {} \;

2. WAL 備份輪替腳本

#!/bin/bash
ARCHIVE=/archived_wal
mkdir -p $ARCHIVE
find $ARCHIVE -type f -mtime +3 -exec rm {} \;

---

🩹 九、排錯清單：常見問題與處理方式

• WAL 不斷增長？ → archive_command 沒成功。
• pg_basebackup 卡住？ → max_wal_senders 不夠。
• PITR 無法回復？ → restore_command 未正確取 WAL。
• pg_dump 匯出異常慢？ → 缺少 -j 多工。
• replica 無法同步？ → primary_conninfo 未設定。

---

💬 互動留言引導（備份與還原討論區）

若你在 PostgreSQL 備份 / 還原、PITR 或 pg_basebackup 過程中遇到：

• WAL 不備份、PITR 無法還原
• pg_dump 巨量資料備份速度過慢
• replica 建置失敗或同步延遲
• 跨版本備份後還原失敗

歡迎直接留言貼出指令與錯誤訊息，我會協助分析並可能整理成補充篇加入本文。

---

🔗 延伸閱讀

• 📊 PostgreSQL 50 萬筆資料建立與備份還原效能完整實驗
• 🧱 Linux NFS 完整指南：伺服器架設、權限管理與效能調校
• 📡 Linux 監控工具完整指南：top / htop / glances / nmon / dstat
• 🌐 Proxmox VE 企業部署藍圖：Cluster、Ceph、ZFS、HA、備份全攻略
• 📁 Linux 檔案系統比較：EXT4 / XFS / Btrfs / ZFS 深度解析

— WWFandy・PostgreSQL Backup Notes