Linux中PostgreSQL数据库备份策略

Linux下PostgreSQL备份策略

一、策略总览与选型

• 备份方式对比与适用场景
  • 逻辑备份：使用pg_dump/pg_dumpall，输出为SQL脚本或自定义归档，支持按库/表/模式导出，便于跨版本迁移与选择性恢复，但恢复时需重建索引，RTO相对较长。
  • 物理备份：使用pg_basebackup获取数据目录一致性副本，结合WAL归档可实现时间点恢复（PITR），适合大库与严格RPO/RTO场景。
  • 第三方工具：如pgBackRest、Barman、pg_rman、Wal‑G，提供增量/差异备份、多存储库、远程/云归档与自动化保留等能力，适合规模化与自动化运维。
• 推荐组合策略
  • 生产库：以物理备份 + WAL归档为主，实现PITR；按容量与变更频率叠加差异/增量备份；保留近7–30天热备份与数月–数年归档以满足合规。
  • 开发/测试与迁移：以**pg_dump 自定义格式（-F c）**为主，便于快速恢复与跨版本升级；关键对象可单独导出。

二、关键配置与启用步骤

• 启用WAL归档（PITR前提）
  • postgresql.conf关键参数
    • wal_level = replica（或更高）
    • archive_mode = on
    • archive_command = ‘cp %p /path/to/archive/%f’（生产建议改为更可靠的脚本/对象存储命令）
    • 可选：archive_timeout = 300（秒，强制切换WAL，避免长事务导致归档延迟）
  • 使配置生效并验证
    • 执行：SELECT pg_reload_conf();
    • 手动切WAL并校验归档：SELECT pg_switch_wal(); 检查归档目录是否落盘。
• 基础物理备份（pg_basebackup）
  • 常用命令
    • pg_basebackup -h localhost -U postgres -D /backups/base_$(date +%F) -F t -z -P -X stream -c fast
    • 说明：-F t 为tar打包并压缩，-X stream 流式传输WAL，-c fast 立即做检查点以缩短恢复时间。
• 第三方工具初始化（以pgBackRest为例）
  • pgbackrest --stanza=mydb --log-level-console=info stanza-create
  • 配置archive_command使用pgBackRest仓库，开启连续归档与保留策略。

三、备份计划模板与保留建议

• 计划模板（可按RPO/RTO与数据量微调）
  • 物理+PITR（大中型库）
    • 全量：每周一次（周日）pg_basebackup
    • 增量/差异：每日一次（周一至周六，按负载选择差异或增量）
    • WAL：持续归档，保留≥RPO窗口；备份完成后按保留策略清理
  • 逻辑备份（开发/测试/小库/迁移）
    • 全库：每日一次 pg_dump -F c -b -v -f db_$(date +%F).backup dbname
    • 关键表/模式：按需每日或每两小时
    • 全局对象：每日一次 pg_dumpall -g（用户/角色/表空间等）
  • 保留与异地
    • 热备份保留：7–30天
    • 归档保留：≥RPO且覆盖最近一次全量之前的时间窗；合规可保留数月–数年
    • 至少一份异地/云存储副本（scp/rsync/s3同步）
• 自动化与清理示例（cron）
  • 逻辑备份（单库）
    • 0 2 * * * /usr/bin/pg_dump -U postgres -d mydb -F c -b -v -f “/backups/mydb-$(date +%Y%m%d).backup”
  • 保留近7天
    • 0 3 * * * find /backups -type f -mtime +7 -name “*.backup” -delete
  • 归档清理（示例保留30天）
    • 30 3 * * * find /path/to/archive -type f -mtime +30 -delete

四、恢复流程要点

• 物理+PITR（基于basebackup + WAL）
  • 准备：停止数据库，清空或移走$PGDATA；将base.tar.gz解压至$PGDATA；将备份中的pg_wal.tar.gz解压到临时目录（如**/backups/pg_wal**）。
  • 配置恢复：在$PGDATA创建recovery.signal；postgresql.conf中设置
    • restore_command = ‘cp /backups/pg_wal/%f %p’
    • 可选：recovery_target_time = ‘2025-12-28 10:00:00’（按需求设定目标）
  • 启动：systemctl start postgresql；恢复完成后数据库自动进入正常模式并删除recovery.signal。
• 逻辑备份恢复
  • 自定义归档：pg_restore -U postgres -d mydb -v db_20251228.backup
  • SQL脚本：psql -U postgres -f db_20251228.sql postgres
• 时间点恢复验证
  • 定期在预备环境演练恢复至关键时间点或最近一次备份前1分钟，核对数据一致性与应用可用性。

五、监控、校验与常见坑

• 备份有效性
  • 每次备份后执行校验：逻辑备份可做pg_restore --list或试恢复；物理/工具备份执行validate（如pgBackRest：pgbackrest --stanza=mydb validate）。
  • 定期恢复演练与校验和/哈希检查，避免“备份成功不可用”。
• 监控与告警
  • 监控项：备份退出码、耗时、备份大小异常、WAL归档滞后、保留策略执行结果、异地/云同步结果。
  • 失败告警与工单闭环，保留日志与审计证据。
• 常见坑与规避
  • 未开启或误配archive_command导致WAL断档，PITR失败；务必用脚本校验返回值并落盘成功再返回0。
  • 长事务/大表导出导致备份窗口过长与性能抖动；选择低峰期、分库分表导出、使用自定义格式并行恢复。
  • 误删/误改后仅靠“最近一次全量”无法回到故障前；务必保留足够WAL保留期并定期演练PITR。
  • 备份仅存本地同机房，灾难时一同丢失；至少一份异地/云副本，并验证可恢复性。