備份與還原（Backup & Restore）：從 pg_dump 到 PITR 時間點恢復 | PostgreSQL

備份與還原是資料庫管理員最核心的職責。PostgreSQL 提供多層次的備份策略：從輕量的 邏輯備份（pg_dump）到完整的 物理備份（pg_basebackup），再到結合 WAL 歸檔的 PITR（Point-in-Time Recovery）時間點恢復。本文將從基礎到進階，完整建立你的災難復原能力。

備份類型總覽

在開始之前，先了解 PostgreSQL 三種主要備份方式的差異：

類型	代表工具	優點	缺點
邏輯備份	pg_dump / pg_dumpall	跨版本還原、可選擇性還原	速度慢、不支援 PITR
物理備份	pg_basebackup / pgBackRest	速度快、支援 PITR	只能整庫還原、不能跨主版本
儲存快照	ZFS / LVM / Cloud Snapshot	幾乎瞬間完成	依賴底層儲存、一致性需額外處理

RPO 與 RTO 概念

選擇備份策略前，必須先定義兩個關鍵指標：

RPO（Recovery Point Objective）——允許損失的最大資料量（以時間衡量）。RPO = 1 小時代表最壞情況會損失 1 小時的資料
RTO（Recovery Time Objective）——允許的最長服務中斷時間。RTO = 15 分鐘代表必須在 15 分鐘內完成還原並上線

RPO 要求        → 備份策略
─────────────────────────────────────────────
24 小時         → 每日 pg_dump 全備即可
1 小時          → 每日全備 + 每小時 WAL 歸檔
5 分鐘          → 物理全備 + 持續 WAL Streaming
接近 0          → Streaming Replication（同步複本）

RTO 要求        → 策略
─────────────────────────────────────────────
數小時          → 從遠端儲存還原全備 + 重放 WAL
30 分鐘以內     → 備份放在本地快速儲存
5 分鐘以內      → Warm Standby（備援機即時接手）
< 1 分鐘        → Hot Standby / Patroni 自動 Failover

WAL 歸檔在備份中的角色

WAL（Write-Ahead Log）是 PostgreSQL 所有資料變更的完整記錄。物理備份只是某一時刻的靜態快照，必須搭配 WAL 才能實現 PITR。

WAL Archiving 的任務是：當 WAL segment（預設 16MB）寫滿後，在被 PostgreSQL 回收之前，複製到安全的歸檔位置：

PostgreSQL
  │
  ├── 寫入 WAL segment (pg_wal/000000010000000000000001)
  │
  ├── segment 寫滿（16MB）→ 觸發 archive_command
  │       archive_command = 'cp %p /archive/%f'
  │
  └── 確認成功（exit code = 0）後才允許回收

歸檔目錄 /archive/
    000000010000000000000001   ← 最舊
    000000010000000000000002
    000000010000000000000003   ← 最新

pg_dump — 邏輯備份

pg_dump 是最常用的邏輯備份工具，透過 MVCC 機制取得一致性快照，不需停機。

輸出格式選擇

# -Fp (plain) — 純文字 SQL，可直接閱讀，但無法並行還原
pg_dump -U postgres -Fp mydb > mydb.sql

# -Fc (custom) — 壓縮自訂格式，支援選擇性還原（推薦）
pg_dump -U postgres -Fc mydb > mydb.dump

# -Fd (directory) — 目錄格式，每個表一個檔案，支援 --jobs 並行
pg_dump -U postgres -Fd mydb -f /backup/mydb_dir/

# -Ft (tar) — tar 格式，可搭配 pg_restore
pg_dump -U postgres -Ft mydb > mydb.tar

常用參數與實務範例

# 並行 dump（只支援 -Fd 格式），-j 指定 worker 數量
pg_dump -U postgres -Fd mydb -j 4 -f /backup/mydb_dir/

# 排除特定表
pg_dump -U postgres -Fc mydb \
  --exclude-table=audit_logs \
  --exclude-table=sessions \
  > mydb_no_audit.dump

# 只備份特定 schema
pg_dump -U postgres -Fc mydb -n public -n reporting > mydb_selected.dump

# 只備份結構（不含資料）
pg_dump -U postgres -Fc --schema-only mydb > mydb_schema.dump

# 只備份資料（不含 DDL）
pg_dump -U postgres -Fc --data-only mydb > mydb_data.dump

