PostgreSQL 文件系统级别备份完整指南

概述

文件系统级别备份是 PostgreSQL 数据库备份的另一种重要策略，通过直接复制数据库文件来实现数据保护。与逻辑备份（如 pg_dump）不同，文件系统备份直接操作底层存储文件，在特定场景下具有独特的优势。

INFO

文件系统备份适用于需要快速恢复大型数据库、灾难恢复场景，以及需要保持数据库物理结构完整性的情况。

核心概念与工作原理

基本原理

PostgreSQL 将所有数据存储在数据目录（通常为 /usr/local/pgsql/data 或 /var/lib/postgresql/data）中，包括：

数据文件：包含表、索引等数据的物理文件
WAL 日志：预写式日志，记录所有数据变更
配置文件：postgresql.conf、pg_hba.conf 等
元数据：系统表、事务状态等

业务场景分析

适用场景：

🏢 大型企业数据仓库：TB 级数据库的快速恢复
🚀 高可用系统：配合流复制的灾难恢复
📊 数据迁移项目：跨服务器的完整数据迁移
🔄 开发环境克隆：快速创建生产环境副本

不适用场景：

📱 在线服务的增量备份
🎯 单表或部分数据的选择性备份
🌐 跨版本的数据库迁移

文件系统备份方法详解

方法一：基本文件复制

实现步骤

bash

# 1. 停止 PostgreSQL 服务
sudo systemctl stop postgresql

# 2. 创建备份目录
sudo mkdir -p /backup/postgresql/$(date +%Y%m%d_%H%M%S)

# 3. 执行文件复制备份
sudo tar -czf /backup/postgresql/$(date +%Y%m%d_%H%M%S)/postgres_backup.tar.gz \
    -C /var/lib/postgresql/14/main .

# 4. 验证备份文件
ls -lh /backup/postgresql/$(date +%Y%m%d_%H%M%S)/

# 5. 重启服务
sudo systemctl start postgresql

完整示例：生产环境备份脚本

bash

#!/bin/bash
# 文件名：postgres_filesystem_backup.sh

# 配置变量
POSTGRES_VERSION="14"
DATA_DIR="/var/lib/postgresql/${POSTGRES_VERSION}/main"
BACKUP_BASE_DIR="/backup/postgresql"
RETENTION_DAYS=7

# 创建带时间戳的备份目录
BACKUP_DIR="${BACKUP_BASE_DIR}/$(date +%Y%m%d_%H%M%S)"
mkdir -p "$BACKUP_DIR"

# 日志记录函数
log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$BACKUP_DIR/backup.log"
}

# 开始备份流程
log_message "开始 PostgreSQL 文件系统备份"

# 检查磁盘空间（至少需要数据目录 2 倍大小）
REQUIRED_SPACE=$(du -s "$DATA_DIR" | awk '{print $1 * 2}')
AVAILABLE_SPACE=$(df "$BACKUP_BASE_DIR" | tail -1 | awk '{print $4}')

if [ "$AVAILABLE_SPACE" -lt "$REQUIRED_SPACE" ]; then
    log_message "错误：磁盘空间不足，需要 ${REQUIRED_SPACE}KB，可用 ${AVAILABLE_SPACE}KB"
    exit 1
fi

# 执行 CHECKPOINT 减少恢复时间
log_message "执行 CHECKPOINT"
sudo -u postgres psql -c "CHECKPOINT;" 2>&1 | tee -a "$BACKUP_DIR/backup.log"

# 停止 PostgreSQL 服务
log_message "停止 PostgreSQL 服务"
sudo systemctl stop postgresql

# 检查服务是否完全停止
sleep 5
if pgrep -f postgres > /dev/null; then
    log_message "警告：PostgreSQL 进程仍在运行，强制终止"
    sudo pkill -9 -f postgres
    sleep 3
fi

# 创建数据目录备份
log_message "开始复制数据文件"
tar -czf "$BACKUP_DIR/postgres_data.tar.gz" \
    -C "$(dirname "$DATA_DIR")" \
    "$(basename "$DATA_DIR")" 2>&1 | tee -a "$BACKUP_DIR/backup.log"

# 记录备份元信息
cat > "$BACKUP_DIR/backup_info.txt" << EOF
备份时间: $(date)
PostgreSQL 版本: $POSTGRES_VERSION
数据目录: $DATA_DIR
备份大小: $(du -h "$BACKUP_DIR/postgres_data.tar.gz" | cut -f1)
服务器: $(hostname)
操作系统: $(uname -a)
EOF

# 重启 PostgreSQL 服务
log_message "重启 PostgreSQL 服务"
sudo systemctl start postgresql

# 验证服务状态
sleep 5
if sudo systemctl is-active --quiet postgresql; then
    log_message "PostgreSQL 服务启动成功"
else
    log_message "错误：PostgreSQL 服务启动失败"
    exit 1
fi

# 清理旧备份
log_message "清理 ${RETENTION_DAYS} 天前的备份"
find "$BACKUP_BASE_DIR" -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \; 2>/dev/null

log_message "备份完成，备份位置：$BACKUP_DIR"

# 发送备份完成通知（可选）
# echo "PostgreSQL 备份完成：$BACKUP_DIR" | mail -s "备份通知" [email protected]

使用示例

bash

# 给脚本添加执行权限
chmod +x postgres_filesystem_backup.sh

# 执行备份
./postgres_filesystem_backup.sh

# 输出示例：
# [2024-06-04 10:30:00] 开始 PostgreSQL 文件系统备份
# [2024-06-04 10:30:01] 执行 CHECKPOINT
# [2024-06-04 10:30:05] 停止 PostgreSQL 服务
# [2024-06-04 10:30:10] 开始复制数据文件
# [2024-06-04 10:32:45] 重启 PostgreSQL 服务
# [2024-06-04 10:32:50] PostgreSQL 服务启动成功
# [2024-06-04 10:32:51] 清理 7 天前的备份
# [2024-06-04 10:32:52] 备份完成，备份位置：/backup/postgresql/20240604_103000

