如何利用pgAdmin在Linux上进行数据备份与恢复

在 Linux 上使用 pgAdmin 进行 PostgreSQL 备份与恢复

一 环境准备与备份策略

• 安装与连接：在 Linux 桌面或浏览器中打开 pgAdmin 4，新增服务器连接（主机、端口、维护数据库、用户名、密码），确保能正常列出数据库对象。
• 备份范围与周期：明确需要备份的对象（整库、单库、特定 Schema/表），结合业务 RPO/RTO 设定备份周期与保留策略。
• 工具与方式：
  • 逻辑备份：pg_dump / pg_restore（适合跨版本迁移、单库/对象级恢复、细粒度选择）。
  • 物理备份：pg_basebackup（适合整机/集群级快速恢复、时间点恢复 PITR 的基座）。
• 存储与合规：选择本地或云端存储，做好权限控制与定期校验（如校验和/还原演练）。

二 使用 pgAdmin 进行备份

• 整库备份（图形界面）
  1. 在左侧树形导航中右键目标数据库，选择 备份…。
  2. 在“备份”对话框中设置：
     • 文件名与保存位置（Linux 路径，如：/var/backups/mydb_20251124.sql 或 .backup）。
     • 备份类型：仅结构、仅数据、结构与数据。
     • 格式：选择 纯文本 SQL（便于跨平台/审阅）或 自定义/TAR（便于 pg_restore 并行与选择性恢复）。
     • 选项：按需勾选“包含大对象（BLOB/CLOB）”“压缩”“角色/表空间随对象导出”等。
  3. 点击“备份”开始，完成后在指定目录查看备份文件。
• 对象/数据导出（表格级）
  • 在表/视图上右键选择 导出…，可导出为 SQL、CSV 等格式；CSV 适合数据交换与批量导入。
• 命令行等价（便于脚本与自动化）
  • 纯文本 SQL：pg_dump -h localhost -U postgres -d mydb -f /var/backups/mydb.sql
  • 自定义归档：pg_dump -h localhost -U postgres -F c -b -v -f /var/backups/mydb.backup mydb

三 使用 pgAdmin 进行恢复

• 整库恢复（图形界面）
  1. 若目标库不存在：先新建空库（注意 Owner/编码/表空间 与原库一致）。
  2. 右键目标库选择 恢复…，在对话框中选择备份文件并设置：
     • 角色/维护数据库、格式（SQL 脚本或自定义归档）。
     • 选项：是否“清理/创建对象”“仅数据/仅结构”“包含大对象”等。
  3. 点击“恢复”，完成后用查询或对象浏览验证。
• 命令行等价（更灵活、可并行）
  • SQL 脚本：psql -h localhost -U postgres -d mydb -f /var/backups/mydb.sql
  • 自定义归档：pg_restore -h localhost -U postgres -d mydb -j 4 -v /var/backups/mydb.backup（-j 启用并行恢复）
• 重要提示
  • 纯文本 SQL 脚本 用 psql 恢复；自定义/TAR 归档 用 pg_restore 恢复，二者不可混用。
  • 恢复到已存在库时，谨慎选择“清理/覆盖”选项，避免误删现网对象。

四 自动化与进阶方案

• 定时备份（脚本 + cron）
  • 示例脚本（/usr/local/bin/pg_backup.sh）：

    #!/usr/bin/env bash
    set -e
    BACKUP_DIR="/var/backups/postgresql"
    DB="mydb"
    USER="postgres"
    TS=$(date +%F_%H%M%S)
    mkdir -p "$BACKUP_DIR"
    pg_dump -h localhost -U "$USER" -F c -b -v -f "$BACKUP_DIR/${DB}_${TS}.backup" "$DB"
    # 可选：清理 7 天前备份
    find "$BACKUP_DIR" -name "${DB}_*.backup" -mtime +7 -delete
    

  • 加入定时任务：0 2 * * * /usr/local/bin/pg_backup.sh
• 时间点恢复 PITR（物理备份 + WAL 归档）
  • 前提：启用 WAL 归档（postgresql.conf）：wal_level=replica、archive_mode=on、archive_command='cp %p /var/lib/postgresql/archive/%f'。
  • 备份：pg_basebackup -h localhost -U postgres -D /var/backups/base_20251124 -P -X stream -R（-R 自动写入 recovery 配置）。
  • 恢复到指定时间点：在 $PGDATA 下创建 recovery.signal，并配置 restore_command 与 recovery_target_time，然后启动数据库，达到目标时点后自动切换为读写。
• 工具补充：pg_rman 支持在线全量/增量备份与 PITR，适合有严格 RPO/RTO 的场景。

五 常见问题与最佳实践

• 版本与兼容性：尽量保持 备份端与恢复端 PostgreSQL 大版本一致；跨小版本通常可用自定义归档恢复，但需充分演练。
• 角色与权限：备份/恢复使用的数据库角色需具备相应权限；若脚本中指定角色，请确保目标库中存在同名角色。
• 一致性：备份期间尽量避免大规模 DDL；必要时在低峰期执行，或使用物理备份减少业务影响。
• 校验与演练：定期做 还原演练 与 校验和 验证；对关键库保留至少 2–3 个 历史备份。
• 安全：备份文件含敏感数据，务必设置 文件系统权限 与 传输加密（如 scp/sftp/对象存储签名）。
• 区分对象：pgAdmin 是管理工具，其自身配置与元数据与 PostgreSQL 数据库不同；迁移/备份 pgAdmin 配置 需另行处理（如 /etc/pgadmin、/var/lib/pgadmin 等目录）。