数据备份与恢复

数据备份与恢复是数据库管理中最重要的任务之一，确保在系统故障、数据损坏或人为错误时能够快速恢复数据。PostgreSQL提供了多种备份和恢复方法，包括逻辑备份、物理备份和连续归档备份。本章将详细介绍PostgreSQL的各种备份和恢复技术，帮助您建立完善的数据保护策略。

备份策略概述

PostgreSQL支持多种备份策略，每种策略都有其适用场景和优缺点。

备份类型分类

  • 逻辑备份：使用SQL命令导出数据，与平台无关
  • 物理备份：直接复制数据库文件，速度快但平台相关
  • 在线备份：数据库运行时进行备份
  • 离线备份：数据库停止时进行备份
  • 完整备份：备份整个数据库集群
  • 增量备份：只备份自上次备份以来更改的数据

备份策略选择考虑因素：

因素 考虑点
数据量 大数据量适合物理备份，小数据量适合逻辑备份
恢复时间要求 要求快速恢复时选择物理备份
平台兼容性 需要跨平台恢复时选择逻辑备份
备份频率 频繁备份时选择增量备份
存储空间 存储空间有限时选择压缩备份

逻辑备份与恢复

逻辑备份使用pg_dump和pg_dumpall工具，将数据库内容导出为SQL脚本或自定义格式文件。

使用pg_dump进行备份

# 备份单个数据库为SQL脚本
pg_dump -h localhost -U username -d database_name > backup.sql

# 备份单个数据库为自定义格式（推荐）
pg_dump -h localhost -U username -Fc -d database_name > backup.dump

# 备份单个表
pg_dump -h localhost -U username -t table_name -d database_name > table_backup.sql

# 备份多个表
pg_dump -h localhost -U username -t table1 -t table2 -d database_name > tables_backup.sql

# 备份特定模式
pg_dump -h localhost -U username -n schema_name -d database_name > schema_backup.sql

# 排除特定表
pg_dump -h localhost -U username -T table_to_exclude -d database_name > backup_without_table.sql

# 压缩备份
pg_dump -h localhost -U username -Fc -Z 9 -d database_name > compressed_backup.dump

# 并行备份（大数据量）
pg_dump -h localhost -U username -Fd -j 4 -d database_name -f backup_directory

使用pg_dumpall备份全局对象

# 备份所有数据库和全局对象
pg_dumpall -h localhost -U postgres > all_databases.sql

# 只备份全局对象（角色和表空间）
pg_dumpall -h localhost -U postgres -g > globals.sql

# 只备份角色
pg_dumpall -h localhost -U postgres -r > roles.sql

# 压缩备份所有数据库
pg_dumpall -h localhost -U postgres | gzip > all_databases.sql.gz

逻辑恢复操作

# 从SQL脚本恢复
psql -h localhost -U username -d database_name -f backup.sql

# 从自定义格式恢复
pg_restore -h localhost -U username -d database_name backup.dump

# 从并行备份恢复
pg_restore -h localhost -U username -d database_name -j 4 backup_directory

# 只恢复数据（不恢复模式）
pg_restore -h localhost -U username -d database_name --data-only backup.dump

# 只恢复模式（不恢复数据）
pg_restore -h localhost -U username -d database_name --schema-only backup.dump

# 恢复到不同数据库名
pg_restore -h localhost -U username -d new_database_name backup.dump

# 恢复特定表
pg_restore -h localhost -U username -d database_name -t table_name backup.dump

# 恢复特定模式
pg_restore -h localhost -U username -d database_name -n schema_name backup.dump

# 在恢复前清理目标数据库
pg_restore -h localhost -U username -d database_name --clean backup.dump

物理备份与恢复

物理备份通过直接复制数据库文件实现，速度更快但需要更多注意事项。

基础备份（文件系统级别）

# 1. 在psql中启用备份模式
SELECT pg_start_backup('base backup');

# 2. 在操作系统中复制数据目录
# 注意：需要停止数据库服务或使用文件系统快照
rsync -a /var/lib/postgresql/data/ /backup/postgresql/base_backup/

# 3. 在psql中结束备份模式
SELECT pg_stop_backup();

# 或者使用pg_basebackup工具（推荐）
pg_basebackup -h localhost -U replication_user -D /backup/postgresql/base_backup -P -v

# 压缩基础备份
pg_basebackup -h localhost -U replication_user -D /backup/postgresql/base_backup -P -v -Z 9

