centos pgadmin备份恢复操作指南

CentOS 下使用 pgAdmin 进行 PostgreSQL 备份与恢复

一 环境准备与前置检查

• 确保已安装并可用 pgAdmin 4，且能通过 服务器 > 数据库 正常连接到目标 PostgreSQL 实例。
• 备份文件与目录建议放在 /var/lib/pgsql/backups 或 /opt/backups 等持久化目录，并赋予 postgres 用户读写权限：
  • 示例：sudo mkdir -p /var/lib/pgsql/backups && sudo chown postgres:postgres /var/lib/pgsql/backups
• 恢复前先确认目标库是否存在、连接账号权限是否足够，并在非生产环境演练一次流程。

二 使用 pgAdmin 进行备份

• 图形界面步骤
  1. 在左侧树形导航中，右键目标数据库，选择 Backup…。
  2. 在“备份”对话框中配置关键参数：
     • Format：常用为 Custom/Tar（便于后续选择性恢复）或 Plain（SQL 脚本）。
     • Filename：如 /var/lib/pgsql/backups/mydb_20251127.backup。
     • Encoding：如 UTF8；Role name：如 postgres。
     • 在 Data/Objects 中按需勾选 Pre-data / Data / Post-data（全量对象与数据）。
  3. 点击 Backup 开始，完成后在消息面板查看日志与结果。
• 命令行等价方式（便于脚本化与定时）
  • 自定义归档格式（推荐）：pg_dump -h localhost -U postgres -Fc -f /var/lib/pgsql/backups/mydb_20251127.backup mydb
  • 纯 SQL 脚本：pg_dump -h localhost -U postgres -f /var/lib/pgsql/backups/mydb_20251127.sql mydb
  • 说明：自定义格式可用 pg_restore 做选择性恢复；SQL 脚本用 psql 执行。

三 使用 pgAdmin 进行恢复

• 图形界面步骤
  1. 在目标服务器节点下，右键 Databases，选择 Restore…（若覆盖现有库，可先新建同名空库或选择其他目标库）。
  2. 在“恢复”对话框中：
     • 选择备份文件（本地或服务器可访问路径）。
     • 指定 目标数据库（注意：自定义格式备份通常还原到空库；SQL 脚本会执行 DDL/DML）。
     • 按需配置恢复选项（如角色映射、是否包含/排除对象等）。
  3. 点击 Restore 开始，完成后在对象树或日志中核验对象是否恢复成功。
• 命令行等价方式
  • 自定义归档：pg_restore -h localhost -U postgres -d mydb /var/lib/pgsql/backups/mydb_20251127.backup
  • 纯 SQL 脚本：psql -h localhost -U postgres -d mydb -f /var/lib/pgsql/backups/mydb_20251127.sql
  • 说明：若备份为 自定义/Tar，优先使用 pg_restore；若为 SQL，使用 psql 执行。

四 备份恢复策略与自动化

• 策略建议
  • 明确备份范围（整库/模式/表）、周期与保留时间（如每日全量、保留 7–30 天）。
  • 工具选择：逻辑备份用 pg_dump/pg_restore（跨版本迁移、选择性恢复友好）；物理备份用 pg_basebackup（整实例/快速恢复、配合 WAL 归档做时间点恢复）。
  • 存储位置：本地盘 + 远端对象存储/备份服务器，提升可靠性。
• 定时备份示例（cron）
  • 以 postgres 用户每天 01:00 备份为自定义归档：
    • 0 1 * * * /usr/bin/pg_dump -h localhost -U postgres -Fc -f "/var/lib/pgsql/backups/mydb_$(date +\%F).backup" mydb
  • 如需备份全库（含角色/表空间等全局对象），可用 pg_dumpall（注意目标库为空库或按需处理）：
    • 0 2 * * * /usr/bin/pg_dumpall -U postgres -f "/var/lib/pgsql/backups/all_dbs_$(date +\%F).sql"
  • 提示：cron 中使用 % 需转义为 \%，并确保备份目录可写、磁盘空间充足。

五 常见问题与进阶恢复

• 常见问题
  • 权限不足：确保执行用户为 postgres 或具备相应权限；备份/恢复文件与目录权限正确。
  • 目标库非空：自定义归档恢复前建议创建同名空库或使用新库名，避免对象冲突。
  • 编码/角色不一致：在 Backup 对话框中显式设置 Encoding 与 Role name，恢复时保持一致。
  • 误删数据且需回滚到过去时间点：需提前启用 WAL 归档 并使用 pg_rman 执行 PITR（时间点恢复）。
• 进阶时间点恢复（PITR）简要步骤
  1. 配置 postgresql.conf：wal_level=replica、archive_mode=on、archive_command='test ! -f /path/to/archive/%f && cp %p /path/to/archive/%f'、restore_command='cp /path/to/archive/%f %p'，必要时调整 max_wal_senders，重启数据库。
  2. 使用 pg_rman 初始化与备份：pg_rman init -B /home/postgres/backup；pg_rman backup --backup-mode=full -B /home/postgres/backup。
  3. 恢复到指定时间点：pg_rman recover --recovery-target-time="2025-11-27 10:00:00" -B /home/postgres/backup，按提示启动数据库。
  • 说明：PITR 要求归档完整且恢复流程在演练环境验证过。