备份与恢复

数据备份是数据库管理中最重要的任务之一。PostgreSQL提供了多种备份和恢复工具，包括逻辑备份（pg_dump、pg_dumpall）和物理备份（pg_basebackup、WAL归档）。本章将详细介绍这些工具的使用方法。

逻辑备份

逻辑备份将数据库对象导出为SQL脚本或归档文件。

pg_dump

pg_dump用于备份单个数据库：

bash

# 基本用法：备份单个数据库
pg_dump -U postgres -d mydb > mydb_backup.sql

# 指定主机和端口
pg_dump -h localhost -p 5432 -U postgres -d mydb > mydb_backup.sql

# 使用自定义格式（推荐，可以选择性恢复）
pg_dump -U postgres -d mydb -F c -f mydb_backup.dump

# 使用目录格式（并行备份）
pg_dump -U postgres -d mydb -F d -f /backup/mydb_dir -j 4

# 使用tar格式
pg_dump -U postgres -d mydb -F t -f mydb_backup.tar

pg_dump常用选项

bash

# 只备份表结构（不包含数据）
pg_dump -U postgres -d mydb --schema-only > schema.sql

# 只备份数据（不包含结构）
pg_dump -U postgres -d mydb --data-only > data.sql

# 备份特定表
pg_dump -U postgres -d mydb -t employees > employees_backup.sql

# 备份多个表
pg_dump -U postgres -d mydb -t table1 -t table2 > tables_backup.sql

# 备份特定Schema
pg_dump -U postgres -d mydb -n sales_schema > sales_backup.sql

# 排除特定表
pg_dump -U postgres -d mydb -T temp_table > mydb_backup.sql

# 包含创建数据库语句
pg_dump -U postgres -d mydb --create > mydb_backup.sql

# 使用INSERT语句格式（而不是COPY）
pg_dump -U postgres -d mydb --column-inserts > mydb_backup.sql

# 压缩备份
pg_dump -U postgres -d mydb -F c -Z 9 -f mydb_backup.dump

# 指定编码
pg_dump -U postgres -d mydb --encoding=UTF8 > mydb_backup.sql

pg_dumpall

pg_dumpall用于备份整个PostgreSQL集群：

bash

# 备份所有数据库和全局对象（角色、表空间等）
pg_dumpall -U postgres > all_backup.sql

# 只备份全局对象（角色、表空间）
pg_dumpall -U postgres --globals-only > globals.sql

# 只备份角色
pg_dumpall -U postgres --roles-only > roles.sql

# 只备份表空间
pg_dumpall -U postgres --tablespaces-only > tablespaces.sql

# 指定输出文件
pg_dumpall -U postgres -f /backup/all_backup.sql

逻辑恢复

恢复SQL脚本

bash

# 使用psql恢复SQL脚本
psql -U postgres -d mydb < mydb_backup.sql

# 恢复到新数据库
createdb -U postgres mydb_new
psql -U postgres -d mydb_new < mydb_backup.sql

# 恢复全局对象
psql -U postgres < globals.sql

# 忽略错误继续执行
psql -U postgres -d mydb -v ON_ERROR_STOP=0 < mydb_backup.sql

pg_restore

pg_restore用于恢复pg_dump创建的自定义格式备份：

bash

# 恢复自定义格式备份
pg_restore -U postgres -d mydb mydb_backup.dump

# 恢复到新数据库（先创建数据库）
createdb -U postgres mydb_new
pg_restore -U postgres -d mydb_new mydb_backup.dump

# 恢复前创建数据库
pg_restore -U postgres -C -d postgres mydb_backup.dump

# 只恢复表结构
pg_restore -U postgres -d mydb --schema-only mydb_backup.dump

# 只恢复数据
pg_restore -U postgres -d mydb --data-only mydb_backup.dump

# 恢复特定表
pg_restore -U postgres -d mydb -t employees mydb_backup.dump

# 恢复特定Schema
pg_restore -U postgres -d mydb -n sales_schema mydb_backup.dump

# 并行恢复（加速恢复）
pg_restore -U postgres -d mydb -j 4 mydb_backup.dump

# 清理后恢复（先删除再创建）
pg_restore -U postgres -d mydb --clean mydb_backup.dump

# 生成SQL脚本而不执行
pg_restore mydb_backup.dump > restore.sql

# 详细输出
pg_restore -U postgres -d mydb -v mydb_backup.dump

物理备份

物理备份直接复制数据库文件，适合大型数据库的备份。

pg_basebackup

pg_basebackup用于创建数据库的物理备份：

bash

# 基本用法
pg_basebackup -U postgres -D /backup/base

# 指定主机和端口
pg_basebackup -h localhost -p 5432 -U postgres -D /backup/base

# 使用压缩
pg_basebackup -U postgres -D /backup/base -z

# 使用tar格式
pg_basebackup -U postgres -D /backup/base -Ft -z

# 包含WAL文件
pg_basebackup -U postgres -D /backup/base -X stream

# 检查点模式
pg_basebackup -U postgres -D /backup/base -c fast

# 进度显示
pg_basebackup -U postgres -D /backup/base -P