# 并行基础备份
pg_basebackup -h localhost -U replication_user -D /backup/postgresql/base_backup -P -v -j 4

配置连续归档

# 在postgresql.conf中配置WAL归档
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/postgresql/archive/%f'

# 或者使用更安全的归档命令
archive_command = 'test ! -f /backup/postgresql/archive/%f && cp %p /backup/postgresql/archive/%f'

# 重新加载配置
SELECT pg_reload_conf();

# 验证归档设置
SELECT name, setting FROM pg_settings WHERE name LIKE 'archive%';

# 手动切换WAL日志
SELECT pg_switch_wal();

时间点恢复（PITR）

# 1. 在postgresql.conf中配置恢复参数
# restore_command = 'cp /backup/postgresql/archive/%f %p'
# recovery_target_time = '2025-10-15 14:30:00'
# recovery_target_timeline = 'latest'

# 2. 创建recovery.conf文件（PostgreSQL 12之前）
# 在数据目录中创建recovery.conf
restore_command = 'cp /backup/postgresql/archive/%f %p'
recovery_target_time = '2025-10-15 14:30:00'
recovery_target_timeline = 'latest'

# 3. PostgreSQL 12及以后版本使用postgresql.auto.conf
# 在postgresql.auto.conf中添加恢复参数
# 启动时自动进入恢复模式

# 4. 启动PostgreSQL进行恢复
# systemctl start postgresql

# 5. 恢复完成后，创建recovery.signal文件表示恢复完成
# 恢复完成后，数据库会自动创建recovery.signal文件

备份自动化

通过脚本和计划任务实现备份自动化，确保备份的一致性和可靠性。

备份脚本示例

#!/bin/bash
# PostgreSQL备份脚本 backup.sh

# 配置变量
DB_HOST="localhost"
DB_USER="backup_user"
DB_NAME="myapp"
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.dump"
LOG_FILE="${BACKUP_DIR}/backup.log"

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

# 执行备份
echo "$(date): 开始备份数据库 $DB_NAME" >> $LOG_FILE
pg_dump -h $DB_HOST -U $DB_USER -Fc -d $DB_NAME > $BACKUP_FILE 2>> $LOG_FILE

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "$(date): 数据库 $DB_NAME 备份成功" >> $LOG_FILE
    # 压缩备份文件
    gzip $BACKUP_FILE
    echo "$(date): 备份文件已压缩" >> $LOG_FILE
    
    # 删除7天前的备份
    find $BACKUP_DIR -name "${DB_NAME}_*.dump.gz" -mtime +7 -delete
    echo "$(date): 已删除7天前的备份文件" >> $LOG_FILE
else
    echo "$(date): 数据库 $DB_NAME 备份失败" >> $LOG_FILE
    exit 1
fi

# 发送通知（可选）
# echo "PostgreSQL backup completed" | mail -s "Backup Success" admin@example.com

计划任务配置

# 使用cron配置定期备份
# 编辑crontab
crontab -e

# 每天凌晨2点执行完整备份
0 2 * * * /path/to/backup.sh

# 每小时执行WAL日志切换（配合连续归档）
0 * * * * psql -U postgres -c "SELECT pg_switch_wal();"

# 每周日凌晨3点执行完整备份
0 3 * * 0 /path/to/weekly_backup.sh

# 每月1号凌晨4点执行月度备份
0 4 1 * * /path/to/monthly_backup.sh

监控备份状态

-- 创建备份监控表
CREATE TABLE backup_monitor (
    id SERIAL PRIMARY KEY,
    backup_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    backup_type VARCHAR(50),
    database_name VARCHAR(100),
    backup_file VARCHAR(255),
    file_size BIGINT,
    status VARCHAR(20),
    duration INTERVAL
);

-- 插入备份记录的脚本函数
#!/bin/bash
# backup_with_monitoring.sh

DB_NAME="myapp"
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.dump"
START_TIME=$(date +%s)

# 执行备份
pg_dump -h localhost -U backup_user -Fc -d $DB_NAME > $BACKUP_FILE

# 计算备份时间
END_TIME=$(date +%s)
DURATION=$((END_TIME - START_TIME))

# 获取文件大小
FILE_SIZE=$(stat -c%s "$BACKUP_FILE")

# 记录备份信息
psql -U postgres -d postgres -c "
    INSERT INTO backup_monitor (backup_type, database_name, backup_file, file_size, status, duration)
    VALUES ('FULL', '$DB_NAME', '$BACKUP_FILE', $FILE_SIZE, 'SUCCESS', '$DURATION seconds');