方法二：一致性快照备份

LVM 快照示例

适用于使用 LVM（逻辑卷管理）的系统，可以在不停止数据库的情况下创建一致性快照。

bash

#!/bin/bash
# LVM 快照备份脚本

VG_NAME="vg_data"              # 卷组名
LV_NAME="lv_postgres"          # 逻辑卷名
SNAPSHOT_NAME="postgres_snap"   # 快照名
SNAPSHOT_SIZE="10G"            # 快照大小
BACKUP_DIR="/backup/snapshot/$(date +%Y%m%d_%H%M%S)"

# 创建备份目录
mkdir -p "$BACKUP_DIR"

log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}

log_message "开始 LVM 快照备份"

# 执行 CHECKPOINT 确保数据一致性
log_message "执行 CHECKPOINT"
sudo -u postgres psql -c "CHECKPOINT;"

# 创建 LVM 快照
log_message "创建 LVM 快照"
sudo lvcreate -L"$SNAPSHOT_SIZE" -s -n "$SNAPSHOT_NAME" "/dev/$VG_NAME/$LV_NAME"

if [ $? -ne 0 ]; then
    log_message "错误：LVM 快照创建失败"
    exit 1
fi

# 挂载快照
MOUNT_POINT="/mnt/postgres_snapshot"
sudo mkdir -p "$MOUNT_POINT"
sudo mount "/dev/$VG_NAME/$SNAPSHOT_NAME" "$MOUNT_POINT"

# 从快照复制数据
log_message "从快照复制数据"
sudo tar -czf "$BACKUP_DIR/postgres_snapshot.tar.gz" -C "$MOUNT_POINT" .

# 清理快照
log_message "清理快照资源"
sudo umount "$MOUNT_POINT"
sudo lvremove -f "/dev/$VG_NAME/$SNAPSHOT_NAME"
sudo rmdir "$MOUNT_POINT"

log_message "快照备份完成：$BACKUP_DIR"

ZFS 快照示例

bash

#!/bin/bash
# ZFS 快照备份脚本

DATASET="tank/postgres"        # ZFS 数据集
SNAPSHOT_NAME="backup_$(date +%Y%m%d_%H%M%S)"
BACKUP_DIR="/backup/zfs/$(date +%Y%m%d_%H%M%S)"

mkdir -p "$BACKUP_DIR"

log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}

log_message "开始 ZFS 快照备份"

# 执行 CHECKPOINT
sudo -u postgres psql -c "CHECKPOINT;"

# 创建 ZFS 快照
log_message "创建 ZFS 快照"
sudo zfs snapshot "${DATASET}@${SNAPSHOT_NAME}"

# 发送快照到备份位置
log_message "发送快照数据"
sudo zfs send "${DATASET}@${SNAPSHOT_NAME}" | gzip > "$BACKUP_DIR/postgres_zfs.gz"

# 验证快照
sudo zfs list -t snapshot | grep "$SNAPSHOT_NAME"

log_message "ZFS 快照备份完成：$BACKUP_DIR"

# 可选：清理快照（根据保留策略）
# sudo zfs destroy "${DATASET}@${SNAPSHOT_NAME}"

方法三：rsync 增量备份

适用于最小化停机时间的场景，通过两阶段 rsync 实现快速备份。

bash

#!/bin/bash
# rsync 两阶段备份脚本

SOURCE_DIR="/var/lib/postgresql/14/main"
BACKUP_DIR="/backup/rsync/$(date +%Y%m%d_%H%M%S)"
TEMP_BACKUP_DIR="/backup/rsync/temp"

mkdir -p "$BACKUP_DIR"
mkdir -p "$TEMP_BACKUP_DIR"

log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$BACKUP_DIR/rsync_backup.log"
}

log_message "开始 rsync 两阶段备份"

# 第一阶段：在线备份（数据库运行中）
log_message "第一阶段：在线数据同步"
rsync -av --delete "$SOURCE_DIR/" "$TEMP_BACKUP_DIR/" 2>&1 | tee -a "$BACKUP_DIR/rsync_backup.log"

ONLINE_DURATION=$SECONDS
log_message "在线同步完成，耗时：${ONLINE_DURATION}秒"

# 执行 CHECKPOINT 为停机做准备
log_message "执行 CHECKPOINT"
sudo -u postgres psql -c "CHECKPOINT;"

# 停止 PostgreSQL 服务
log_message "停止 PostgreSQL 服务"
STOP_TIME=$(date +%s)
sudo systemctl stop postgresql

# 第二阶段：校验和同步（数据库停止）
log_message "第二阶段：校验和最终同步"
rsync -av --checksum --delete "$SOURCE_DIR/" "$TEMP_BACKUP_DIR/" 2>&1 | tee -a "$BACKUP_DIR/rsync_backup.log"

# 移动到最终备份位置
mv "$TEMP_BACKUP_DIR" "$BACKUP_DIR/data"

# 重启 PostgreSQL 服务
START_TIME=$(date +%s)
sudo systemctl start postgresql

DOWNTIME=$((START_TIME - STOP_TIME))
log_message "PostgreSQL 服务重启完成，停机时间：${DOWNTIME}秒"

# 创建备份信息文件
cat > "$BACKUP_DIR/backup_info.json" << EOF
{
    "backup_method": "rsync_two_stage",
    "backup_start": "$(date -d @$STOP_TIME)",
    "backup_end": "$(date -d @$START_TIME)",
    "downtime_seconds": $DOWNTIME,
    "online_sync_seconds": $ONLINE_DURATION,
    "source_dir": "$SOURCE_DIR",
    "backup_size": "$(du -sh "$BACKUP_DIR/data" | cut -f1)",
    "server": "$(hostname)"
}
EOF