# 完整示例
pg_basebackup -h localhost -p 5432 -U postgres \
    -D /backup/base \
    -Ft -z \
    -X stream \
    -P \
    -R  # 创建recovery配置

WAL归档

WAL（Write-Ahead Logging）归档可以实现时间点恢复（PITR）：

sql

-- 在postgresql.conf中配置WAL归档
-- 启用WAL归档
archive_mode = on

-- 归档命令
archive_command = 'cp %p /archive/%f'

-- 或者使用更复杂的归档脚本
archive_command = 'rsync -a %p backup_server:/archive/%f'

-- 设置WAL级别
wal_level = replica  -- 或 logical

-- 检查WAL配置
SHOW archive_mode;
SHOW archive_command;
SHOW wal_level;

配置归档示例

bash

# 创建归档目录
mkdir -p /var/lib/postgresql/archive
chown postgres:postgres /var/lib/postgresql/archive

# 编辑postgresql.conf
cat >> /etc/postgresql/15/main/postgresql.conf << EOF
# WAL配置
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f'
max_wal_senders = 3
wal_keep_size = 1GB
EOF

# 重启PostgreSQL
systemctl restart postgresql

-- 检查归档状态
SELECT * FROM pg_stat_archiver;

时间点恢复（PITR）

PITR允许将数据库恢复到指定的时间点。

准备工作

bash

# 1. 确保WAL归档已启用
# 2. 创建基础备份
pg_basebackup -U postgres -D /backup/base_$(date +%Y%m%d) -Ft -z -X stream -P

# 3. 持续归档WAL文件
# archive_command会自动归档

恢复到指定时间点

bash

# 1. 停止PostgreSQL服务
systemctl stop postgresql