# 排除暫存表的資料（保留結構）
pg_dump -U postgres -Fc mydb \
  --exclude-table-data='tmp_*' \
  > mydb_no_tmp_data.dump

# 設定 Lock 等待逾時，避免長時間卡住
PGPASSWORD=secret pg_dump \
  -h db.example.com -p 5432 -U postgres \
  --lock-wait-timeout=30s \
  -Fc mydb > mydb.dump

# 透過 SSH 管道直接傳輸到遠端
pg_dump -U postgres -Fc mydb | \
  ssh backup@nas "cat > /nas/backup/mydb_$(date +%Y%m%d).dump"

備份驗證

# 列出 dump 檔案的內容目錄（不解壓）
pg_restore --list mydb.dump | head -30

# 只還原結構到測試庫進行驗證
pg_restore -U postgres -d testdb --schema-only mydb.dump

pg_dumpall — 全叢集備份

pg_dumpall 備份整個 PostgreSQL cluster，包含所有資料庫、Role 和 Tablespace 定義：

# 完整全叢集備份（輸出純文字 SQL）
pg_dumpall -U postgres > cluster_full.sql

# 只備份全域物件（Role、Tablespace）— 不備份資料庫內容
pg_dumpall -U postgres --globals-only > cluster_globals.sql

# 只備份 Role 定義
pg_dumpall -U postgres --roles-only > cluster_roles.sql

# 帶壓縮的全叢集備份
pg_dumpall -U postgres | gzip > cluster_full_$(date +%Y%m%d).sql.gz

注意：pg_dumpall 不支援自訂格式（-Fc），輸出固定為 plain SQL。還原時通常先用 pg_dumpall --globals-only 還原角色，再用 pg_restore 還原各個資料庫。

pg_restore — 還原邏輯備份

# 還原到已存在的資料庫
pg_restore -U postgres -d mydb mydb.dump

# 建立資料庫並還原（-C 旗標）
pg_restore -U postgres -C -d postgres mydb.dump

# 並行還原（-j），速度可大幅提升
pg_restore -U postgres -d mydb -j 4 mydb.dump

# 只還原特定表
pg_restore -U postgres -d mydb -t orders -t order_items mydb.dump

# 只還原特定 schema
pg_restore -U postgres -d mydb -n reporting mydb.dump

# 列出內容後選擇性還原（two-step）
pg_restore --list mydb.dump > restore_list.txt
# 編輯 restore_list.txt，移除不需要的項目
pg_restore -U postgres -d mydb -L restore_list.txt mydb.dump

pg_basebackup — 物理備份

pg_basebackup 透過 Streaming Replication Protocol 取得整個資料目錄的一致性快照，是 PITR 的基礎。

核心參數詳解

# 基本物理備份
pg_basebackup -U replicator -h localhost -D /backup/base/

# -X stream：同步備份 WAL（standalone 可獨立還原）
pg_basebackup -U replicator -h localhost -D /backup/base/ -X stream

# -R：自動產生 standby.signal（用於設定 Standby）
pg_basebackup -U replicator -h localhost -D /backup/standby/ -X stream -R

# -P：顯示備份進度
pg_basebackup -U replicator -h localhost -D /backup/base/ -X stream -P

# -Z：壓縮備份等級
pg_basebackup -U replicator -h localhost -D /backup/base/ -X stream -Z 9

# PG15+ 指定壓縮算法
pg_basebackup -U replicator -h localhost -D /backup/base/ \
  -X stream --compress=lz4:1

# 輸出為 tar 格式（便於儲存與傳輸）
pg_basebackup -U replicator -h localhost -Ft \
  -D /backup/base_tar/ -X stream -Z 6

完整生產級範例

# tar 壓縮 + 進度 + WAL streaming + 日誌
pg_basebackup \
  -h db.prod.example.com \
  -U replicator \
  -p 5432 \
  -D /backup/$(date +%Y%m%d_%H%M%S)/ \
  -Ft \
  -X stream \
  -Z 6 \
  -P \
  -v \
  2>&1 | tee /var/log/pg_basebackup.log

PostgreSQL 設定（允許 basebackup 連線）

-- 建立 Replication 用戶
CREATE ROLE replicator LOGIN REPLICATION PASSWORD 'replicpass';

# postgresql.conf
wal_level = replica          -- 至少要 replica（PITR 最低需求）
max_wal_senders = 3          -- 允許的 WAL sender 連線數
archive_mode = on            -- 開啟 WAL 歸檔（PITR 必要）