log_message "rsync 备份完成，停机时间仅：${DOWNTIME}秒"

关键限制与注意事项

限制一：必须停止服务器

在数据库运行时进行文件复制会导致数据不一致，因为：

缓冲区数据：内存中的脏页可能未写入磁盘
事务状态：正在进行的事务可能处于中间状态
文件锁定：某些文件可能被数据库进程锁定

业务影响分析

bash

# 评估停机时间的脚本
#!/bin/bash

DATA_SIZE=$(du -sb /var/lib/postgresql/14/main | cut -f1)
BACKUP_SPEED=100000000  # 100MB/s 估算速度

ESTIMATED_TIME=$((DATA_SIZE / BACKUP_SPEED))
ESTIMATED_MINUTES=$((ESTIMATED_TIME / 60))

echo "数据大小：$(numfmt --to=iec $DATA_SIZE)"
echo "预估备份时间：${ESTIMATED_MINUTES}分钟"
echo "业务影响评估："
echo "- 在线服务将中断 ${ESTIMATED_MINUTES} 分钟"
echo "- 建议在业务低峰期（如凌晨 2-4 点）执行"
echo "- 考虑使用快照备份减少停机时间"

限制二：不支持部分备份

PostgreSQL 的数据文件之间存在复杂的依赖关系，必须作为整体进行备份。

以下备份方式是**错误的**，会导致数据损坏：

仅备份特定数据库目录
仅备份表文件而忽略事务日志
分别备份数据文件和 WAL 日志

错误示例与后果

bash

# ❌ 错误：仅备份单个数据库
tar -czf wrong_backup.tar.gz /var/lib/postgresql/14/main/base/16384/

# ❌ 错误：仅备份数据文件
tar -czf wrong_backup.tar.gz /var/lib/postgresql/14/main/base/

# ✅ 正确：完整备份
tar -czf correct_backup.tar.gz /var/lib/postgresql/14/main/

错误备份的恢复尝试：

bash

