Debian下PostgreSQL备份恢复策略

一、备份策略

1. 逻辑备份（适用于中小型数据库/跨平台迁移）

核心工具：pg_dump（导出SQL脚本）、pg_dumpall（导出所有数据库及角色）。
常用命令：

• 备份单个数据库为压缩的自定义格式（支持选择性恢复）：

  pg_dump -U postgres -h localhost -p 5432 -F c -b -v -f /path/to/backup/mydb.dump mydb
  

• 备份所有数据库（包括角色、表空间）：

  pg_dumpall -U postgres -f /path/to/backup/all_databases.sql
  

• 压缩备份文件（节省空间）：

  pg_dump -U postgres mydb | gzip > /path/to/backup/mydb.sql.gz
  

特点：逻辑备份为文本或自定义格式，可跨PostgreSQL版本、跨操作系统恢复；但恢复速度较慢，不适合大型数据库。

2. 物理备份（适用于大型数据库/快速恢复）

核心工具：pg_basebackup（复制整个数据库集群文件）。
常用命令：

• 创建全量物理备份（压缩传输）：

  pg_basebackup -U postgres -h localhost -p 5432 -D /path/to/backup/full -F t -z -P
  

特点：物理备份是数据库文件的完整副本，恢复速度快；需配合WAL归档实现时间点恢复（PITR），适合大型数据库或需要快速恢复的场景。

3. 增量备份（PostgreSQL 17+，减少存储与时间成本）

原理：基于全量备份生成增量备份，仅备份自上次备份以来的WAL日志变化。
前提条件：开启WAL摘要进程（summarize_wal = on）。
常用命令：

• 先创建全量备份：

  pg_basebackup -U postgres -D /path/to/backup/full -F t -z -P
  

• 再创建增量备份（基于全量备份的manifest文件）：

  pg_basebackup -D /path/to/backup/incr1 --incremental /path/to/backup/full/backup_manifest -c fast -p 5432 -U postgres
  

特点：显著减少备份时间和存储空间，适合频繁备份的大型数据库。

4. WAL归档（实现时间点恢复PITR的关键）

作用：记录所有数据变更，支持恢复到任意时间点（如误删数据、故障前状态）。
配置步骤：

• 修改postgresql.conf：

  wal_level = replica       # 提高WAL日志级别
  archive_mode = on         # 开启归档模式
  archive_command = 'cp %p /path/to/wal_archive/%f'  # 归档命令（将WAL文件复制到指定目录）
  

• 重启PostgreSQL使配置生效：

  sudo systemctl restart postgresql
  

注意：WAL归档需与物理备份结合使用，否则无法实现PITR。

5. 自动化与定时任务（避免人为遗漏）

工具：cron（Linux系统定时任务）。
配置示例：每天凌晨2点执行全量逻辑备份（压缩后保存到/backups目录）：

crontab -e

添加以下内容：

0 2 * * * /usr/bin/pg_dump -U postgres -h localhost -p 5432 -F c -b -v -f /backups/pg_backup_$(date +\%Y\%m\%d).dump mydb

注意：测试定时任务是否正常执行，定期清理过期备份（如保留最近7天的备份）。

二、恢复策略

1. 逻辑备份恢复（SQL/自定义格式）

适用场景：恢复单个数据库、跨版本迁移或逻辑备份文件损坏较轻的情况。
常用命令：

• 恢复SQL格式备份（*.sql）：

  psql -U postgres -d target_db -f /path/to/backup/mydb.sql
  

• 恢复自定义格式备份（*.dump/*.backup）：

  pg_restore -U postgres -d target_db -v /path/to/backup/mydb.dump
  

• 解压后恢复（若备份文件为.gz格式）：

  gunzip < /path/to/backup/mydb.sql.gz | psql -U postgres -d target_db
  

注意：恢复前需确保目标数据库存在（若不存在，先用createdb创建）。

2. 物理备份恢复（全量/增量）

适用场景：数据库崩溃、磁盘损坏或需要完整恢复到某个全量备份点。
步骤：

• 停止PostgreSQL服务：

  sudo systemctl stop postgresql
  

• 清空或备份当前数据目录（默认/var/lib/postgresql/<version>/main）：

  rm -rf /var/lib/postgresql/<version>/main/*
  

• 复制物理备份文件到数据目录：

  cp -r /path/to/backup/full/* /var/lib/postgresql/<version>/main/
  

• 启动PostgreSQL服务：

  sudo systemctl start postgresql
  

注意：若使用增量备份，需先恢复全量备份，再按顺序恢复增量备份（如incr1→incr2）。

3. 基于时间点的恢复（PITR，解决误操作/故障）

适用场景：恢复到某个特定时间点（如误删表、数据错误提交）。
前提条件：已开启WAL归档并配置archive_command。
步骤：

• 准备全量备份（如/path/to/backup/full）。
• 清空当前数据目录并复制全量备份：

  rm -rf /var/lib/postgresql/<version>/main/*
  cp -r /path/to/backup/full/* /var/lib/postgresql/<version>/main/
  

• 创建postgresql.auto.conf（用于覆盖默认配置）：

  echo "restore_command = 'cp /path/to/wal_archive/%f %p'" >> /var/lib/postgresql/<version>/main/postgresql.auto.conf
  echo "recovery_target_time = '2025-10-01 14:30:00'" >> /var/lib/postgresql/<version>/main/postgresql.auto.conf  # 设置恢复时间点
  echo "recovery_target_timeline = 'latest'" >> /var/lib/postgresql/<version>/main/postgresql.auto.conf
  

• 启动PostgreSQL（进入恢复模式）：

  sudo systemctl start postgresql
  

• 检查恢复状态（查看日志或执行SQL）：

  sudo -u postgres psql -c "SELECT pg_is_in_recovery();"  # 返回true表示正在恢复
  

• 恢复完成后，PostgreSQL会自动重启并进入正常模式。

4. 恢复验证（确保数据一致性）

关键步骤：

• 恢复后启动PostgreSQL服务：

  sudo systemctl start postgresql
  

• 登录数据库检查对象是否存在：

  sudo -u postgres psql -c "\l"  # 列出所有数据库
  sudo -u postgres psql -d target_db -c "\dt"  # 列出目标数据库的所有表
  

• 运行查询验证数据完整性（如检查关键表的记录数）：

  sudo -u postgres psql -d target_db -c "SELECT COUNT(*) FROM critical_table;"
  

注意：定期测试备份文件的恢复流程，确保在真实故障时能快速恢复。

三、注意事项

• 备份存储：将备份文件存放到异地（如云存储、外部硬盘），避免单点故障。
• 权限管理：备份目录需设置为postgres用户所有（chown -R postgres:postgres /path/to/backup），防止未授权访问。
• 版本兼容：逻辑备份需确保恢复环境的PostgreSQL版本不低于备份时的版本；物理备份需版本一致。
• 监控与报警：监控备份任务的执行状态（如cron日志），设置报警机制（如邮件通知），及时发现备份失败问题。