# pg_hba.conf
host  replication  replicator  10.0.0.0/24  scram-sha-256

pg_verifybackup — 備份驗證（PG13+）

# 驗證備份目錄（比對 backup_manifest 中的 checksums）
pg_verifybackup /backup/base/

# 跳過 checksum 驗證（速度快）
pg_verifybackup --skip-checksums /backup/base/

# 驗證 tar 格式備份
pg_verifybackup -m /backup/base_tar/backup_manifest /backup/base_tar/

PITR — 時間點恢復

PITR（Point-in-Time Recovery）允許將資料庫還原到任意歷史時間點，是物理備份 + WAL 歸檔的組合，也是 PostgreSQL 最強大的災難復原能力。

步驟一：設定 WAL 歸檔

# postgresql.conf
wal_level = replica
archive_mode = on

# 複製到本地目錄（防覆蓋寫法）
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

# 複製到遠端（rsync）
# archive_command = 'rsync -a %p backup@nas:/pg_archive/%f'

# 上傳到 AWS S3
# archive_command = 'aws s3 cp %p s3://mybucket/pg-archive/%f'

# 強制每 N 秒歸檔（即使 segment 未寫滿），決定最大 RPO
archive_timeout = 300   -- 5 分鐘

PG15+ 新增 archive_library 可用 C extension 取代 shell command，效能更好且錯誤處理更精確。

步驟二：執行物理備份

# 使用 pg_basebackup 取得基礎備份
pg_basebackup -U replicator -h localhost -D /backup/base/ -X stream -P

步驟三：災難發生，開始還原

# 1. 停止 PostgreSQL
sudo systemctl stop postgresql

# 2. 備份當前（損壞的）資料目錄
sudo mv /var/lib/postgresql/17/main /var/lib/postgresql/17/main.broken

# 3. 還原物理備份
sudo mkdir -p /var/lib/postgresql/17/main
sudo -u postgres tar -xzf /backup/base.tar.gz -C /var/lib/postgresql/17/main/

# 4. 建立 recovery.signal（PG12+）
sudo -u postgres touch /var/lib/postgresql/17/main/recovery.signal

步驟四：設定 recovery 參數

# postgresql.conf（或 postgresql.auto.conf）

# WAL 歸檔來源
restore_command = 'cp /archive/%f %p'

# 還原目標：指定時間點（ISO 8601 格式）
recovery_target_time = '2026-07-05 14:30:00 UTC'

# 其他目標選項（擇一使用）：
# recovery_target_lsn = '0/15D68C8'           -- 指定 LSN
# recovery_target_xid = '12345'                -- 指定 transaction ID
# recovery_target_name = 'before_migration_v2' -- 命名還原點

# 是否包含目標時間點的 transaction
recovery_target_inclusive = true

# 達到目標後的動作
# promote = 自動提升為 Primary（預設）
# pause   = 暫停，等待 DBA 確認
# shutdown = 完成後關閉
recovery_target_action = 'pause'

步驟五：啟動並確認恢復

# 啟動 PostgreSQL
sudo systemctl start postgresql

# 監控恢復進度
sudo tail -f /var/log/postgresql/postgresql-*.log
# 預期日誌：
# LOG: starting point-in-time recovery to 2026-07-05 14:30:00+00
# LOG: restored log file "000000010000000000000001" from archive
# LOG: consistent recovery state reached at 0/12345AB
# LOG: pausing at the end of recovery

-- 若 recovery_target_action = 'pause'，確認資料正確後繼續
SELECT pg_wal_replay_resume();

-- 確認已離開恢復模式
SELECT pg_is_in_recovery();
-- 應返回 false

-- 驗證還原後的資料狀態
SELECT MAX(created_at) FROM orders;

pgBackRest — 企業級備份工具

pgBackRest 是目前最廣泛採用的 PostgreSQL 備份工具，支援增量備份、並行處理、多儲存後端（S3、GCS、Azure）。

基本設定

# /etc/pgbackrest/pgbackrest.conf

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2          # 保留 2 份完整備份
repo1-retention-diff=4          # 保留 4 份差異備份
compress-type=lz4
compress-level=3
log-level-console=info

# 使用 S3 作為儲存後端
# repo1-type=s3
# repo1-s3-bucket=mybucket
# repo1-s3-region=ap-northeast-1

