PostgreSQL在CentOS中的备份恢复实践

PostgreSQL 在 CentOS 的备份恢复实践

一 备份策略与准备

• 明确目标：区分逻辑备份（库/模式/表级，灵活迁移）与物理备份（数据目录/基础备份，适合快速整机恢复与时间点恢复 PITR）。
• 备份频率与保留：结合业务选择每日/每周/每月全量，保留多版本，并定期做恢复演练验证可用性。
• 存储与监控：备份落盘到安全位置（本地/NAS/对象存储），并做好完整性校验与状态监控/告警。
• 版本与路径：确认CentOS 7/8/9与PostgreSQL 12/14/15版本，备份/恢复工具路径与PGDATA一致。
• 权限与安全：使用具备足够权限的数据库账号，传输与存放过程加密，限制备份文件访问权限。

二 逻辑备份与恢复

• 常用工具与场景
  • pg_dump：导出单个数据库，可指定模式/表，支持纯文本与自定义归档格式。
  • pg_dumpall：导出整个集群（含全局对象，如角色/表空间）。
  • psql：导入纯文本 SQL 备份。
  • pg_restore：恢复**自定义归档格式（-F c/-F d）**的备份，支持并行与选择性对象恢复。
• 典型命令示例（以数据库testdb为例）
  • 纯文本备份与恢复
    • 备份：su - postgres -c "pg_dump testdb > /tmp/testdb_$(date +%F).sql"
    • 恢复：先建库 su - postgres -c "createdb testdb_restored"，再导入
      su - postgres -c "psql -d testdb_restored -f /tmp/testdb_2025-12-05.sql"
  • 自定义归档备份与恢复（适合大库与选择性恢复）
    • 备份：su - postgres -c "pg_dump -F c -f /opt/backup/testdb_$(date +%F).dump testdb"
    • 恢复：先建库，再恢复
      su - postgres -c "pg_restore -d testdb_restored /opt/backup/testdb_2025-12-05.dump"
  • 常用选项
    • 按模式/表：-n schema1、-t schema1.tbl；仅结构：-s；仅数据：-a；包含 DROP：-c；不写属主：-O；压缩传输：pg_dump mydb | gzip > mydb.sql.gz。
    • 恢复控制：-1/--single-transaction（一致性事务）、可按需选择对象恢复。

三 物理备份与时间点恢复 PITR

• 基础备份（pg_basebackup）
  • 执行：su - postgres -c "pg_basebackup -D /opt/pg_base/backup_$(date +%F) -F p -X stream -P -v"
  • 说明：-F p 为普通目录复制，-X stream 流式传输 WAL，-P/-v 显示进度与详细信息。
• 准备恢复目录与配置
  • 停止实例：systemctl stop postgresql-15（版本号按实际替换）。
  • 备份原数据目录：mv $PGDATA $PGDATA.bak_$(date +%F)。
  • 将备份目录移动到数据目录：mv /opt/pg_base/backup_YYYY-MM-DD $PGDATA。
  • 创建恢复信号文件：touch $PGDATA/recovery.signal（PostgreSQL 12+ 使用信号文件；早期版本用 recovery.conf）。
• 配置恢复目标（WAL 归档或回放至指定时间点）
  • 编辑 $PGDATA/postgresql.auto.conf（或 postgresql.conf）：
    • 使用 WAL 归档目录：restore_command = 'cp /var/lib/pgsql/15/archive/%f %p'（路径按实际归档目录调整）。
    • 指定时间点：recovery_target_time = '2025-12-05 10:00:00+08'（示例）。
    • 完成后是否停留：recovery_target_action = 'promote'（默认 shutdown）。
• 启动并完成恢复
  • 启动：systemctl start postgresql-15；检查日志：journalctl -u postgresql-15 -f。
  • 达到目标后，PostgreSQL 会自动移除 recovery.signal 并切换为读写模式。
• 归档清理（如启用 WAL 归档）
  • 使用 pg_archivecleanup 清理已不再需要的 WAL 段，避免磁盘占满。

四 自动化与常见注意事项

• 自动化备份脚本与定时任务
  • 示例脚本（保留30天）：

    #!/usr/bin/env bash
    set -e
    BACKUP_DIR="/opt/pgbackup"
    DATE=$(date +%F)
    mkdir -p "$BACKUP_DIR"
    su - postgres -c "pg_dump -F c -f $BACKUP_DIR/testdb_$DATE.dump testdb"
    find "$BACKUP_DIR" -name "testdb_*.dump" -mtime +30 -delete
    

  • 定时任务（每日 23:00）：0 23 * * * /opt/pgbackup/backup.sh。
• 常见问题与要点
  • 恢复前先创建目标库（纯文本/自定义归档均建议先建库）。
  • 大对象与性能：自定义归档配合 pg_restore -j <N> 并行恢复；纯文本导入可在单事务中执行（-1）以保证一致性。
  • 权限与属主：使用 -O 避免目标库用户/属主冲突；跨环境恢复需提前准备角色与表空间。
  • 版本兼容：尽量在相同或相邻小版本间恢复；跨大版本建议先逻辑导出/导入验证。
  • 监控与演练：定期检查备份生成与大小，并定期演练恢复流程确保可用性。