Linux

Ubuntu 22.04 PostgreSQL 備份與還原

Ubuntu 22.04 PostgreSQL Backup and Restore

PostgreSQL 是企業級開源關聯式資料庫，備份與還原是資料庫管理中最重要的任務之一。本文將詳細介紹在 Ubuntu 22.04 上進行 PostgreSQL 備份與還原的各種方法。

備份策略概述

在規劃備份策略時，需要考慮以下幾個面向：

  • RPO（Recovery Point Objective）：可接受的資料遺失時間
  • RTO（Recovery Time Objective）：系統恢復所需的最長時間
  • 備份頻率：根據資料變更頻率決定
  • 保留策略：備份檔案保留的時間與數量
  • 儲存位置：本地、遠端或雲端儲存

常見的備份策略組合：

  • 每日完整備份 + 持續 WAL 歸檔
  • 每週完整備份 + 每日差異備份
  • 即時複寫 + 定期快照

pg_dump 單一資料庫備份

pg_dump 是最常用的邏輯備份工具，用於備份單一資料庫。

基本用法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# 備份資料庫為 SQL 格式
pg_dump -U postgres -d mydb > mydb_backup.sql

# 使用壓縮
pg_dump -U postgres -d mydb | gzip > mydb_backup.sql.gz

# 備份特定 schema
pg_dump -U postgres -d mydb -n public > mydb_public.sql

# 備份特定資料表
pg_dump -U postgres -d mydb -t users -t orders > mydb_tables.sql

# 僅備份結構（不含資料）
pg_dump -U postgres -d mydb --schema-only > mydb_schema.sql

# 僅備份資料（不含結構）
pg_dump -U postgres -d mydb --data-only > mydb_data.sql

排除特定資料表

1
2
3
4
5

# 排除單一資料表
pg_dump -U postgres -d mydb --exclude-table=logs > mydb_backup.sql

# 排除多個資料表（使用萬用字元）
pg_dump -U postgres -d mydb --exclude-table='temp_*' > mydb_backup.sql

pg_dumpall 全部資料庫備份

pg_dumpall 可以備份整個 PostgreSQL 叢集，包含所有資料庫、角色和表空間。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 備份所有資料庫
pg_dumpall -U postgres > all_databases.sql

# 僅備份全域物件（角色、表空間）
pg_dumpall -U postgres --globals-only > globals.sql

# 僅備份角色
pg_dumpall -U postgres --roles-only > roles.sql

# 使用壓縮
pg_dumpall -U postgres | gzip > all_databases.sql.gz

備份格式選項

PostgreSQL 提供四種備份格式：

Plain（純文字，預設）

1

pg_dump -U postgres -d mydb -F p -f mydb.sql
  • 輸出為 SQL 指令文字檔
  • 可直接閱讀和編輯
  • 使用 psql 還原

Custom（自訂格式）

1

pg_dump -U postgres -d mydb -F c -f mydb.dump
  • 壓縮的自訂格式
  • 支援選擇性還原
  • 支援平行還原
  • 使用 pg_restore 還原

Directory（目錄格式）

1

pg_dump -U postgres -d mydb -F d -f mydb_dir -j 4
  • 每個資料表一個檔案
  • 支援平行備份和還原
  • -j 指定並行作業數

Tar（tar 格式）

1

pg_dump -U postgres -d mydb -F t -f mydb.tar
  • 標準 tar 格式
  • 不支援平行操作
  • 使用 pg_restore 還原

還原資料庫

使用 psql 還原（Plain 格式）

1
2
3
4
5
6
7
8
9

# 還原到現有資料庫
psql -U postgres -d mydb < mydb_backup.sql

# 建立新資料庫並還原
createdb -U postgres newdb
psql -U postgres -d newdb < mydb_backup.sql

# 還原壓縮檔
gunzip -c mydb_backup.sql.gz | psql -U postgres -d mydb

使用 pg_restore 還原（Custom/Directory/Tar 格式）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

# 還原完整備份
pg_restore -U postgres -d mydb mydb.dump

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

# 僅還原結構
pg_restore -U postgres -d mydb --schema-only mydb.dump

# 僅還原資料
pg_restore -U postgres -d mydb --data-only mydb.dump