# 2. 清空数据目录
rm -rf /var/lib/postgresql/15/main/*

# 3. 恢复基础备份
tar -xzf /backup/base_20240115/base.tar.gz -C /var/lib/postgresql/15/main/

# 4. 创建recovery配置
cat > /var/lib/postgresql/15/main/postgresql.auto.conf << EOF
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2024-01-15 14:30:00'
recovery_target_action = 'promote'
EOF

# 5. 创建recovery信号文件
touch /var/lib/postgresql/15/main/recovery.signal

# 6. 启动PostgreSQL
systemctl start postgresql

# PostgreSQL会自动恢复到指定时间点

恢复选项

bash

# 恢复到指定时间点
recovery_target_time = '2024-01-15 14:30:00'

# 恢复到指定事务ID
recovery_target_xid = '1234567'

# 恢复到指定WAL位置
recovery_target_lsn = '0/5000000'

# 恢复到最新状态
recovery_target = 'immediate'

# 恢复后暂停
recovery_target_action = 'pause'

# 恢复后提升为主库
recovery_target_action = 'promote'

# 恢复后关闭
recovery_target_action = 'shutdown'

备份策略

完整备份策略

bash

#!/bin/bash
# 完整备份脚本 backup_full.sh

# 配置变量
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"
DB_USER="postgres"

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

# 执行备份
pg_dump -U $DB_USER -d $DB_NAME -F c -f $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# 压缩旧备份（超过7天）
find $BACKUP_DIR -name "*.dump" -mtime +7 -exec gzip {} \;

# 删除超过30天的备份
find $BACKUP_DIR -name "*.dump.gz" -mtime +30 -delete

echo "备份完成: ${DB_NAME}_${DATE}.dump"

增量备份策略

bash

#!/bin/bash
# 增量备份脚本 backup_incremental.sh

BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d)
BASE_BACKUP="/backup/base_$(date +%Y%m%d)"

# 每周执行一次基础备份
if [ $(date +%u) -eq 1 ]; then
    echo "执行基础备份..."
    pg_basebackup -U postgres -D $BASE_BACKUP -Ft -z -X stream -P
fi

# WAL归档已通过archive_command自动执行
echo "增量备份（WAL归档）已在进行中"

# 检查归档状态
psql -U postgres -c "SELECT * FROM pg_stat_archiver;"

自动化备份脚本

bash

#!/bin/bash
# 自动化备份脚本 auto_backup.sh

# 配置
BACKUP_DIR="/backup/postgresql"
LOG_FILE="/var/log/postgresql_backup.log"
RETENTION_DAYS=30

# 日志函数
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') $1" >> $LOG_FILE
}

# 创建备份目录
mkdir -p $BACKUP_DIR/{daily,weekly,monthly}

# 获取当前日期信息
DAY_OF_WEEK=$(date +%u)    # 1-7 (周一到周日)
DAY_OF_MONTH=$(date +%d)   # 01-31

log "开始备份..."

# 每日备份
pg_dumpall -U postgres | gzip > $BACKUP_DIR/daily/daily_$(date +%Y%m%d).sql.gz
log "每日备份完成"

# 每周备份（周日）
if [ $DAY_OF_WEEK -eq 7 ]; then
    cp $BACKUP_DIR/daily/daily_$(date +%Y%m%d).sql.gz \
       $BACKUP_DIR/weekly/weekly_$(date +%Y%m%d).sql.gz
    log "每周备份完成"
fi

# 每月备份（每月1日）
if [ $DAY_OF_MONTH -eq 01 ]; then
    cp $BACKUP_DIR/daily/daily_$(date +%Y%m%d).sql.gz \
       $BACKUP_DIR/monthly/monthly_$(date +%Y%m%d).sql.gz
    log "每月备份完成"
fi

# 清理过期备份
find $BACKUP_DIR/daily -name "*.sql.gz" -mtime +7 -delete
find $BACKUP_DIR/weekly -name "*.sql.gz" -mtime +30 -delete
find $BACKUP_DIR/monthly -name "*.sql.gz" -mtime +365 -delete
log "清理过期备份完成"

log "备份任务结束"

设置定时任务

bash

# 编辑crontab
crontab -e

# 添加定时任务
# 每天凌晨2点执行备份
0 2 * * * /path/to/auto_backup.sh

# 每小时检查WAL归档
0 * * * * /path/to/check_archive.sh

备份验证

验证备份完整性

bash

# 验证自定义格式备份
pg_restore --list mydb_backup.dump > /dev/null && echo "备份有效"

# 验证SQL备份
head -n 50 mydb_backup.sql
tail -n 50 mydb_backup.sql

# 检查备份文件大小
ls -lh mydb_backup.*

# 验证备份内容
pg_restore --list mydb_backup.dump | head -20

测试恢复

bash

# 创建测试数据库
createdb -U postgres test_restore

# 恢复到测试数据库
pg_restore -U postgres -d test_restore mydb_backup.dump

# 验证数据
psql -U postgres -d test_restore -c "SELECT COUNT(*) FROM employees;"

# 清理测试数据库
dropdb -U postgres test_restore

备份监控

监控脚本

bash

#!/bin/bash
# 备份监控脚本 check_backup.sh

BACKUP_DIR="/backup/postgresql"
ALERT_EMAIL="admin@example.com"

# 检查最新备份
LATEST_BACKUP=$(ls -t $BACKUP_DIR/daily/*.sql.gz | head -1)
BACKUP_AGE=$(( ($(date +%s) - $(stat -c %Y $LATEST_BACKUP)) / 3600 ))

if [ $BACKUP_AGE -gt 24 ]; then
    echo "警告：最新备份已超过24小时" | mail -s "备份警告" $ALERT_EMAIL
fi

# 检查备份大小
BACKUP_SIZE=$(stat -c %s $LATEST_BACKUP)
if [ $BACKUP_SIZE -lt 1000 ]; then
    echo "警告：备份文件异常小" | mail -s "备份警告" $ALERT_EMAIL
fi

# 检查WAL归档
ARCHIVE_COUNT=$(ls /var/lib/postgresql/archive/ | wc -l)
echo "当前归档WAL文件数: $ARCHIVE_COUNT"

PostgreSQL内部监控

sql

-- 查看归档状态
SELECT * FROM pg_stat_archiver;

-- 查看最近一次成功归档
SELECT 
    archived_count,
    last_archived_wal,
    last_archived_time,
    failed_count,
    last_failed_wal,
    last_failed_time
FROM pg_stat_archiver;

-- 查看复制状态（用于流复制备份）
SELECT * FROM pg_stat_replication;

-- 查看WAL发送状态
SELECT * FROM pg_stat_wal_sender;

-- 查看当前WAL位置
SELECT pg_current_wal_lsn();

-- 查看WAL文件列表
SELECT pg_walfile_name(pg_current_wal_lsn());

小结

本章介绍了PostgreSQL备份与恢复的主要内容：

工具	用途	特点
pg_dump	单库逻辑备份	灵活，可选择备份对象
pg_dumpall	全库逻辑备份	包含全局对象
pg_restore	恢复逻辑备份	支持并行、选择性恢复
pg_basebackup	物理备份	适合大型数据库
WAL归档	增量备份	支持时间点恢复

下一章我们将学习性能优化技巧。

备份与恢复 ​

逻辑备份 ​

pg_dump ​

pg_dump常用选项 ​

pg_dumpall ​

逻辑恢复 ​

恢复SQL脚本 ​

pg_restore ​

物理备份 ​

pg_basebackup ​

WAL归档 ​

配置归档示例 ​

时间点恢复（PITR） ​

准备工作 ​

恢复到指定时间点 ​

恢复选项 ​

备份策略 ​

完整备份策略 ​

增量备份策略 ​

自动化备份脚本 ​

设置定时任务 ​

备份验证 ​

验证备份完整性 ​

测试恢复 ​

备份监控 ​

监控脚本 ​

PostgreSQL内部监控 ​

小结 ​

备份与恢复

逻辑备份

pg_dump

pg_dump常用选项

pg_dumpall

逻辑恢复

恢复SQL脚本

pg_restore

物理备份

pg_basebackup

WAL归档

配置归档示例

时间点恢复（PITR）

准备工作

恢复到指定时间点

恢复选项

备份策略

完整备份策略

增量备份策略

自动化备份脚本

设置定时任务

备份验证

验证备份完整性

测试恢复

备份监控

监控脚本

PostgreSQL内部监控

小结