"

# 检查备份完整性
pg_restore -l $BACKUP_FILE > /dev/null 2>&1
if [ $? -eq 0 ]; then
    echo "Backup integrity check passed"
else
    echo "Backup integrity check failed"
    psql -U postgres -d postgres -c "
        UPDATE backup_monitor 
        SET status = 'FAILED' 
        WHERE backup_file = '$BACKUP_FILE';
    "
fi

备份验证与测试

定期验证备份的完整性和可恢复性是确保备份有效性的关键。

备份完整性检查

# 检查备份文件是否存在
ls -la /backup/postgresql/*.dump

# 使用pg_restore检查备份文件结构
pg_restore -l /backup/postgresql/database_20251015_020000.dump

# 验证备份文件是否损坏
pg_restore -L /backup/postgresql/database_20251015_020000.dump /dev/null

# 检查备份中的表结构
pg_restore -f - --schema-only /backup/postgresql/database_20251015_020000.dump | head -20

# 验证特定表是否存在
pg_restore -l /backup/postgresql/database_20251015_020000.dump | grep table_name

恢复测试

# 1. 创建测试数据库
createdb -U postgres test_restore_db

# 2. 执行恢复测试
pg_restore -h localhost -U postgres -d test_restore_db /backup/postgresql/database_20251015_020000.dump

# 3. 验证恢复的数据
psql -U postgres -d test_restore_db -c "SELECT count(*) FROM users;"
psql -U postgres -d test_restore_db -c "SELECT count(*) FROM orders;"

# 4. 检查表结构
psql -U postgres -d test_restore_db -c "\dt"

# 5. 验证数据完整性
psql -U postgres -d test_restore_db -c "
    SELECT 
        (SELECT count(*) FROM users) as user_count,
        (SELECT count(*) FROM orders) as order_count;
"

# 6. 清理测试数据库
dropdb -U postgres test_restore_db

灾难恢复计划

制定完善的灾难恢复计划，确保在各种故障场景下都能快速恢复业务。

灾难恢复流程

# 1. 硬件故障恢复流程
# 步骤1: 确认故障类型和范围
# 步骤2: 启动备用服务器
# 步骤3: 从最近的备份恢复数据
# 步骤4: 应用WAL日志进行时间点恢复
# 步骤5: 验证数据完整性
# 步骤6: 切换应用连接到新服务器

# 2. 数据损坏恢复流程
# 步骤1: 停止数据库服务
# 步骤2: 确定数据损坏范围
# 步骤3: 从备份中恢复未损坏的数据
# 步骤4: 从WAL日志中恢复后续更改
# 步骤5: 验证恢复后的数据
# 步骤6: 重新启动数据库服务

# 3. 人为错误恢复流程
# 步骤1: 确定错误发生时间
# 步骤2: 准备恢复到错误发生前的时间点
# 步骤3: 执行时间点恢复
# 步骤4: 验证恢复的数据
# 步骤5: 重新开放数据库访问

高可用性配置

# 1. 流复制配置
# 主服务器postgresql.conf
wal_level = replica
max_wal_senders = 3
max_replication_slots = 3
archive_mode = on
archive_command = 'cp %p /backup/postgresql/archive/%f'

# 主服务器pg_hba.conf
host replication replicator 192.168.1.101/32 md5

# 备服务器recovery.conf (PostgreSQL 12之前)
standby_mode = on
primary_conninfo = 'host=192.168.1.100 port=5432 user=replicator password=password'
restore_command = 'cp /backup/postgresql/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'

# 2. 使用pg_ctl进行故障切换
# 在备服务器上执行
pg_ctl promote -D /var/lib/postgresql/data

# 3. 监控复制状态
SELECT 
    client_addr,
    state,
    sync_state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as lag
FROM pg_stat_replication;

数据备份与恢复最佳实践：

  1. 制定备份策略：根据业务需求制定完整的备份策略，包括备份频率、保留周期等
  2. 多重备份：采用多种备份方式，确保备份的可靠性
  3. 定期测试：定期测试备份的可恢复性，确保备份有效
  4. 异地存储：将备份文件存储在不同地理位置，防范自然灾害
  5. 加密备份：对敏感数据的备份进行加密保护
  6. 监控告警：建立备份监控机制，及时发现备份失败
  7. 文档化流程：详细记录备份和恢复流程，便于紧急情况下操作
  8. 定期演练：定期进行灾难恢复演练，提高应急响应能力