第八章：备份与恢复¶
数据备份是数据库管理的关键环节。本章将介绍 PostgreSQL 的多种备份策略和恢复方法。
8.1 备份类型概述¶
| 备份类型 | 特点 | 适用场景 |
|---|---|---|
| SQL 转储 | 逻辑备份，跨版本兼容 | 小型数据库、迁移 |
| 文件系统备份 | 物理备份，速度快 | 大型数据库 |
| WAL 归档 | 持续备份，时间点恢复 | 生产环境 |
| 逻辑复制 | 表级同步 | 数据分发 |
8.2 pg_dump 逻辑备份¶
基本用法¶
# 备份单个数据库
pg_dump -U postgres -h localhost mydb > backup.sql
# 指定格式
pg_dump -U postgres -F c mydb > backup.dump # 自定义格式（推荐）
pg_dump -U postgres -F d mydb -f backup_dir/ # 目录格式
pg_dump -U postgres -F t mydb > backup.tar # tar 格式
# 压缩备份
pg_dump -U postgres -F c -Z 9 mydb > backup.dump
# 仅备份结构
pg_dump -U postgres --schema-only mydb > schema.sql
# 仅备份数据
pg_dump -U postgres --data-only mydb > data.sql
# 备份特定表
pg_dump -U postgres -t users -t orders mydb > tables.sql
# 备份特定模式
pg_dump -U postgres -n sales mydb > sales_schema.sql
# 排除表
pg_dump -U postgres -T logs -T temp_* mydb > backup.sql
恢复数据¶
# 从 SQL 文件恢复
psql -U postgres -h localhost mydb < backup.sql
# 从自定义格式恢复
pg_restore -U postgres -h localhost -d mydb backup.dump
# 恢复前创建数据库
pg_restore -U postgres -C -d postgres backup.dump
# 恢复特定表
pg_restore -U postgres -d mydb -t users backup.dump
# 并行恢复（加速）
pg_restore -U postgres -d mydb -j 4 backup.dump
# 仅恢复结构
pg_restore -U postgres -d mydb --schema-only backup.dump
# 生成 SQL 而不执行
pg_restore -U postgres --schema-only backup.dump > schema.sql
pg_dumpall 备份所有数据库¶
# 备份所有数据库和全局对象（角色、表空间）
pg_dumpall -U postgres > all_databases.sql
# 仅备份全局对象
pg_dumpall -U postgres --globals-only > globals.sql
# 恢复
psql -U postgres -f all_databases.sql
8.3 文件系统备份¶
冷备份¶
# 停止 PostgreSQL
systemctl stop postgresql
# 复制数据目录
cp -r /var/lib/postgresql/14/main /backup/pg_cold_backup/
# 启动 PostgreSQL
systemctl start postgresql
热备份（需要归档）¶
-- 开始备份
SELECT pg_start_backup('backup_label');
-- 在操作系统层面复制数据目录
-- cp -r /var/lib/postgresql/14/main /backup/
-- 结束备份
SELECT pg_stop_backup();
8.4 WAL 归档与时间点恢复¶
配置 WAL 归档¶
-- postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'
-- 或使用更可靠的命令
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
-- Windows
archive_command = 'copy "%p" "C:\\archive\\%f"'
基础备份¶
# 使用 pg_basebackup
pg_basebackup -U postgres -h localhost -D /backup/base -Ft -z -P
# 参数说明
# -D 目标目录
# -Ft tar 格式
# -z 压缩
# -P 显示进度
# -X stream 同时备份 WAL
时间点恢复（PITR）¶
# 1. 停止数据库
systemctl stop postgresql
# 2. 清空数据目录
rm -rf /var/lib/postgresql/14/main/*
# 3. 恢复基础备份
tar -xf /backup/base/base.tar.gz -C /var/lib/postgresql/14/main/
# 4. 创建恢复配置
cat > /var/lib/postgresql/14/main/postgresql.auto.conf << EOF
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2024-03-15 10:30:00'
recovery_target_action = 'promote'
EOF
# 5. 创建恢复信号文件
touch /var/lib/postgresql/14/main/recovery.signal
# 6. 启动数据库
systemctl start postgresql
# 数据库将恢复到指定时间点
恢复目标选项¶
-- 恢复到指定时间
recovery_target_time = '2024-03-15 10:30:00'
-- 恢复到指定事务 ID
recovery_target_xid = '12345'
-- 恢复到指定 WAL 位置
recovery_target_lsn = '0/5000028'
-- 恢复到最早一致点
recovery_target = 'immediate'
-- 恢复后的动作
recovery_target_action = 'promote' -- 提升为主库
recovery_target_action = 'pause' -- 暂停等待
recovery_target_action = 'shutdown' -- 关闭
8.5 pgBackRest¶
pgBackRest 是企业级备份工具：
安装配置¶
# Ubuntu/Debian
apt-get install pgbackrest
# 创建配置
cat > /etc/pgbackrest/pgbackrest.conf << EOF
[global]
repo1-path=/backup/pgbackrest
repo1-retention-full=2
process-max=4
compress-type=lz4
[main]
pg1-path=/var/lib/postgresql/14/main
EOF
# 创建存储库
mkdir -p /backup/pgbackrest
chown postgres:postgres /backup/pgbackrest
备份操作¶
# 创建 stanza（备份配置）
pgbackrest --stanza=main stanza-create
# 全量备份
pgbackrest --stanza=main --type=full backup
# 增量备份
pgbackrest --stanza=main --type=incr backup
# 差异备份
pgbackrest --stanza=main --type=diff backup
# 查看备份
pgbackrest --stanza=main info
恢复操作¶
# 恢复到最新
pgbackrest --stanza=main --delta restore
# 恢复到指定时间
pgbackrest --stanza=main --delta --target="2024-03-15 10:30:00" restore
# 恢复到指定备份
pgbackrest --stanza=main --delta --set=20240315-100000F restore
8.6 自动化备份脚本¶
#!/bin/bash
# backup_postgres.sh
set -e
# 配置
DB_NAME="mydb"
BACKUP_DIR="/backup/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
# 创建备份目录
mkdir -p $BACKUP_DIR
# 备份
echo "开始备份 $DB_NAME..."
pg_dump -U postgres -F c -f "$BACKUP_DIR/${DB_NAME}_${DATE}.dump" $DB_NAME
# 检查备份
if [ -f "$BACKUP_DIR/${DB_NAME}_${DATE}.dump" ]; then
echo "备份成功: ${DB_NAME}_${DATE}.dump"
# 发送通知
# curl -X POST webhook_url -d "备份成功"
else
echo "备份失败!"
exit 1
fi
# 清理旧备份
find $BACKUP_DIR -name "*.dump" -mtime +$RETENTION_DAYS -delete
echo "清理完成"
# 验证备份
echo "验证备份..."
pg_restore -l "$BACKUP_DIR/${DB_NAME}_${DATE}.dump" > /dev/null
if [ $? -eq 0 ]; then
echo "备份验证通过"
fi
Cron 定时任务¶
# 编辑 crontab
crontab -e
# 每天凌晨 2 点备份
0 2 * * * /usr/local/bin/backup_postgres.sh >> /var/log/pg_backup.log 2>&1
# 每周日凌晨 3 点全量备份
0 3 * * 0 /usr/local/bin/backup_postgres.sh --full >> /var/log/pg_backup.log 2>&1
8.7 小结¶
本章学习了 PostgreSQL 的备份与恢复：
- 逻辑备份：pg_dump、pg_dumpall，适合小型数据库
- 物理备份：文件系统复制，速度快
- WAL 归档：持续备份，支持时间点恢复
- pgBackRest：企业级备份工具
- 自动化：脚本和定时任务
下一章将学习高可用与复制。