# 尝试恢复错误备份
sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/14/main/*
sudo tar -xzf wrong_backup.tar.gz -C /var/lib/postgresql/14/main/
sudo systemctl start postgresql

# 结果：PostgreSQL 启动失败
# 错误日志：
# FATAL: could not access status of transaction 12345
# DETAIL: Could not read from file "pg_xact/0000" at offset 16384

多文件系统环境的挑战

问题场景

在企业环境中，PostgreSQL 组件经常分布在不同存储设备上：

解决方案：同步快照脚本

bash

#!/bin/bash
# 多文件系统同步快照备份

# 配置存储路径
DATA_PATH="/fast_ssd/postgresql/data"
WAL_PATH="/slow_hdd/postgresql/wal"
TABLESPACE_PATH="/nfs_mount/tablespace"

BACKUP_BASE="/backup/multi_fs/$(date +%Y%m%d_%H%M%S)"
mkdir -p "$BACKUP_BASE"

log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$BACKUP_BASE/sync_backup.log"
}

# 检查所有存储设备的快照能力
check_snapshot_capability() {
    log_message "检查存储设备快照能力"

    # 检查 LVM 卷
    if ! lvdisplay | grep -q "fast_ssd"; then
        log_message "警告：SSD 不支持 LVM 快照"
        return 1
    fi

    # 检查文件系统类型
    FS_TYPE=$(df -T "$DATA_PATH" | tail -1 | awk '{print $2}')
    log_message "数据目录文件系统：$FS_TYPE"

    if [[ "$FS_TYPE" != "ext4" && "$FS_TYPE" != "xfs" && "$FS_TYPE" != "zfs" ]]; then
        log_message "警告：文件系统 $FS_TYPE 可能不支持一致性快照"
    fi

    return 0
}

# 执行同步快照备份
perform_sync_snapshot() {
    log_message "开始同步快照备份"

    # 执行 CHECKPOINT 确保数据一致性
    sudo -u postgres psql -c "CHECKPOINT;"

    # 创建备份脚本的并行执行
    cat > "$BACKUP_BASE/snapshot_commands.sh" << 'EOF'
#!/bin/bash
# 并行执行快照命令

# SSD 快照
lvcreate -L5G -s -n data_snap /dev/vg_fast/lv_data &
PID1=$!

# HDD 快照
lvcreate -L2G -s -n wal_snap /dev/vg_slow/lv_wal &
PID2=$!

# NFS 备份（无法快照，直接复制）
rsync -av /nfs_mount/tablespace/ /backup/tablespace_temp/ &
PID3=$!

# 等待所有操作完成
wait $PID1 $PID2 $PID3

echo "所有快照创建完成：$(date)"
EOF

    chmod +x "$BACKUP_BASE/snapshot_commands.sh"

    # 停止 PostgreSQL（确保一致性）
    log_message "停止 PostgreSQL 服务"
    sudo systemctl stop postgresql

    # 并行执行快照
    log_message "并行创建快照"
    sudo "$BACKUP_BASE/snapshot_commands.sh" 2>&1 | tee -a "$BACKUP_BASE/sync_backup.log"

    # 重启 PostgreSQL
    log_message "重启 PostgreSQL 服务"
    sudo systemctl start postgresql

    # 从快照复制数据
    copy_from_snapshots
}

# 从快照复制数据
copy_from_snapshots() {
    log_message "从快照复制数据到备份位置"

    # 挂载快照并复制
    mkdir -p /mnt/{data_snap,wal_snap}

    sudo mount /dev/vg_fast/data_snap /mnt/data_snap
    sudo mount /dev/vg_slow/wal_snap /mnt/wal_snap

    # 并行复制
    tar -czf "$BACKUP_BASE/data.tar.gz" -C /mnt/data_snap . &
    tar -czf "$BACKUP_BASE/wal.tar.gz" -C /mnt/wal_snap . &
    tar -czf "$BACKUP_BASE/tablespace.tar.gz" -C /backup/tablespace_temp . &

    wait # 等待所有复制完成

    # 清理快照
    sudo umount /mnt/{data_snap,wal_snap}
    sudo lvremove -f /dev/vg_fast/data_snap
    sudo lvremove -f /dev/vg_slow/wal_snap
    rm -rf /backup/tablespace_temp

    log_message "快照备份完成"
}

# 主执行流程
if check_snapshot_capability; then
    perform_sync_snapshot
else
    log_message "回退到传统文件系统备份"
    # 执行传统备份逻辑
fi

恢复过程详解

基本恢复流程

完整恢复脚本

bash

#!/bin/bash
# PostgreSQL 文件系统备份恢复脚本

BACKUP_FILE="$1"
POSTGRES_VERSION="14"
DATA_DIR="/var/lib/postgresql/${POSTGRES_VERSION}/main"
POSTGRES_USER="postgres"

if [ -z "$BACKUP_FILE" ]; then
    echo "用法: $0 <备份文件路径>"
    echo "示例: $0 /backup/postgresql/20240604_103000/postgres_data.tar.gz"
    exit 1
fi

log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}

# 验证备份文件
verify_backup() {
    log_message "验证备份文件"

    if [ ! -f "$BACKUP_FILE" ]; then
        log_message "错误：备份文件不存在：$BACKUP_FILE"
        exit 1
    fi

    # 检查备份文件完整性
    if ! tar -tzf "$BACKUP_FILE" >/dev/null 2>&1; then
        log_message "错误：备份文件损坏或格式错误"
        exit 1
    fi

    # 验证备份内容
    log_message "备份文件内容预览："
    tar -tzf "$BACKUP_FILE" | head -10
}

# 准备恢复环境
prepare_recovery() {
    log_message "准备恢复环境"

    # 停止 PostgreSQL 服务
    if systemctl is-active --quiet postgresql; then
        log_message "停止 PostgreSQL 服务"
        sudo systemctl stop postgresql

        # 确保进程完全终止
        sleep 5
        if pgrep -f postgres; then
            log_message "强制终止残留进程"
            sudo pkill -9 -f postgres
        fi
    fi

    # 备份当前数据（如果存在）
    if [ -d "$DATA_DIR" ] && [ "$(ls -A $DATA_DIR 2>/dev/null)" ]; then
        CURRENT_BACKUP="/tmp/postgresql_current_$(date +%Y%m%d_%H%M%S)"
        log_message "备份当前数据到：$CURRENT_BACKUP"
        sudo mv "$DATA_DIR" "$CURRENT_BACKUP"
    fi

    # 创建新的数据目录
    sudo mkdir -p "$DATA_DIR"
}

# 执行恢复
perform_recovery() {
    log_message "开始恢复数据"

    # 解压备份到数据目录
    log_message "解压备份文件"
    sudo tar -xzf "$BACKUP_FILE" -C "$DATA_DIR" --strip-components=1

    if [ $? -ne 0 ]; then
        log_message "错误：解压备份文件失败"
        exit 1
    fi

    # 设置正确的文件权限
    log_message "设置文件权限"
    sudo chown -R "$POSTGRES_USER:$POSTGRES_USER" "$DATA_DIR"
    sudo chmod 700 "$DATA_DIR"
    sudo chmod 600 "$DATA_DIR"/*.conf 2>/dev/null || true

    # 验证关键文件存在
    verify_critical_files
}

# 验证关键文件
verify_critical_files() {
    log_message "验证关键文件"

    CRITICAL_FILES=(
        "postgresql.conf"
        "pg_hba.conf"
        "PG_VERSION"
        "base"
        "global"
        "pg_wal"
    )

    for file in "${CRITICAL_FILES[@]}"; do
        if [ ! -e "$DATA_DIR/$file" ]; then
            log_message "错误：缺少关键文件：$file"
            exit 1
        fi
    done

    log_message "关键文件验证通过"
}

# 启动并验证恢复
start_and_verify() {
    log_message "启动 PostgreSQL 服务"

    # 启动服务
    sudo systemctl start postgresql

    # 等待服务启动
    sleep 10

    # 检查服务状态
    if ! systemctl is-active --quiet postgresql; then
        log_message "错误：PostgreSQL 服务启动失败"
        log_message "查看错误日志："
        sudo journalctl -u postgresql --no-pager -n 20
        exit 1
    fi

    # 测试数据库连接
    log_message "测试数据库连接"
    if sudo -u postgres psql -c "SELECT version();" >/dev/null 2>&1; then
        log_message "数据库连接成功"
    else
        log_message "错误：无法连接到数据库"
        exit 1
    fi

    # 检查数据库状态
    check_database_status
}

# 检查数据库状态
check_database_status() {
    log_message "检查数据库状态"

    # 检查恢复过程
    RECOVERY_INFO=$(sudo -u postgres psql -t -c "
        SELECT
            CASE
                WHEN pg_is_in_recovery() THEN 'In Recovery'
                ELSE 'Normal Operation'
            END as status,
            pg_size_pretty(pg_database_size(current_database())) as db_size;
    " 2>/dev/null)

    if [ $? -eq 0 ]; then
        log_message "数据库状态：$RECOVERY_INFO"
    fi

    # 列出数据库
    log_message "可用数据库："
    sudo -u postgres psql -l 2>/dev/null | grep -E "^\s*\w+\s*\|"

    # 检查表空间
    log_message "表空间信息："
    sudo -u postgres psql -c "SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) FROM pg_tablespace;" 2>/dev/null
}

# 主执行流程
main() {
    log_message "开始 PostgreSQL 文件系统备份恢复"
    log_message "备份文件：$BACKUP_FILE"

    verify_backup
    prepare_recovery
    perform_recovery
    start_and_verify

    log_message "恢复完成！数据库已准备就绪"
    log_message "建议执行以下后续操作："
    echo "  1. 验证数据完整性"
    echo "  2. 更新统计信息：ANALYZE;"
    echo "  3. 重建索引（如需要）：REINDEX;"
    echo "  4. 检查应用程序连接"
}

# 执行恢复
main

恢复验证检查清单

bash

#!/bin/bash
# 恢复后验证脚本

log_message() {
    echo "[验证] $1"
}

# 1. 服务状态检查
log_message "检查 PostgreSQL 服务状态"
systemctl status postgresql --no-pager

# 2. 数据库连接测试
log_message "测试数据库连接"
sudo -u postgres psql -c "SELECT current_timestamp, version();"

# 3. 数据完整性检查
log_message "检查数据完整性"
sudo -u postgres psql -c "
    SELECT
        schemaname,
        tablename,
        pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
    FROM pg_tables
    WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
    ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
    LIMIT 10;
"

# 4. 事务日志检查
log_message "检查 WAL 恢复状态"
sudo -u postgres psql -c "
    SELECT
        pg_is_in_recovery() as in_recovery,
        pg_last_wal_receive_lsn() as last_wal_received,
        pg_last_wal_replay_lsn() as last_wal_replayed;
"

# 5. 性能基准测试
log_message "执行简单性能测试"
sudo -u postgres psql -c "
    SELECT
        count(*) as connection_test
    FROM pg_stat_activity;
"

log_message "验证完成"

性能优化与最佳实践

备份性能优化

1. 并行备份策略

bash

#!/bin/bash
# 并行备份脚本 - 适用于多核心服务器

CORES=$(nproc)
DATA_DIR="/var/lib/postgresql/14/main"
BACKUP_DIR="/backup/parallel/$(date +%Y%m%d_%H%M%S)"

mkdir -p "$BACKUP_DIR"

log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}

# 分析数据目录结构
analyze_data_structure() {
    log_message "分析数据目录结构"

    # 找出最大的数据库目录
    find "$DATA_DIR/base" -maxdepth 1 -type d -exec du -s {} \; | \
    sort -rn | head -5 > "$BACKUP_DIR/large_databases.txt"

    log_message "最大的数据库目录："
    cat "$BACKUP_DIR/large_databases.txt"
}

# 并行备份执行
parallel_backup() {
    log_message "开始并行备份（使用 $CORES 个核心）"

    # 停止数据库
    sudo systemctl stop postgresql

    # 并行压缩不同组件
    (
        log_message "备份数据文件"
        tar -czf "$BACKUP_DIR/base.tar.gz" -C "$DATA_DIR" base/
    ) &

    (
        log_message "备份 WAL 文件"
        tar -czf "$BACKUP_DIR/pg_wal.tar.gz" -C "$DATA_DIR" pg_wal/
    ) &

    (
        log_message "备份全局数据"
        tar -czf "$BACKUP_DIR/global.tar.gz" -C "$DATA_DIR" global/
    ) &

    (
        log_message "备份配置文件"
        tar -czf "$BACKUP_DIR/config.tar.gz" -C "$DATA_DIR" *.conf
    ) &

    # 等待所有备份完成
    wait

    # 重启数据库
    sudo systemctl start postgresql

    log_message "并行备份完成"
}

analyze_data_structure
parallel_backup

2. 压缩优化策略

bash

#!/bin/bash
# 不同压缩算法的性能对比

DATA_DIR="/var/lib/postgresql/14/main"
TEST_FILE="$DATA_DIR/base"
BACKUP_BASE="/tmp/compression_test"

mkdir -p "$BACKUP_BASE"

# 测试不同压缩方法
test_compression() {
    local method="$1"
    local extension="$2"
    local command="$3"

    echo "测试 $method 压缩..."

    start_time=$(date +%s)
    eval "$command '$BACKUP_BASE/test.$extension' '$TEST_FILE/'"
    end_time=$(date +%s)

    duration=$((end_time - start_time))
    size=$(du -h "$BACKUP_BASE/test.$extension" | cut -f1)

    echo "$method: 时间=${duration}s, 大小=$size"
}

# 压缩算法对比
echo "压缩性能测试："
test_compression "gzip" "tar.gz" "tar -czf"
test_compression "bzip2" "tar.bz2" "tar -cjf"
test_compression "xz" "tar.xz" "tar -cJf"
test_compression "lz4" "tar.lz4" "tar -c | lz4 >"
test_compression "zstd" "tar.zst" "tar -c | zstd >"

# 清理测试文件
rm -f "$BACKUP_BASE"/test.*

输出示例：

压缩性能测试：
gzip: 时间=45s, 大小=2.1G
bzip2: 时间=120s, 大小=1.8G
xz: 时间=180s, 大小=1.6G
lz4: 时间=15s, 大小=2.8G
zstd: 时间=25s, 大小=2.0G

推荐：
- 快速备份：使用 lz4（速度优先）
- 存储优化：使用 xz（压缩率优先）
- 平衡选择：使用 zstd（速度与压缩率平衡）

监控与报警

备份监控脚本

bash

#!/bin/bash
# 备份监控和报警脚本

BACKUP_DIR="/backup/postgresql"
ALERT_EMAIL="[email protected]"
MAX_BACKUP_AGE_HOURS=25  # 超过25小时认为备份失败

# 检查最新备份
check_latest_backup() {
    local latest_backup=$(find "$BACKUP_DIR" -name "*.tar.gz" -type f -printf '%T@ %p\n' | sort -n | tail -1 | cut -d' ' -f2-)

    if [ -z "$latest_backup" ]; then
        send_alert "错误：未找到任何备份文件"
        return 1
    fi

    local backup_time=$(stat -c %Y "$latest_backup")
    local current_time=$(date +%s)
    local age_hours=$(( (current_time - backup_time) / 3600 ))

    echo "最新备份：$latest_backup"
    echo "备份时间：$(date -d @$backup_time)"
    echo "已过时间：${age_hours}小时"

    if [ $age_hours -gt $MAX_BACKUP_AGE_HOURS ]; then
        send_alert "警告：备份已过期${age_hours}小时，超过阈值${MAX_BACKUP_AGE_HOURS}小时"
        return 1
    fi

    return 0
}

# 检查备份完整性
check_backup_integrity() {
    local latest_backup=$(find "$BACKUP_DIR" -name "*.tar.gz" -type f -printf '%T@ %p\n' | sort -n | tail -1 | cut -d' ' -f2-)

    echo "验证备份完整性：$latest_backup"

    if tar -tzf "$latest_backup" >/dev/null 2>&1; then
        echo "✓ 备份文件完整"
        return 0
    else
        send_alert "错误：备份文件损坏 - $latest_backup"
        return 1
    fi
}

# 检查磁盘空间
check_disk_space() {
    local usage=$(df "$BACKUP_DIR" | tail -1 | awk '{print $5}' | sed 's/%//')

    echo "备份磁盘使用率：${usage}%"

    if [ $usage -gt 90 ]; then
        send_alert "警告：备份磁盘空间不足，使用率${usage}%"
        return 1
    elif [ $usage -gt 80 ]; then
        echo "注意：磁盘使用率较高（${usage}%），建议清理旧备份"
    fi

    return 0
}

# 发送报警
send_alert() {
    local message="$1"
    local hostname=$(hostname)
    local timestamp=$(date)

    echo "🚨 报警：$message"

    # 发送邮件报警
    cat << EOF | mail -s "PostgreSQL备份报警 - $hostname" "$ALERT_EMAIL"
PostgreSQL 备份系统报警

服务器: $hostname
时间: $timestamp
报警信息: $message

请及时检查备份系统状态。

详细信息：
$(df -h "$BACKUP_DIR")
$(ls -la "$BACKUP_DIR" | tail -5)
EOF

    # 可选：发送到监控系统
    # curl -X POST "http://monitoring.company.com/alert" \
    #      -d "source=postgresql_backup&message=$message&host=$hostname"
}

# 生成备份报告
generate_backup_report() {
    local report_file="$BACKUP_DIR/backup_report_$(date +%Y%m%d).txt"

    cat > "$report_file" << EOF
PostgreSQL 备份状态报告
生成时间：$(date)
服务器：$(hostname)

备份目录统计：
$(du -sh "$BACKUP_DIR"/* 2>/dev/null | sort -h)

最近5次备份：
$(ls -lt "$BACKUP_DIR"/*.tar.gz 2>/dev/null | head -5)

磁盘使用情况：
$(df -h "$BACKUP_DIR")

备份配置：
- 备份目录：$BACKUP_DIR
- 最大备份age：${MAX_BACKUP_AGE_HOURS}小时
- 报警邮箱：$ALERT_EMAIL
EOF

    echo "备份报告已生成：$report_file"
}

# 主监控流程
main() {
    echo "=== PostgreSQL 备份监控检查 ==="
    echo "开始时间：$(date)"

    local exit_code=0

    check_latest_backup || exit_code=1
    check_backup_integrity || exit_code=1
    check_disk_space || exit_code=1

    generate_backup_report

    if [ $exit_code -eq 0 ]; then
        echo "✓ 所有检查通过"
    else
        echo "❌ 发现问题，请查看上述报警信息"
    fi

    echo "结束时间：$(date)"
    return $exit_code
}

# 执行监控
main

与 SQL 转储的对比分析

特性对比表

特性	文件系统备份	SQL 转储 (pg_dump)
备份速度	⚡ 快速（直接文件复制）	🐌 较慢（需要查询和格式化）
恢复速度	⚡ 快速（文件解压）	🐌 较慢（需要重新执行 SQL）
备份大小	📦 较大（包含索引）	💾 较小（仅数据和结构）
停机时间	⏸️ 需要停机	✅ 在线备份
部分备份	❌ 不支持	✅ 支持单表/单库
跨版本兼容	❌ 版本敏感	✅ 版本兼容性好
一致性保证	⚠️ 需要特殊处理	✅ 事务一致性

使用场景建议

混合备份策略

bash

#!/bin/bash
# 混合备份策略脚本

DB_SIZE_THRESHOLD=50  # GB
BACKUP_DIR="/backup/mixed"

# 获取数据库大小
get_database_size() {
    local size_bytes=$(sudo -u postgres psql -t -c "
        SELECT pg_database_size(current_database());
    " | tr -d ' ')

    local size_gb=$((size_bytes / 1024 / 1024 / 1024))
    echo $size_gb
}

# 选择备份策略
choose_backup_strategy() {
    local db_size=$(get_database_size)
    local current_hour=$(date +%H)

    echo "当前数据库大小：${db_size}GB"
    echo "当前时间：$(date)"

    if [ $db_size -lt $DB_SIZE_THRESHOLD ]; then
        echo "数据库较小，使用 SQL 转储备份"
        perform_sql_dump
    elif [ $current_hour -ge 2 ] && [ $current_hour -le 4 ]; then
        echo "在维护时间窗口，使用文件系统备份"
        perform_filesystem_backup
    else
        echo "数据库较大且不在维护窗口，使用在线 SQL 转储"
        perform_sql_dump
    fi
}

# SQL 转储备份
perform_sql_dump() {
    local backup_file="$BACKUP_DIR/sql_dump_$(date +%Y%m%d_%H%M%S).sql.gz"

    echo "开始 SQL 转储备份"
    sudo -u postgres pg_dumpall | gzip > "$backup_file"

    echo "SQL 转储完成：$backup_file"
}

# 文件系统备份
perform_filesystem_backup() {
    local backup_file="$BACKUP_DIR/filesystem_$(date +%Y%m%d_%H%M%S).tar.gz"

    echo "开始文件系统备份"
    sudo systemctl stop postgresql
    tar -czf "$backup_file" -C /var/lib/postgresql/14 main/
    sudo systemctl start postgresql

    echo "文件系统备份完成：$backup_file"
}

# 执行混合策略
mkdir -p "$BACKUP_DIR"
choose_backup_strategy

自动化与调度

Cron 任务配置

bash

# 编辑 crontab
sudo crontab -e

# 添加以下任务：

# 每日凌晨 2 点执行文件系统备份
0 2 * * * /opt/scripts/postgres_filesystem_backup.sh >> /var/log/postgres_backup.log 2>&1

# 每小时执行备份监控检查
0 * * * * /opt/scripts/backup_monitor.sh >> /var/log/backup_monitor.log 2>&1

# 每周日清理超过 30 天的备份
0 3 * * 0 find /backup/postgresql -type f -mtime +30 -delete

# 每月生成备份统计报告
0 1 1 * * /opt/scripts/backup_report.sh | mail -s "月度备份报告" [email protected]

Systemd 服务配置

创建系统服务以更好地管理备份任务：

bash

# 创建服务文件
sudo tee /etc/systemd/system/postgres-backup.service << 'EOF'
[Unit]
Description=PostgreSQL Filesystem Backup
After=postgresql.service
Requires=postgresql.service

[Service]
Type=oneshot
User=root
ExecStart=/opt/scripts/postgres_filesystem_backup.sh
StandardOutput=journal
StandardError=journal

[Install]
WantedBy=multi-user.target
EOF

# 创建定时器文件
sudo tee /etc/systemd/system/postgres-backup.timer << 'EOF'
[Unit]
Description=PostgreSQL Backup Timer
Requires=postgres-backup.service

[Timer]
OnCalendar=daily
Persistent=true
RandomizedDelaySec=300

[Install]
WantedBy=timers.target
EOF

# 启用并启动定时器
sudo systemctl daemon-reload
sudo systemctl enable postgres-backup.timer
sudo systemctl start postgres-backup.timer

# 查看定时器状态
sudo systemctl status postgres-backup.timer
sudo systemctl list-timers postgres-backup.timer

故障排除指南

常见问题与解决方案

1. 备份过程中服务无法停止

bash

# 问题诊断脚本
#!/bin/bash

check_postgres_processes() {
    echo "检查 PostgreSQL 相关进程："
    ps aux | grep postgres | grep -v grep

    echo -e "\n检查数据库连接："
    sudo -u postgres psql -c "
        SELECT pid, usename, application_name, client_addr, state
        FROM pg_stat_activity
        WHERE state = 'active';
    " 2>/dev/null || echo "无法连接到数据库"
}

force_stop_postgres() {
    echo "强制停止 PostgreSQL："

    # 1. 尝试优雅停止
    sudo systemctl stop postgresql
    sleep 5

    # 2. 检查是否还有进程
    if pgrep -f postgres; then
        echo "发现残留进程，发送 SIGTERM"
        sudo pkill -TERM -f postgres
        sleep 10
    fi

    # 3. 强制终止
    if pgrep -f postgres; then
        echo "强制终止所有 PostgreSQL 进程"
        sudo pkill -9 -f postgres
        sleep 3
    fi

    # 4. 清理共享内存
    echo "清理共享内存段"
    sudo ipcs -m | grep postgres | awk '{print $2}' | xargs -r sudo ipcrm -m

    echo "PostgreSQL 停止完成"
}

check_postgres_processes
force_stop_postgres

2. 恢复后数据库无法启动

bash

# 恢复故障诊断脚本
#!/bin/bash

diagnose_recovery_failure() {
    local data_dir="/var/lib/postgresql/14/main"

    echo "=== PostgreSQL 恢复故障诊断 ==="

    # 1. 检查文件权限
    echo "检查文件权限："
    ls -la "$data_dir" | head -10

    # 2. 检查关键文件
    echo -e "\n检查关键文件："
    for file in postgresql.conf pg_hba.conf PG_VERSION; do
        if [ -f "$data_dir/$file" ]; then
            echo "✓ $file 存在"
        else
            echo "❌ $file 缺失"
        fi
    done

    # 3. 检查数据目录结构
    echo -e "\n检查目录结构："
    find "$data_dir" -maxdepth 2 -type d | sort

    # 4. 检查日志文件
    echo -e "\n最近的错误日志："
    journalctl -u postgresql --no-pager -n 20

    # 5. 检查 PostgreSQL 版本兼容性
    if [ -f "$data_dir/PG_VERSION" ]; then
        local data_version=$(cat "$data_dir/PG_VERSION")
        local server_version=$(postgres --version | awk '{print $3}' | cut -d. -f1)

        echo -e "\n版本兼容性："
        echo "数据版本：$data_version"
        echo "服务器版本：$server_version"

        if [ "$data_version" != "$server_version" ]; then
            echo "❌ 版本不匹配！需要升级或降级"
        else
            echo "✓ 版本匹配"
        fi
    fi
}

fix_common_issues() {
    local data_dir="/var/lib/postgresql/14/main"

    echo -e "\n=== 尝试修复常见问题 ==="

    # 1. 修复文件权限
    echo "修复文件权限..."
    sudo chown -R postgres:postgres "$data_dir"
    sudo chmod 700 "$data_dir"

    # 2. 检查并修复配置文件
    if [ -f "$data_dir/postgresql.conf.backup" ]; then
        echo "发现配置备份，恢复配置文件..."
        sudo cp "$data_dir/postgresql.conf.backup" "$data_dir/postgresql.conf"
    fi

    # 3. 清理锁文件
    if [ -f "$data_dir/postmaster.pid" ]; then
        echo "清理锁文件..."
        sudo rm -f "$data_dir/postmaster.pid"
    fi

    # 4. 尝试单用户模式启动
    echo "尝试单用户模式启动..."
    sudo -u postgres postgres --single -D "$data_dir" template1 << 'EOF'
SELECT version();
\q
EOF

    if [ $? -eq 0 ]; then
        echo "✓ 单用户模式正常"
    else
        echo "❌ 单用户模式失败"
    fi
}

diagnose_recovery_failure
fix_common_issues

3. WAL 日志恢复问题

bash

# WAL 恢复诊断脚本
#!/bin/bash

check_wal_recovery() {
    local data_dir="/var/lib/postgresql/14/main"

    echo "=== WAL 恢复状态检查 ==="

    # 1. 检查 WAL 目录
    echo "WAL 目录内容："
    ls -la "$data_dir/pg_wal/" | head -10

    # 2. 检查控制文件
    if command -v pg_controldata >/dev/null; then
        echo -e "\n控制文件信息："
        sudo -u postgres pg_controldata "$data_dir" | grep -E "(state|Latest checkpoint|REDO)"
    fi

    # 3. 检查恢复配置
    if [ -f "$data_dir/recovery.conf" ]; then
        echo -e "\n恢复配置文件存在："
        cat "$data_dir/recovery.conf"
    elif [ -f "$data_dir/postgresql.auto.conf" ]; then
        echo -e "\n自动配置文件："
        grep -i recovery "$data_dir/postgresql.auto.conf"
    fi

    # 4. 尝试WAL重放
    echo -e "\n启动 PostgreSQL 并监控恢复过程..."
    sudo systemctl start postgresql &

    # 监控日志中的恢复信息
    timeout 30 sudo journalctl -u postgresql -f | while read line; do
        echo "$line" | grep -E "(recovery|redo|checkpoint|consistent)"
    done
}

check_wal_recovery

总结

文件系统级别备份是 PostgreSQL 备份策略中的重要组成部分，特别适用于大型数据库的快速备份与恢复场景。关键要点包括：

核心优势

高性能：直接文件操作，备份和恢复速度快
完整性：保留所有数据库对象和物理结构
适用性：适合大型数据库和灾难恢复场景

关键限制

停机要求：通常需要停止数据库服务
整体性：无法进行选择性备份
版本敏感：对 PostgreSQL 版本要求严格

最佳实践

合理选择：根据数据库大小和业务要求选择备份方式
监控自动化：建立完善的监控和报警机制
定期验证：定期测试备份文件的完整性和可恢复性
混合策略：结合 SQL 转储和文件系统备份的优势

PostgreSQL 文件系统级别备份完整指南 ​

概述 ​

核心概念与工作原理 ​

基本原理 ​

业务场景分析 ​

文件系统备份方法详解 ​

方法一：基本文件复制 ​

实现步骤 ​

完整示例：生产环境备份脚本 ​

使用示例 ​

方法二：一致性快照备份 ​

LVM 快照示例 ​

ZFS 快照示例 ​

方法三：rsync 增量备份 ​

关键限制与注意事项 ​

限制一：必须停止服务器 ​

业务影响分析 ​

限制二：不支持部分备份 ​

错误示例与后果 ​

多文件系统环境的挑战 ​

问题场景 ​

解决方案：同步快照脚本 ​

恢复过程详解 ​

基本恢复流程 ​

完整恢复脚本 ​

恢复验证检查清单 ​

性能优化与最佳实践 ​

备份性能优化 ​

1. 并行备份策略 ​

2. 压缩优化策略 ​

输出示例： ​

监控与报警 ​

备份监控脚本 ​

与 SQL 转储的对比分析 ​

特性对比表 ​

使用场景建议 ​

混合备份策略 ​

自动化与调度 ​

Cron 任务配置 ​

Systemd 服务配置 ​

故障排除指南 ​

常见问题与解决方案 ​

1. 备份过程中服务无法停止 ​

2. 恢复后数据库无法启动 ​

3. WAL 日志恢复问题 ​

总结 ​

核心优势 ​

关键限制 ​

最佳实践 ​

PostgreSQL 文件系统级别备份完整指南

概述

核心概念与工作原理

基本原理

业务场景分析

文件系统备份方法详解

方法一：基本文件复制

实现步骤

完整示例：生产环境备份脚本

使用示例

方法二：一致性快照备份

LVM 快照示例

ZFS 快照示例

方法三：rsync 增量备份

关键限制与注意事项

限制一：必须停止服务器

业务影响分析

限制二：不支持部分备份

错误示例与后果

多文件系统环境的挑战

问题场景

解决方案：同步快照脚本

恢复过程详解

基本恢复流程

完整恢复脚本

恢复验证检查清单

性能优化与最佳实践

备份性能优化

1. 并行备份策略

2. 压缩优化策略

输出示例：

监控与报警

备份监控脚本

与 SQL 转储的对比分析

特性对比表

使用场景建议

混合备份策略

自动化与调度

Cron 任务配置

Systemd 服务配置

故障排除指南

常见问题与解决方案

1. 备份过程中服务无法停止

2. 恢复后数据库无法启动

3. WAL 日志恢复问题

总结

核心优势

关键限制

最佳实践