備份與還原
備份是資料安全的最後防線。本篇介紹 PostgreSQL 的備份方法與還原策略。
一、 備份類型
| 類型 | 工具 | 特點 | 適用 |
|---|---|---|---|
| 邏輯備份 | pg_dump | SQL 格式，跨版本相容 | 小型資料庫、遷移 |
| 物理備份 | pg_basebackup | 複製資料檔，速度快 | 大型資料庫、災難恢復 |
| 持續歸檔 | WAL Archiving | 支援 PITR | 需要時間點還原 |
二、 pg_dump 邏輯備份
基本用法
bash
# 備份單一資料庫
pg_dump -h localhost -U postgres -d mydb > backup.sql
# 備份特定表
pg_dump -h localhost -U postgres -d mydb -t users -t orders > tables.sql
# 排除特定表
pg_dump -h localhost -U postgres -d mydb --exclude-table=logs > backup.sql輸出格式
bash
# 純 SQL（可讀，可編輯）
pg_dump -Fp mydb > backup.sql
# 自訂格式（壓縮，支援並行還原）
pg_dump -Fc mydb > backup.dump
# 目錄格式（多檔案，支援並行）
pg_dump -Fd mydb -f backup_dir
# tar 格式
pg_dump -Ft mydb > backup.tar常用選項
| 選項 | 說明 |
|---|---|
-a / --data-only | 只備份資料，不含結構 |
-s / --schema-only | 只備份結構，不含資料 |
-n schema | 只備份特定 schema |
-N schema | 排除特定 schema |
-j N | 使用 N 個並行連線 |
--no-owner | 不包含擁有者資訊 |
--if-exists | DROP 語句加上 IF EXISTS |
三、 pg_restore 還原
bash
# 從自訂格式還原
pg_restore -h localhost -U postgres -d mydb backup.dump
# 並行還原（加速）
pg_restore -h localhost -U postgres -d mydb -j 4 backup.dump
# 只還原資料
pg_restore -h localhost -U postgres -d mydb -a backup.dump
# 還原特定表
pg_restore -h localhost -U postgres -d mydb -t users backup.dump
# 從純 SQL 還原
psql -h localhost -U postgres -d mydb < backup.sql還原到新資料庫
bash
# 建立新資料庫
createdb -h localhost -U postgres mydb_restored
# 還原
pg_restore -h localhost -U postgres -d mydb_restored backup.dump四、 pg_dumpall 全域備份
備份所有資料庫和全域物件（角色、表空間）：
bash
# 完整備份
pg_dumpall -h localhost -U postgres > full_backup.sql
# 只備份全域物件
pg_dumpall -h localhost -U postgres --globals-only > globals.sql
# 只備份角色
pg_dumpall -h localhost -U postgres --roles-only > roles.sql五、 pg_basebackup 物理備份
複製整個資料目錄，適合大型資料庫：
bash
# 基本備份
pg_basebackup -h localhost -U replicator -D /backup/base -Fp -Xs -P
# 壓縮備份
pg_basebackup -h localhost -U replicator -D /backup/base -Ft -z -Xs -P選項說明
| 選項 | 說明 |
|---|---|
-D | 輸出目錄 |
-Fp | 原始格式（plain） |
-Ft | tar 格式 |
-Xs | 串流 WAL |
-P | 顯示進度 |
-z | gzip 壓縮 |
-R | 自動生成 standby 設定 |
需要的權限
sql
-- 建立複寫用戶
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secret';# pg_hba.conf 允許複寫連線
host replication replicator 0.0.0.0/0 scram-sha-256六、 WAL 歸檔與 PITR
開啟 WAL 歸檔
conf
# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'建立基礎備份
bash
pg_basebackup -h localhost -U replicator -D /backup/base -Fp -Xs -P時間點還原 (PITR)
bash
# 1. 停止 PostgreSQL
systemctl stop postgresql
# 2. 保留舊資料目錄
mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main_old
# 3. 還原基礎備份
cp -r /backup/base /var/lib/postgresql/16/main
# 4. 建立還原設定
cat > /var/lib/postgresql/16/main/postgresql.auto.conf << EOF
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2024-01-15 14:30:00'
recovery_target_action = 'promote'
EOF
# 5. 建立還原信號檔
touch /var/lib/postgresql/16/main/recovery.signal
# 6. 啟動 PostgreSQL
systemctl start postgresql七、 備份策略設計
小型資料庫（< 10GB）
bash
# 每日完整備份
0 2 * * * pg_dump -Fc mydb > /backup/mydb_$(date +\%Y\%m\%d).dump
# 保留 7 天
0 3 * * * find /backup -name "*.dump" -mtime +7 -delete中型資料庫（10GB - 100GB）
bash
# 每日差異備份 + 週備份
# 週日完整備份
0 2 * * 0 pg_basebackup -D /backup/full_$(date +\%Y\%W) -Ft -z -Xs
# 每日 WAL 歸檔（自動）
archive_command = 'cp %p /backup/wal/%f'大型資料庫（> 100GB）
- 持續 WAL 歸檔
- 定期 pg_basebackup
- 使用 pgBackRest 或 Barman 管理
八、 pgBackRest
專業的備份管理工具：
ini
# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/backup/pgbackrest
repo1-retention-full=2
process-max=4
[mydb]
pg1-path=/var/lib/postgresql/16/mainbash
# 完整備份
pgbackrest --stanza=mydb backup --type=full
# 增量備份
pgbackrest --stanza=mydb backup --type=incr
# 還原
pgbackrest --stanza=mydb restore
# 時間點還原
pgbackrest --stanza=mydb restore --target-time="2024-01-15 14:30:00"九、 雲端備份
備份到 S3
bash
# 使用 aws cli
pg_dump -Fc mydb | aws s3 cp - s3://mybucket/backup/mydb_$(date +%Y%m%d).dump
# 使用 pgBackRest + S3
[global]
repo1-type=s3
repo1-s3-bucket=mybucket
repo1-s3-region=us-east-1十、 驗證備份
> **備份不驗證等於沒備份**
定期還原備份到測試環境驗證完整性。
bash
# 驗證備份檔案
pg_restore --list backup.dump > /dev/null && echo "Backup valid"
# 還原到測試資料庫
createdb test_restore
pg_restore -d test_restore backup.dump
psql -d test_restore -c "SELECT COUNT(*) FROM users;"
dropdb test_restore十一、 自動化腳本
bash
#!/bin/bash
# backup.sh
DB_NAME="mydb"
BACKUP_DIR="/backup"
RETENTION_DAYS=7
S3_BUCKET="s3://mybucket/postgres-backup"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.dump"
# 執行備份
pg_dump -Fc $DB_NAME > $BACKUP_FILE
# 上傳到 S3
aws s3 cp $BACKUP_FILE $S3_BUCKET/
# 清理舊備份
find $BACKUP_DIR -name "*.dump" -mtime +$RETENTION_DAYS -delete
# 通知
echo "Backup completed: $BACKUP_FILE"總結
| 方法 | 適用場景 | 還原速度 |
|---|---|---|
pg_dump | 小型資料庫、遷移 | 慢 |
pg_basebackup | 大型資料庫 | 快 |
WAL 歸檔 | 需要 PITR | 快 |
pgBackRest | 企業級備份管理 | 快 |
備份原則：
- 3-2-1 法則：3 份備份、2 種媒體、1 份異地
- 定期驗證還原
- 記錄備份日誌
進階挑戰
- 設計一個完整的備份策略，包括每日完整備份和每小時 WAL 歸檔
- 演練一次 PITR 還原，將資料庫還原到特定時間點
- 使用
pg_basebackup建立一個備用伺服器