PostgreSQL 備份與恢復策略

PostgreSQL 備份與恢復策略：確保資料安全的最後防線

在資料庫管理的世界裡，「備份」往往是被忽視的環節，直到災難發生時才顯得彌足珍貴。對於運行在 Ubuntu 22.04 或 Debian 12 上的 PostgreSQL 服務而言，建立一套可靠且可驗證的備份與恢復策略，是系統管理員的基本功。本文將介紹兩種最實用的備份方式：邏輯備份（pg_dump）與物理備份（pg_basebackup），並說明如何結合自動化腳本實現資料保護。

邏輯備份：靈活與便攜的 `pg_dump`

對於大多數中小型應用或需要跨版本遷移的場景，pg_dump 是首選工具。它會將資料庫的內容轉譯為標準 SQL 指令，產生一個純文字檔案。這種格式的優點在於便攜性極高，可以輕鬆匯入到不同版本的 PostgreSQL 中，甚至轉換到其他關係型資料庫。

首先，我們需要安裝 PostgreSQL 客戶端工具（如果尚未安裝）：

sudo apt update
sudo apt install postgresql-client-common postgresql-client

單一資料庫備份

若要備份單一資料庫（例如 myapp_db），請使用以下指令。請注意，PostgreSQL 的預設使用者是 postgres，建議使用 sudo -u postgres 切換身份執行，以避免權限問題。

sudo -u postgres pg_dump myapp_db > /var/backups/postgres/myapp_db_$(date +%F).sql

這裡使用了 $(date +%F) 來自動生成日期標記（格式為 YYYY-MM-DD），確保每個備份檔案都有唯一的檔名，避免覆蓋舊資料。

全資料庫集群備份

若需備份整個伺服器上的所有資料庫、角色及設定，應使用 pg_dumpall。這不僅包含資料，還包含全域物件（如使用者帳號）。

sudo -u postgres pg_dumpall > /var/backups/postgres/full_backup_$(date +%F).sql

物理備份：快速恢復的 `pg_basebackup`

當資料庫規模龐大，且對恢復時間目標（RTO）要求極高時，邏輯備份的恢復過程可能過於緩慢。此時，物理備份（Base Backup）是更好的選擇。它直接複製 PostgreSQL 的數據目錄檔案，恢復時只需將檔案放回原處即可，速度遠勝於逐行執行 SQL。

執行物理備份前，請確保目標目錄存在且權限正確：

sudo mkdir -p /var/backups/postgres/base
sudo chown postgres:postgres /var/backups/postgres/base

使用 pg_basebackup 指令進行備份：

sudo -u postgres pg_basebackup -D /var/backups/postgres/base/backup_$(date +%F) -Ft -z -P

參數說明：

-D：指定備份輸出目錄。
-Ft：將備份檔案打包為 tar 格式，便於傳輸與儲存。
-z：壓縮備份檔案，節省磁碟空間。
-P：顯示進度，讓你知道備份過程是否正常。

自動化備份腳本

手動執行備份容易遺漏，建議透過 cron 定時任務自動化此過程。建立一個簡單的腳本 /usr/local/bin/pg_backup.sh：

#!/bin/bash

BACKUP_DIR="/var/backups/postgres"
DATE=$(date +%F)
RETENTION_DAYS=7

# 建立目錄
mkdir -p $BACKUP_DIR/logic $BACKUP_DIR/base

# 備份單一資料庫
sudo -u postgres pg_dump myapp_db > $BACKUP_DIR/logic/myapp_db_$DATE.sql

# 清理超過 7 天的舊備份
find $BACKUP_DIR -type f -mtime +$RETENTION_DAYS -delete

echo "Backup completed at $(date)" >> $BACKUP_DIR/log/backup.log

賦予執行權限並加入 crontab：

sudo chmod +x /usr/local/bin/pg_backup.sh
sudo crontab -e
# 加入以下行，每天凌晨 2 點執行
0 2 * * * /usr/local/bin/pg_backup.sh

常見問題與解決方案

1. 恢復時出現權限錯誤： 在執行 pg_restore 或解壓物理備份後，務必修正檔案擁有者。PostgreSQL 通常以 postgres 使用者運行，若檔案歸 root 所有，服務將無法啟動。

sudo chown -R postgres:postgres /var/lib/postgresql/14/main

2. 備份過程中資料庫正在寫入，資料是否一致？ pg_dump 會建立一個一致性快照（Snapshot），確保備份出來的資料是某個時間點的一致狀態，不會出現部分寫入的髒數據。對於物理備份，pg_basebackup 同樣會處理 WAL 日誌的同步，確保備份點的完整性。

小結

建立 PostgreSQL 備份策略的核心在於「定期」與「驗證」。無論選擇邏輯備份的靈活性，還是物理備份的速度，關鍵在於定期測試恢復流程。建議每季至少執行一次恢復測試，確保備份檔案在緊急時刻真的能派上用場。資料無價，謹慎備份，方能高枕無憂。