Ubuntu 上恢复 PostgreSQL 数据的常用方法

一 准备与版本要求

• 确认目标库版本：恢复工具版本需与目标库匹配，建议大于或等于源库版本；如使用自定义格式转储，工具需PostgreSQL 13+。
• 安装客户端工具（若未安装）：sudo apt update & & sudo apt install postgresql-client。
• 规划恢复方式：
  • 逻辑备份：使用 pg_dump/pg_restore 导入到现有或新建数据库。
  • 时间点恢复 PITR：基于基础备份 + WAL 归档恢复到指定时间或 LSN。
• 权限与目录：准备恢复目录，确保 postgres 用户可读写；如从云上下载备份，目标端安装与备份相同大版本的 PostgreSQL。

二 逻辑恢复 使用 pg_dump 与 pg_restore

• 场景：有 .sql 脚本或 自定义格式（-Fc） 的备份文件，恢复到现有或新建数据库。
• 步骤：
  1. 若目标库已存在且有数据，建议新建空库：
     • psql -U postgres -c “CREATE DATABASE targetdb; ”
  2. SQL 脚本恢复（纯文本 .sql）：
     • psql -U postgres -d targetdb -f /path/backup.sql
  3. 自定义格式恢复（-Fc，适合保留对象权限、并行等）：
     • pg_restore -U postgres -d targetdb /path/backup.dump
     • 如需先清空目标对象冲突：pg_restore -U postgres -d targetdb /path/backup.dump -c
  4. 验证：
     • psql -U postgres -d targetdb -c “\dt”
     • psql -U postgres -d targetdb -c “SELECT COUNT(*) FROM your_table; ”
• 提示：
  • 使用 -Fc 时优先用 pg_restore；纯文本 .sql 用 psql。
  • 目标库为空可避免对象冲突；必要时用 -c 清理再导入。

三 时间点恢复 PITR 基于基础备份与 WAL 归档

• 适用：误删表/行后，需恢复到某个时间点或某个 LSN。
• 前提：已配置并验证 WAL 归档（archive_mode=on，archive_command 正常），且有可用的基础备份（如 pg_basebackup 或文件系统拷贝）。
• 步骤：
  1. 准备恢复目录并放置基础备份（示例：$PGDATA 为 /var/lib/postgresql/16/main）：
     • sudo -u postgres cp -a /backup/base/ $PGDATA
     • sudo chown -R postgres:postgres $PGDATA
  2. 在 $PGDATA 下创建恢复信号文件并配置恢复参数（postgresql.conf 或 recovery.conf，取决于版本）：
     • touch $PGDATA/recovery.signal
     • 基本参数示例：
       • restore_command = ‘cp /path/to/wal_archive/%f %p’
       • recovery_target_time = ‘2025-11-27 10:00:00’ # 恢复到该时间点

       • 或使用 LSN：recovery_target_lsn = ‘0/3000028’

       • 如有多时间线：recovery_target_timeline = ‘latest’

  3. 启动数据库进入恢复模式：
     • sudo systemctl start postgresql
  4. 观察日志，确认达到目标后自动切换为读写：
     • tail -f /var/log/postgresql/postgresql-16-main.log
     • 成功会看到 “archive recovery complete” 与 “database system is ready to accept connections”
  5. 验证数据后，可按需移除或重命名 recovery.signal，防止再次进入恢复模式。
• 关键要点：
  • 时间点必须落在已归档 WAL 的可恢复范围内；设置过早会丢失后续正确事务，过晚可能报 “recovery ended before configured recovery target was reached”。
  • 若需恢复到故障前的最新状态，可用 recovery_target_timeline=latest。

四 常见问题与排查

• 版本不兼容：pg_dump/pg_restore 版本需≥目标库；自定义格式转储建议 13+。
• 权限与目录：恢复目录与数据文件需 postgres:postgres 权限；云上下载的备份先解压到恢复目录再导入。
• 目标库对象冲突：已有同名表/索引/角色会报错，建议用新建空库或加 -c 清理后再导入。
• PITR 目标不可达：出现 “recovery ended before configured recovery target was reached”，将 recovery_target_time 或 recovery_target_lsn 适当提前后重试。
• 云数据库限制：如源库开启 TDE（透明数据加密），通常不支持恢复到自建数据库。

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！