Ubuntu中pgAdmin备份恢复策略

Ubuntu下使用 pgAdmin 的备份与恢复策略

一 策略总览

• 备份类型与工具
  • 逻辑备份：使用 pg_dump / pg_dumpall，适合按库/模式/表粒度恢复、跨版本迁移、开发测试环境。
  • 物理备份：使用 pg_basebackup，适合整机/集群级快速恢复与时间点恢复（PITR）。
• 备份对象与范围
  • 数据库对象（库/模式/表）、全局对象（角色、表空间等）需分别规划；必要时组合使用逻辑与物理备份。
• 存储与保留
  • 备份落盘到与数据库服务器不同的磁盘/网络存储，建议配合 异地/云存储；制定按重要性分级的保留周期与定期清理策略。
• 自动化与监控
  • 使用 cron 或 pgAgent 定时执行；记录日志、保留执行结果、异常告警与定期恢复演练，验证备份可用性与 RPO/RTO。

二 备份策略与实施

• 逻辑备份（pg_dump）
  • 适用：单库/多库、选择性对象恢复、跨平台迁移。
  • 示例：
    • 仅结构：pg_dump -h localhost -U postgres -s mydb > mydb_schema.sql
    • 仅数据：pg_dump -h localhost -U postgres -a mydb > mydb_data.sql
    • 自定义归档（推荐，便于并行与对象级恢复）：pg_dump -h localhost -U postgres -F c -f mydb.dump mydb
  • 自动化：crontab 每日 2 点备份并保留 30 天
    • 0 2 * * * /usr/bin/pg_dump -h localhost -U postgres -F c -f /backups/mydb_$(date +%F).dump mydb && find /backups -name “mydb_*.dump” -mtime +30 -delete
• 全局对象与角色
  • 使用 pg_dumpall -g 导出角色/表空间等全局对象，与库级备份分开管理，便于在新集群快速重建安全边界。
• 物理备份（pg_basebackup）
  • 适用：需要快速全量恢复与 时间点恢复（PITR） 的场景。
  • 示例：pg_basebackup -h localhost -U replicator -D /backups/base_2025-12-31 -P -X stream -R
  • 要求：启用 WAL 归档（wal_level=replica 或更高，archive_mode=on，archive_command 指向安全存储）。

三 恢复策略与实施

• 逻辑备份恢复
  • 自定义/目录/Tar 归档：pg_restore -h localhost -U postgres -d mydb mydb.dump
  • SQL 脚本：psql -h localhost -U postgres -f mydb_schema.sql mydb
  • 常用选项：-j 并行度、-c 先清理目标对象、-1 单事务恢复（一致性优先）。
• 物理备份恢复
  • 将备份目录（如 /backups/base_2025-12-31）拷贝为数据目录，按集群配置调整 postgresql.conf 与 recovery.conf/pg_hba.conf；基于 WAL 归档执行 PITR（设置 recovery_target_time 或 recovery_target_lsn），启动后在 pg_wal 目录看到回放进度，达到目标后自动切换为读写模式。
• 使用 pgAdmin 图形界面
  • 备份：在对象树中右键数据库 → Backup…，在 General 选择 Format（Plain/Custom/Tar/Directory）、文件名与编码；在 Data/Objects 选择备份范围；在 Options 配置 OID、DDL 等。
  • 恢复：右键数据库 → Restore…，选择备份文件与格式，按需勾选 Sections（Pre-data/Data/Post-data）、Clean before restore、Single transaction、Exit on error 等，执行并在右下角查看结果。

四 自动化与运维实践

• 调度与日志
  • 简单场景用 cron；复杂依赖与通知建议用 pgAgent（在 pgAdmin 中创建作业，按日/周/月执行并保留日志）。
• 保留与校验
  • 按业务重要性分级保留（如：日备保留 7–30 天，周备保留 3–12 个月）；定期执行恢复演练与 备份完整性校验（如 pg_restore -l/-t 检查、试恢复至测试库）。
• 安全与合规
  • 备份文件落盘权限最小化；敏感数据备份加密与异地/云存储；传输通道使用 TLS；对 pgAdmin 配置与元数据 进行周期性备份（如 ~/.pgadmin4/ 目录）。

五 常见场景与命令速查

• 场景一：单库日常备份（自定义归档 + 压缩 + 保留 30 天）
  • 备份：0 2 * * * /usr/bin/pg_dump -h localhost -U postgres -F c -f /backups/mydb_$(date +%F).dump mydb && find /backups -name “mydb_*.dump” -mtime +30 -delete
  • 恢复：pg_restore -h localhost -U postgres -d mydb /backups/mydb_2025-12-31.dump
• 场景二：全量物理备份 + PITR
  • 备份：pg_basebackup -h localhost -U replicator -D /backups/base_$(date +%F) -P -X stream -R
  • 恢复：配置 recovery_target_time，启动数据库至达到目标时间点后自动转为读写。
• 场景三：迁移/克隆数据库（对象级恢复）
  • 备份：pg_dump -h src -U postgres -F c -f db.dump sourcedb
  • 恢复：createdb -O owner targetdb && pg_restore -h dst -U postgres -d targetdb -j 4 db.dump
• 场景四：仅迁移角色与表空间
  • 备份：pg_dumpall -g > globals.sql
  • 恢复：psql -h localhost -U postgres -f globals.sql。