PostgreSQL在CentOS上的备份与恢复策略

PostgreSQL 在 CentOS 上的备份与恢复策略

一、策略总览与选型

建议采用“逻辑备份 + 物理备份”的双轨策略：
- 逻辑备份用 pg_dump / pg_dumpall，适合跨版本迁移、单库/多库/指定对象恢复、开发测试环境快速拉起。
- 物理备份用 pg_basebackup + WAL 归档，适合生产环境的时间点恢复 PITR、快速全量回滚与最小停机维护。
关键实践：
- 明确 RPO/RTO 目标，按业务选择全量频率与保留周期（如近 7/30/90 天）。
- 备份文件做异地/多副本存储，并定期做恢复演练与完整性校验。
- 自动化执行与监控告警，确保失败可被及时感知与修复。

二、逻辑备份与恢复步骤

常用命令速览
- 单库备份与恢复（自定义格式，便于选择性恢复与压缩）：
  - 备份：pg_dump -h 127.0.0.1 -p 5432 -U postgres -F c -f /backups/mydb_$(date +%F).dump mydb
  - 恢复：先建库 createdb -O owner mydb，再 pg_restore -d mydb /backups/mydb_YYYY-MM-DD.dump
- 所有数据库与全局对象（角色/表空间等）：
  - 备份：pg_dumpall -U postgres > /backups/pg_all_$(date +%F).sql
  - 恢复：psql -f /backups/pg_all_YYYY-MM-DD.sql postgres
- 指定表/模式、仅结构/仅数据：
  - 指定表：pg_dump -t schema.table db > table.sql
  - 指定模式：pg_dump -n schema db > schema.sql
  - 仅结构：pg_dump -s db > schema.sql；仅数据：pg_dump -a db > data.sql
- 大型库优化：
  - 压缩：pg_dump db | gzip > db.sql.gz（恢复：gunzip -c db.sql.gz | psql db）
  - 并行与目录格式：pg_dump -j 4 -F d -f out.dir db（恢复：pg_restore -j 4 -d db out.dir）
- 原子性与一致性：
  - 使用 -1/--single-transaction 将转储作为单个事务执行，避免部分成功。

自动化与保留示例（以 postgres 用户）

备份脚本 /opt/scripts/pg_dump.sh：

#!/usr/bin/env bash
set -e
BACKUP_DIR=/backups
DATE=$(date +%F_%H-%M-%S)
mkdir -p "$BACKUP_DIR"
pg_dump -h 127.0.0.1 -p 5432 -U postgres -F c --file "$BACKUP_DIR/mydb_$DATE.dump" mydb
# 保留近30天
find "$BACKUP_DIR" -name "mydb_*.dump" -mtime +30 -delete

定时任务（每天 02:00）：0 2 * * * /opt/scripts/pg_dump.sh

恢复要点
- 目标库需预先创建且干净（避免对象已存在导致冲突）。
- 自定义/目录格式必须用 pg_restore；纯文本脚本用 psql。
- 远程恢复请保证网络、权限与目标库参数兼容。

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

启用 WAL 归档（编辑 postgresql.conf 并重启/重载）

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/pgsql/12/archive/%f && cp %p /var/lib/pgsql/12/archive/%f'
max_wal_senders = 3
wal_keep_segments = 32

归档目录需提前创建并赋权：mkdir -p /var/lib/pgsql/12/archive && chown postgres:postgres /var/lib/pgsql/12/archive

获取基础备份（在线热备）
```
pg_basebackup -h 127.0.0.1 -U postgres -D /backups/base_$(date +%F) \
  -F p -P -v -R -X stream
```
- -R 会在目标目录生成 standby.signal（PostgreSQL 12+ 使用 recovery.signal 机制），便于直接进入恢复模式。
恢复到指定时间点（PITR）
- 将基础备份目录拷贝为新的 $PGDATA，在 $PGDATA 下创建 recovery.signal（PostgreSQL 12+）：touch $PGDATA/recovery.signal
- 配置恢复目标（编辑 postgresql.conf 或 postgresql.auto.conf）：
```
restore_command = 'cp /var/lib/pgsql/12/archive/%f "%p"'
recovery_target_time = '2025-12-19 10:00:00'
# 或按事务ID：recovery_target_xid = '123456'
# 或按还原到最新：recovery_target_action = 'promote'
```
- 启动数据库，观察日志直至达到目标并自动切换为可读写：
```
pg_ctl -D $PGDATA start
tail -f $PGDATA/log/postgresql-*.log
```
版本差异提示
- PostgreSQL 12 之前：使用 recovery.conf，参数如 restore_command、recovery_target_time 写入该文件。
- PostgreSQL 12 及以后：使用 signal 文件 + postgresql.conf/postgresql.auto.conf 管理恢复。

四、备份与恢复验证及运维要点

定期恢复演练：按月/季度在隔离环境恢复关键库或全库，核对行数/校验和/业务关键报表。
完整性校验：
- 逻辑备份可用 pg_restore -l 预览内容、pg_restore --schema-only/-data-only 做结构/数据校验；
- 物理备份校验 WAL 连续性、时间线（timeline）与基础备份一致性。
安全与合规：
- 备份文件加密与访问控制（如传输到对象存储、落盘加密、最小权限共享）；
- 对敏感数据在导出前做脱敏或列级加密。
监控与告警：
- 记录 备份耗时、成功率、容量增长；对失败告警（如 Cron 失败、archive_command 返回非 0、可用空间不足）。
容量与性能：
- 合理设置 WAL 保留与归档并发，避免磁盘被 WAL 撑爆；
- 大型库优先使用并行转储/恢复与压缩降低 I/O 与网络压力。

最新问答

相关标签