Ubuntu 上基于 pgAdmin 的 PostgreSQL 备份策略建议
一 策略总览
- 备份类型与适用场景
- 逻辑备份：使用 pg_dump，适合常规备份、跨版本迁移、单表/单库恢复。常用格式为 Custom（.dump），支持压缩与并行恢复；大型库可用 Directory 格式并行处理。
- 物理备份：使用 pg_basebackup，适合快速全量恢复与搭建备机；结合 WAL 归档 可实现时间点恢复（PITR）。
- 备份频率与保留
- 生产库建议：每周一次全量（Custom 或 basebackup）+ 每日增量（WAL）。保留策略示例：全量保留 30 天，WAL 保留覆盖最近一次全量起至保留窗口（如再加 7 天）。
- 存储与隔离
- 备份落盘到与数据库不同的磁盘/分区；关键备份再同步至 离线/云端 做异地冗余。
- 验证与演练
- 每周抽样恢复演练，每月一次完整 PITR 演练；每次备份后校验文件大小与完整性，确保可恢复性。
二 备份方式与工具选择
- pgAdmin 图形界面
- 适合偶发手工备份与小规模环境：数据库或服务器节点右键选择 备份…/恢复…，可直观配置格式、路径、并行度等。
- 命令行与自动化
- 逻辑备份示例：pg_dump -h localhost -U postgres -F c --blobs -f /backups/mydb_$(date +%F).dump mydb
- 物理备份示例：pg_basebackup -h localhost -U postgres -D /backups/base_$(date +%F) -P -X stream -R
- 自动化建议：将 pg_dump/pg_basebackup 脚本化，配合 Cron 定时执行与日志轮转，降低人为失误。
三 自动化与监控落地
- 脚本化与参数化
- 使用环境变量管理连接与保留天数，示例：DB_HOST、DB_PORT、DB_USER、DB_NAME、BACKUP_DIR、RETENTION_DAYS、LOG_FILE。
- 关键参数：Custom 格式（–format=c）、包含大对象（–blobs）、压缩级别（1–9，常用 6；归档/空间紧张用 9）、大库并行（–jobs=N）。
- Cron 定时任务
- 示例（每天 02:00 全量）：0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_cron.log 2>&1
- 日志与告警
- 记录每次备份的文件、耗时、大小；失败时通过邮件/企业微信/钉钉告警（脚本检查退出码 $? 并发送通知）。
- 备份验证
- 每周自动恢复到一个临时库并校验关键表行数/校验和，确保备份有效。
四 时间点恢复 PITR 配置与演练
- 启用 WAL 归档（postgresql.conf）
- wal_level = replica
- archive_mode = on
- archive_command = ‘cp %p /var/wal_archive/%f’
- max_wal_size = 1GB；min_wal_size = 80MB
- 修改后重启数据库；在 pgAdmin 服务器状态面板检查 WAL 级别 与 归档状态。
- 基础备份与恢复流程
- 每周一次基础备份（pg_basebackup 或 pg_dump Custom）。
- 故障恢复：准备恢复目录，放置基础备份与 recovery.signal；在 postgresql.auto.conf 或 recovery.conf 中设置 restore_command 与恢复目标（如 recovery_target_time=‘2025-09-10 14:30:00’），启动实例自动回放至目标时刻。
五 备份范围与安全加固
- 备份范围
- 数据库对象：常规业务库全量备份；超大库可按 schema/表 分组并行备份。
- 全局对象：使用 pg_dumpall --globals-only 备份 角色、表空间 等全局定义，单独保存与版本化管理。
- 配置与元数据
- 备份 pgAdmin 配置与元数据（用于快速恢复管理台）：配置文件常见路径 /etc/pgadmin/，用户数据目录 ~/.pgadmin/；可用 tar/rsync 或 Duplicity 做定期备份。
- 安全与合规
- 备份文件启用 加密 与 最小权限访问控制；传输与落盘均建议加密；定期在隔离环境验证恢复流程与可用性。
