Ubuntu下使用 pgAdmin 制定 PostgreSQL 备份策略
一 策略框架与取舍
- 明确目标：既要满足灾难恢复（RPO/RTO），也要兼顾迁移与审计。
- 备份类型：
- 逻辑备份：使用pg_dump，适合跨版本迁移、单库/单表恢复、开发/测试环境；pgAdmin 支持图形化备份与恢复。
- 物理备份：使用pg_basebackup，适合快速整机恢复与时间点恢复（PITR），需启用WAL 归档。
- 存储与保留：至少采用本地+异地/云双写；按业务重要性设置保留周期与多代保留（如7/30/90天）。
- 自动化与验证：用Cron定时执行；定期做恢复演练与完整性校验；对敏感备份进行加密与访问控制。
- 配置与元数据：除数据库外，定期备份**pgAdmin 配置目录（~/.pgadmin4/）**及服务器端关键配置（如角色、表空间、参数）。
二 备份类型与适用场景
| 类型 |
工具 |
主要优点 |
局限 |
典型场景 |
| 逻辑备份 |
pg_dump（pgAdmin 图形界面或命令行） |
跨版本迁移、选择性恢复、便于审计与版本控制 |
体积较大、恢复速度相对慢、停机时间更长 |
单库/单表恢复、开发测试、结构变更发布 |
| 物理备份 |
pg_basebackup + WAL 归档 |
快速全量恢复、支持PITR、停机时间短 |
依赖 WAL 归档与一致性，跨大版本迁移受限 |
生产库整机恢复、近实时 RPO/RTO 要求 |
| 说明：pgAdmin 提供“备份/恢复”对话框封装了 pg_dump/pg_restore 的常用能力；物理备份需配合 WAL 归档实现时间点恢复。 |
|
|
|
|
三 推荐备份策略模板
- 小型或一般业务（以逻辑备份为主）
- 全量：每日一次，使用Custom 格式（-F c），启用压缩；保留7–30天。
- 验证：每周抽样恢复一次到测试库；保留校验日志。
- 配置：备份**~/.pgadmin4/** 与服务器端角色/表空间定义。
- 中大型或高可用业务（逻辑+物理结合）
- 物理全量：每周一次 pg_basebackup，保留2–4周；
- WAL 归档：持续归档到安全存储，保留≥全量周期；
- 逻辑备份：每日一次关键库/关键表的 Custom 备份，保留7–30天，用于快速迁移与细粒度恢复；
- 演练：每月执行一次PITR 演练与全量恢复演练。
- 开发与发布流程
- 使用 pgAdmin 的仅架构备份生成 DDL 脚本，纳入Git版本控制；
- 数据变更以迁移脚本管理，与生产备份分离，保证可追溯与可回滚。
四 实施步骤与自动化
- 使用 pgAdmin 执行备份
- 连接服务器 → 左侧选择数据库 → 右键备份… → 配置：
- 文件名与路径：如 /backups/mydb_20250928.dump；
- 格式：优先Custom（支持压缩与部分恢复），也可选Tar/Plain；
- 选项：按需启用压缩、Blobs、以及No-Owner/No-Privileges/Disable-Triggers等；
- 恢复：在目标库上右键恢复，选择备份文件执行。
- 自动化脚本与 Cron
- 示例（Custom 格式，含日志与保留）：
- 备份脚本 /usr/local/bin/pg_backup.sh
- #!/bin/bash
set -e
source /data/backup_config.env
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE=“${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.dump”
mkdir -p “$BACKUP_DIR”
pg_dump --host=“$DB_HOST” --port=“$DB_PORT” --username=“$DB_USER”
–format=c --blobs --verbose --file=“$BACKUP_FILE” “$DB_NAME”
echo “[$(date)] Backup succeeded: $BACKUP_FILE” >> “$LOG_FILE”
保留策略
find “$BACKUP_DIR” -name “${DB_NAME}_*.dump” -mtime +$RETENTION_DAYS -delete
- Crontab（每天 02:00）
- 0 2 * * * /usr/local/bin/pg_backup.sh
- 大型库优化：使用Directory 格式（-F d）+ --jobs=N并行备份/恢复（pg_dump/pg_restore 均支持）。
- 物理备份与 PITR 关键步骤
- 启用 WAL 归档（postgresql.conf）：
- wal_level = replica
- archive_mode = on
- archive_command = ‘cp %p /archive/%f’
- 基础备份：
- pg_basebackup -h localhost -U postgres -D /backups/base_$(date +%F) -Ft -z -P -X stream -R
- 恢复要点：基于基础备份与归档 WAL 执行时间点恢复（按官方流程配置 recovery 参数并启动）。
五 验证与运维规范
- 定期恢复演练：按周/月在隔离环境恢复全量与抽样备份，记录恢复时间与数据一致性结果。
- 完整性校验：对备份文件做校验和或尝试干跑恢复；保留校验日志。
- 安全与合规：备份文件加密与最小权限访问；云端备份启用版本与保留策略；对含敏感数据的库，备份链路全程TLS加密。
- 监控与告警：记录成功/失败日志并接入告警（邮件/IM）；监控磁盘空间与WAL 归档延迟。
- 配置备份：定期备份**pgAdmin 配置目录（~/.pgadmin4/）**与服务器端全局对象，确保平台层可恢复。
