PostgreSQL pg_dump 資料庫備份與還原實戰教學

pg_dump 備份核心指令

pg_dump 是 PostgreSQL 官方提供的邏輯備份工具，可將資料庫結構與資料導出為 SQL 腳本或自定義格式。基本語法如下：

cd /usr/local/pgsql/bin
./pg_dump your_database_name > ./your_backup_file.dump

• your_database_name：欲備份的資料庫名稱
• your_backup_file.dump：輸出的備份檔案名稱，可包含路徑

備份檔案還原方法

方式一：使用 psql 還原（僅適用純 SQL 格式）

./psql your_database_name < ./your_backup_file.dump

此方式僅能還原基本設定，無法處理表結構與資料的完整還原。

方式二：使用 pg_restore 還原（推薦，支援自定義/目錄/壓縮格式）

./pg_restore -d your_database_name ./your_backup_file.dump

pg_restore 可完整還原表結構、資料、索引、約束等所有物件，並支援平行還原（-j 參數）大幅縮短時間。

進階備份技巧

指定輸出格式

# 目錄格式（支援平行備份/還原，彈性最高）
./pg_dump -Fd -f /backup/dir your_database_name

# 自定義格式（壓縮、支援 pg_restore 選擇性還原）
./pg_dump -Fc -f backup.custom your_database_name

# 純 SQL 格式（可直接用 psql 執行，但無壓縮、難以選擇性還原）
./pg_dump -Fp -f backup.sql your_database_name

常用篩選選項

• -t table_name：僅備份特定資料表
• -n schema_name：僅備份特定綱要
• --data-only：僅導出資料，不含結構
• --schema-only：僅導出結構，不含資料
• --exclude-table=pattern：排除特定表

壓縮與平行備份

# 使用 gzip 壓縮並啟用 4 條平行作業
./pg_dump -Fd -j 4 -Z 6 -f /backup/dir your_database_name

還原實戰範例

完整還原至新資料庫

createdb new_database
./pg_restore -d new_database -j 4 /backup/dir

選擇性還原特定表

./pg_restore -d target_db -t users -t orders backup.custom

僅還原結構不含資料

./pg_restore -d target_db --schema-only backup.custom

自動化備份腳本範例

#!/bin/bash
BACKUP_DIR="/backup/postgresql/$(date +%Y%m%d)"
mkdir -p "$BACKUP_DIR"
pg_dump -Fd -j 4 -Z 6 -f "$BACKUP_DIR" mydb
find /backup/postgresql -type d -mtime +7 -exec rm -rf {} \;

注意事項

• 備份時資料庫可正常讀寫，但長時間備份可能影響效能，建議在低峰期執行
• 定期測試還原流程，確保備份檔案有效性
• 生產環境建議結合 WAL 檔案備份（pg_basebackup 或 Barman）實現時間點恢復（PITR）
• pg_dump 版本需與目標資料庫版本相容，跨大版本升級時請參考官方文件