Ubuntu中PostgreSQL数据库恢复技巧

Ubuntu下PostgreSQL数据库恢复技巧

一、常见恢复场景与对应方法

• 逻辑备份恢复（自定义/目录/TAR 或 SQL 脚本）：使用 pg_restore 或 psql 将备份导入到目标库，适合单库迁移、结构+数据恢复、跨机器恢复。
• 时间点恢复 PITR（WAL 归档）：基于基础备份 + WAL 归档 恢复到指定时间点或最新状态，适合误删表/行、介质故障、回滚错误事务。
• 版本兼容恢复：高版本备份在低版本恢复时，优先导出为 纯 SQL 脚本 再导入，规避自定义格式不兼容问题。
• 图形化工具恢复：使用 pgAdmin 的 Restore 向导执行自定义/目录/TAR 备份恢复，便于可视化管理。
  以上方法覆盖日常运维与应急场景，建议按备份类型选择对应路径。

二、逻辑备份恢复步骤

• 准备目标库：先创建空库（注意与备份时一致的 owner/编码/模板 等），例如：sudo -u postgres createdb your_db。
• 自定义/目录/TAR 备份（.dump/.tar/.dir）：
  • pg_restore -U postgres -d your_db -v /path/backup.dump
  • 常用选项：-c（清理目标对象）、-n schema（仅某 schema）、-t table（仅某表）、-j N（并行导入）。
• SQL 脚本备份（.sql）：
  • psql -U postgres -d your_db -f /path/backup.sql
• 验证：连接数据库检查对象与数据，例如 \dt、SELECT COUNT(*) FROM your_table;
• 图形化方式：在 pgAdmin 中右键目标库 → Restore → 选择备份文件与格式 → 执行并查看日志。

三、时间点恢复 PITR 步骤

• 前提条件：已配置 WAL 归档（生产建议长期开启），并有可用的基础备份（如 basebackup）。
• 配置恢复参数（在目标数据目录的 postgresql.conf 或 recovery 配置段）：
  • wal_level = replica
  • archive_mode = on
  • archive_command = ‘cp %p /var/lib/postgresql/pgbackup/archive_wals/%f’
• 执行基础备份（示例）：
  • pg_basebackup -h primary_host -D /var/lib/postgresql/13/main -Ft -z -P
• 进入恢复模式：
  • 在目标数据目录创建空文件：touch recovery.signal
  • 配置恢复目标（三选一或组合）：
    • 恢复到最新：recovery_target_timeline = ‘latest’
    • 恢复到指定时间：recovery_target_time = ‘2025-12-10 10:00:00+08’
    • 恢复到指定 LSN：recovery_target_lsn = ‘0/12345678’
  • 指定 WAL 获取方式：restore_command = ‘cp /var/lib/postgresql/pgbackup/archive_wals/%f %p’
• 启动实例：pg_ctl -D /var/lib/postgresql/13/main start
• 观察日志：tail -f server.log，看到 “archive recovery complete” 后，实例会自动以 read/write 模式打开；如需保留为备用库，可改为 standby_mode = on 并配置 primary_conninfo。
• 归档完整性检查：必要时手动切换 WAL 验证归档是否正常，例如：SELECT pg_switch_wal();。

四、版本兼容与常见错误处理

• 高版本到低版本恢复：
  • 问题：自定义/目录/TAR 备份可能包含新版本特性，低版本 pg_restore 不兼容。
  • 解决：备份时用 pg_dump -F p（纯 SQL 脚本），在低版本用 psql 导入。
• 权限与连接：
  • 使用 postgres 系统用户或具备 CREATEDB/SUPERUSER 权限的角色执行恢复；必要时在命令前加 sudo -u postgres。
• 目标库状态：
  • 恢复前确保目标库为空（或使用 -c 清理），避免对象冲突。
• 归档断档：
  • 恢复时报 “could not open file … No such file or directory”，多为 WAL 归档缺失；需补齐归档或改用更早的基础备份。
• 时间/时区：
  • 使用 recovery_target_time 时，注意与服务器 时区 一致，建议以 UTC 记录与指定。
• 空间与性能：
  • 大库恢复时启用 -j 并行（自定义/目录格式），并确保 archive_command 目标磁盘空间充足。

五、实用命令清单

• 创建库：sudo -u postgres createdb your_db
• 逻辑恢复：
  • pg_restore -U postgres -d your_db -v /path/backup.dump
  • psql -U postgres -d your_db -f /path/backup.sql
• 基础备份：pg_basebackup -h primary_host -D /var/lib/postgresql/13/main -Ft -z -P
• 触发 WAL 切换：SELECT pg_switch_wal();
• 进入恢复：touch $PGDATA/recovery.signal
• 启动/查看日志：pg_ctl -D $PGDATA start；tail -f server.log
• 图形化：pgAdmin → 目标库 → Restore（选择 .dump/.sql 与相应选项）。