[main]                          # stanza 名稱
pg1-path=/var/lib/postgresql/17/main
pg1-port=5432
pg1-user=postgres

# postgresql.conf（配合 pgBackRest）
archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_mode = on
wal_level = replica

日常操作

# 初始化 stanza（首次設定）
sudo -u postgres pgbackrest --stanza=main stanza-create

# 全量備份
sudo -u postgres pgbackrest --stanza=main backup --type=full

# 差異備份（自上次 full 以來的變更）
sudo -u postgres pgbackrest --stanza=main backup --type=diff

# 增量備份（自上次任何備份以來的變更）
sudo -u postgres pgbackrest --stanza=main backup --type=incr

# 並行備份（指定進程數）
sudo -u postgres pgbackrest --stanza=main backup --type=full --process-max=4

# 查看備份清單
sudo -u postgres pgbackrest --stanza=main info

# 驗證備份完整性
sudo -u postgres pgbackrest --stanza=main check

PITR 還原

# 還原到最新狀態
sudo -u postgres pgbackrest --stanza=main restore

# 還原到指定時間點
sudo -u postgres pgbackrest --stanza=main restore \
  --type=time "--target=2026-07-05 14:30:00"

# Delta restore（只更新有變更的檔案，速度更快）
sudo -u postgres pgbackrest --stanza=main restore --delta

自動化排程

# /etc/cron.d/pgbackrest

# 每週日凌晨 1 點全量備份
0 1 * * 0  postgres pgbackrest --stanza=main backup --type=full

# 週一至週六凌晨 1 點差異備份
0 1 * * 1-6 postgres pgbackrest --stanza=main backup --type=diff

# 每小時驗證歸檔狀態
0 * * * *  postgres pgbackrest --stanza=main check

三層備份策略

生產環境建議採用三層備份策略，兼顧安全性與效率：

Layer 1：全量物理備份（每週）
  ├── 工具：pgBackRest --type=full
  ├── 頻率：每週日凌晨
  ├── 保留：2 份（2 週）
  └── 目的：PITR 的起點

Layer 2：差異備份（每日）
  ├── 工具：pgBackRest --type=diff
  ├── 頻率：週一至週六凌晨
  ├── 保留：7 份
  └── 目的：縮短 WAL 重放量（縮小 RTO）

Layer 3：WAL 持續歸檔（即時）
  ├── 工具：archive_command 或 pgBackRest
  ├── 保留：至少覆蓋最舊的全量備份
  └── 目的：實現細粒度 PITR

不同業務場景的策略選擇：

場景	RPO	RTO	建議策略
小型應用	24 小時	4 小時	每日 pg_dump + 遠端儲存
中型業務	1 小時	1 小時	全備(週) + WAL 歸檔(5分鐘)
電商平台	15 分鐘	30 分鐘	全備(日) + WAL(1分鐘) + Warm Standby
金融系統	0 分鐘	5 分鐘	Streaming Replication + pgBackRest + Hot Standby

PG17 增量備份

PostgreSQL 17 正式引入內建的 Incremental Backup，是有史以來最重要的備份改進之一：

# 啟用 WAL summarizer
# postgresql.conf
summarize_wal = on

# 執行全量備份（作為增量基礎）
pg_basebackup -U replicator -D /backup/full_20260705/ -X stream

# 執行增量備份（只備份變更的 blocks）
pg_basebackup -U replicator -D /backup/incr_20260706/ \
  --incremental=/backup/full_20260705/backup_manifest \
  -X stream

# 合併增量備份為完整備份
pg_combinebackup \
  /backup/full_20260705 \
  /backup/incr_20260706 \
  -o /backup/combined_20260706/

# 驗證合併結果
pg_verifybackup /backup/combined_20260706/

PG17 增量備份的優勢：

官方原生支援，不依賴第三方工具
pg_walsummarizer 進程追蹤修改的 block 清單
增量備份大小可縮小至全備的 5~20%
pg_combinebackup 合併多個增量為完整備份

備份監控

-- 查看 WAL 歸檔狀態
SELECT archived_count,
       last_archived_wal,
       last_archived_time,
       failed_count,
       last_failed_wal
FROM pg_stat_archiver;

-- 計算歸檔延遲（秒）
SELECT EXTRACT(EPOCH FROM (NOW() - last_archived_time))::int
       AS archive_lag_seconds
FROM pg_stat_archiver;

-- 查看當前 WAL 位置
SELECT pg_current_wal_lsn(),
       pg_current_wal_insert_lsn();