# 還原特定資料表
pg_restore -U postgres -d mydb -t users mydb.dump

# 平行還原（加快速度）
pg_restore -U postgres -d mydb -j 4 mydb.dump

# 清除現有物件後還原
pg_restore -U postgres -d mydb --clean mydb.dump

自動化備份腳本

以下是一個完整的自動化備份腳本：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

#!/bin/bash
# PostgreSQL 自動備份腳本

# 設定變數
BACKUP_DIR="/var/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DB_USER="postgres"
DB_NAME="mydb"
RETENTION_DAYS=7

# 確保備份目錄存在
mkdir -p "$BACKUP_DIR"

# 執行備份
echo "開始備份 $DB_NAME..."
pg_dump -U "$DB_USER" -d "$DB_NAME" -F c -f "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"

# 檢查備份結果
if [ $? -eq 0 ]; then
    echo "備份成功：${DB_NAME}_${TIMESTAMP}.dump"

    # 計算檔案大小
    SIZE=$(du -h "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump" | cut -f1)
    echo "備份檔案大小：$SIZE"
else
    echo "備份失敗！" >&2
    exit 1
fi

# 清理過期備份
echo "清理 $RETENTION_DAYS 天前的備份..."
find "$BACKUP_DIR" -name "${DB_NAME}_*.dump" -mtime +$RETENTION_DAYS -delete

echo "備份作業完成"

設定 Cron 排程

1
2
3
4
5

# 編輯 crontab
crontab -e

# 每天凌晨 2 點執行備份
0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

備份到遠端（S3）

將備份上傳到 AWS S3 提供異地備援：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

#!/bin/bash
# PostgreSQL 備份並上傳到 S3

BACKUP_DIR="/var/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"
S3_BUCKET="s3://my-backup-bucket/postgresql"

# 執行備份
pg_dump -U postgres -d "$DB_NAME" -F c | gzip > "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump.gz"

# 上傳到 S3
aws s3 cp "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump.gz" "$S3_BUCKET/"

# 設定 S3 生命週期原則來自動清理舊備份
# 可在 AWS Console 或使用 aws s3api 設定

# 驗證上傳
aws s3 ls "$S3_BUCKET/${DB_NAME}_${TIMESTAMP}.dump.gz"

# 清理本地檔案（可選）
rm "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump.gz"

時間點恢復（PITR）

PITR 允許將資料庫恢復到任意時間點，需要啟用 WAL 歸檔。

設定 WAL 歸檔

編輯 postgresql.conf：

1
2
3

wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

建立基礎備份

1
2

# 使用 pg_basebackup
pg_basebackup -U postgres -D /var/backups/postgresql/base -Ft -z -P

恢復到特定時間點

建立 recovery.signal 檔案並設定 postgresql.conf：

1
2
3

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2024-02-22 10:30:00'
recovery_target_action = 'promote'

啟動 PostgreSQL 後會自動進行恢復。

備份驗證

定期驗證備份的完整性非常重要：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

#!/bin/bash
# 備份驗證腳本

BACKUP_FILE=$1
TEST_DB="backup_test_$(date +%s)"

echo "建立測試資料庫..."
createdb -U postgres "$TEST_DB"

echo "還原備份..."
pg_restore -U postgres -d "$TEST_DB" "$BACKUP_FILE"

if [ $? -eq 0 ]; then
    echo "還原成功，執行基本檢查..."

    # 檢查資料表數量
    TABLE_COUNT=$(psql -U postgres -d "$TEST_DB" -t -c "SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public';")
    echo "資料表數量：$TABLE_COUNT"

    # 檢查總記錄數（範例）
    # RECORD_COUNT=$(psql -U postgres -d "$TEST_DB" -t -c "SELECT count(*) FROM users;")
    # echo "users 記錄數：$RECORD_COUNT"

    echo "備份驗證通過！"
else
    echo "備份驗證失敗！" >&2
fi

# 清理測試資料庫
dropdb -U postgres "$TEST_DB"

參考資料

comments powered by Disqus
© 2020 - 2026 Astroicers Blog
Built with Hugo
Theme Stack designed by Jimmy