Ubuntu中pgAdmin的备份与恢复策略

Ubuntu下 pgAdmin 的备份与恢复策略

一、策略总览与选型

• 备份类型与适用场景
  • 逻辑备份：使用 pg_dump / pg_dumpall，导出为 SQL 或 自定义格式（-F c），便于跨小版本迁移、按库/表粒度恢复，但大数据量时导入导出较慢。
  • 物理备份：使用 pg_basebackup，直接复制数据目录，恢复速度快，适合生产环境的快速整机恢复，通常要求 PostgreSQL 版本一致。
  • 时间点恢复 PITR：在物理备份基础上配合 WAL 归档（archive_command），可将库恢复到任意时间点，满足误删、故障回滚等需求。
• 角色与配置备份
  • 全局对象（角色、表空间等）建议用 pg_dumpall --globals-only 单独备份；pgAdmin 自身的配置与连接信息位于 ~/.pgadmin4/，需单独纳入备份。

二、备份策略与实施步骤

• 逻辑备份（pg_dump，适合日常与迁移）
  • 单库导出为 SQL（便于审阅与跨版本）：
    • 命令：pg_dump -U postgres -h localhost -p 5432 -f /backups/mydb_$(date +%F).sql mydb
  • 单库导出为自定义格式（便于并行与选择性恢复）：
    • 命令：pg_dump -U postgres -h localhost -p 5432 -F c -f /backups/mydb_$(date +%F).dump mydb
  • 全库与全局对象：
    • 全库 SQL：pg_dumpall -U postgres -f /backups/all_$(date +%F).sql
    • 仅全局对象：pg_dumpall --globals-only -U postgres -f /backups/globals_$(date +%F).sql
  • 定时任务（cron，示例每日 02:00 备份 mydb）：
    • 0 2 * * * /usr/bin/pg_dump -U postgres -h localhost -p 5432 -F c /backups/mydb_$(date +%F).dump mydb
  • 压缩与保留：建议配合 gzip 压缩与 logrotate/自定义脚本做保留策略（如保留最近 7–30 天）。
• 物理备份（pg_basebackup，适合生产快速恢复）
  • 执行备份：pg_basebackup -U postgres -h localhost -D /backups/base_$(date +%F) -F t -X fetch -P
  • 建议与 WAL 归档配合启用 PITR（见第四部分）。
• pgAdmin 图形化备份
  • 备份：在左侧树中右键数据库 → 备份 → 选择格式（Plain/自定义）、勾选 Pre-data / Data / Post-data → 指定路径开始备份。
  • 恢复：先创建同名空库 → 右键数据库 → 恢复 → 选择备份文件 → 开始恢复。

三、恢复策略与步骤

• 逻辑备份恢复
  • SQL 文件（纯文本）：psql -U postgres -h localhost -p 5432 -f /backups/mydb_2025-08-01.sql mydb
  • 自定义格式（-F c）：pg_restore -U postgres -h localhost -p 5432 -d mydb /backups/mydb_2025-08-01.dump
  • 常用选项：-j N（并行）、-c（先清理目标对象）、-C（自动 CREATE DATABASE）。
• 物理备份恢复
  • 停止数据库：systemctl stop postgresql@12-main（版本号按实际）
  • 清空数据目录并解压基础备份到数据目录（/var/lib/postgresql/12/main）
  • 配置恢复（见第四部分），然后启动：systemctl start postgresql@12-main
• pgAdmin 图形化恢复
  • 新建同名数据库（编码/所有者与原库一致）→ 右键数据库 → 恢复 → 选择备份文件 → 开始恢复。

四、时间点恢复 PITR 配置与演练

• 启用 WAL 归档（主库 postgresql.conf）
  • wal_level = replica
  • archive_mode = on
  • archive_command = ‘test ! -f /archive/%f && cp %p /archive/%f’
  • 创建归档目录并授权：mkdir -p /archive && chown postgres:postgres /archive
  • 重启生效：systemctl restart postgresql@12-main
• 获取基础备份
  • pg_basebackup -U postgres -h localhost -D /backups/base_$(date +%F) -F t -X fetch -P
• 执行时间点恢复
  • 停止数据库并清空数据目录，将基础备份解压至数据目录
  • 在 $PGDATA 创建 recovery.signal（PostgreSQL 12+），并配置：
    • restore_command = ‘cp /archive/%f %p’
    • recovery_target_time = ‘2025-08-01 14:30:00’（按需设置目标时间/事务）
  • 启动服务，PostgreSQL 将自动回放 WAL 至指定时间点后进入只读，确认无误后执行 SELECT pg_wal_replay_resume(); 完成恢复
• 演练与校验
  • 建议每月在测试环境演练一次 PITR，校验 恢复点目标 RPO 与 恢复时间目标 RTO，并验证备份完整性与可用性。

五、安全、合规与运维要点

• 备份存储与保留
  • 备份文件与归档目录应置于安全位置，建议 异地/云存储 留存，至少保留 7–30 天；对敏感数据启用 加密 与 访问控制。
• 权限与账号
  • 执行备份/恢复使用具备足够权限的 数据库超级用户（如 postgres）；操作系统层面限制备份目录访问权限（仅 postgres 可读写）。
• 版本与兼容性
  • 逻辑备份便于 跨小版本 迁移；物理备份与 PITR 通常要求 相同主版本 的 PostgreSQL。
• 配置与元数据
  • 定期备份 pg_dumpall --globals-only（角色/表空间等）与 ~/.pgadmin4/（连接与偏好设置）；变更前后及时更新备份清单与校验值（如 sha256sum）。
• 变更窗口与回滚
  • 恢复操作对业务有影响，建议在 低峰时段 执行，并在恢复前对当前库做一次备份以便快速回滚。