-- 查看備份進度（若有 pg_basebackup 正在執行）
SELECT pid, application_name, state,
       sent_lsn, write_lsn, flush_lsn
FROM pg_stat_replication;

歸檔延遲告警腳本

#!/bin/bash
# 監控 WAL 歸檔延遲
MAX_LAG=600  # 10 分鐘

LAG=$(psql -U postgres -t -c "
  SELECT COALESCE(
    EXTRACT(EPOCH FROM (NOW() - last_archived_time)),
    99999
  )::int FROM pg_stat_archiver;
")

if [ "$LAG" -gt "$MAX_LAG" ]; then
  echo "CRITICAL: WAL archive lag ${LAG}s > ${MAX_LAG}s" | \
    mail -s "PostgreSQL Archive Alert" dba@example.com
fi

常見陷阱

1. archive_command 失敗導致磁碟爆滿

archive_command 失敗時，PostgreSQL 不會回收 WAL segment 但會持續重試，直到 pg_wal/ 目錄磁碟空間耗盡：

# 錯誤：目標目錄可能不存在
archive_command = 'cp %p /archive/%f'

# 正確：防覆蓋 + 確認目標存在
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

2. pg_dump 遇到 DDL 鎖等待

# pg_dump 不鎖表，但若有並發 DDL 可能無限等待
# 設定 lock_timeout 防止卡住
PGOPTIONS="-c lock_timeout=30s" pg_dump -U postgres -Fc mydb > mydb.dump

3. 還原後 Sequences 不同步

-- 邏輯備份使用 --data-only 還原後，sequences 可能落後
-- 手動重設所有 sequences
SELECT setval(
  pg_get_serial_sequence('"' || table_name || '"', column_name),
  (SELECT MAX(id) FROM orders)
)
FROM information_schema.columns
WHERE column_default LIKE 'nextval%';

4. pg_basebackup 需要額外的 wal sender

# max_wal_senders 必須大於 現有 Standby 數 + 1
# 假設有 2 個 Standby，至少需要 3（2 standby + 1 basebackup）
max_wal_senders = 5

5. WAL 歸檔保留期不夠長

錯誤情境：
  全量備份執行於週日 → 週六需要 PITR
  但 WAL 歸檔只保留 3 天 → 週日~週三的 WAL 已刪除
  → 無法 PITR 回那段時間

正確做法：
  WAL 歸檔保留期 >= 最舊全量備份的年齡
  保留 2 份週備份 → WAL 至少保留 14 天

6. recovery.signal 的生命週期

# 完成還原後，若 recovery_target_action = 'pause'
# 必須執行 pg_wal_replay_resume() 後 PostgreSQL 才會移除 recovery.signal
# 若手動刪除 recovery.signal 再重啟，會強制離開恢復模式

7. 從 Standby 備份需注意 WAL 回收

# Standby 執行 pg_basebackup 時，Primary 可能回收所需 WAL
# 開啟 hot_standby_feedback 或使用 replication slot 防止
hot_standby_feedback = on
# 或
primary_slot_name = 'backup_slot'

版本演進

版本	備份相關新功能
PG 9.1	pg_basebackup 首次引入
PG 9.4	Replication Slot 正式引入（防止 WAL 被提前回收）
PG 10	pg_basebackup 支援 `-X stream`
PG 12	recovery.conf 廢棄，改為 postgresql.conf + recovery.signal
PG 13	pg_verifybackup 引入，backup_manifest 標準化
PG 15	archive_library 引入（取代 archive_command shell）；支援 zstd 壓縮
PG 16	pg_basebackup 支援從 Standby 備份
PG 17	內建 Incremental Backup（summarize_wal / pg_combinebackup）

總結

備份與還原是資料庫安全的最後防線。關鍵要點：

小型系統用 pg_dump 即可滿足基本需求
生產環境必須使用 pg_basebackup + WAL 歸檔實現 PITR
企業級建議採用 pgBackRest 的三層備份策略（全備 + 差異 + WAL）
PG17 的原生增量備份是重大里程碑，未來可逐步取代第三方工具的增量功能
定期進行還原演練，確保備份真正可用——未經驗證的備份不是備份

下一篇，我們將深入探討 複寫與高可用——掌握 Streaming Replication、Logical Replication 與 Patroni 自動 Failover，打造永不停機的 PostgreSQL 叢集。