ubuntu postgresql数据恢复方法

Ubuntu 上 PostgreSQL 数据恢复方法

一、先判断你的备份类型与恢复目标

• 你手头是逻辑备份（由 pg_dump/pg_dumpall 生成，常见为 .sql 或 自定义格式 .dump/.backup），还是物理备份（由 pg_basebackup 生成的基础备份，配合 WAL 归档做时间点恢复）。
• 你要恢复到最新状态、指定时间点（PITR）、还是仅某个库/表。
• 恢复环境与原库PostgreSQL 大版本尽量一致；逻辑备份可跨小版本，物理备份与 PITR 通常要求同版本。
• 若准备做 PITR，确认已开启 WAL 归档（archive_mode=on，archive_command） 且归档目录中保留了从基础备份到故障前的连续 WAL。

二、逻辑恢复步骤（pg_dump/pg_dumpall）

• 场景：误删表/数据、跨版本迁移、只恢复单个库或部分对象。
• 准备：安装同版本 PostgreSQL；如目标库不存在先创建；确保备份文件可读。
• 恢复命令：
  • SQL 文本备份（.sql）：
    • 创建目标库（如不存在）：createdb -U postgres your_db
    • 执行恢复：psql -U postgres -d your_db -f backup.sql
  • 自定义格式备份（.dump/.backup）：
    • 创建目标库：createdb -U postgres your_db
    • 执行恢复：pg_restore -U postgres -d your_db -F c -j 4 backup.dump（-j 为并行度，可选）
• 注意：若需覆盖目标库，可在恢复前先备份当前库；使用 --clean/–if-exists 谨慎，避免误删现网对象。

三、物理恢复与时间点恢复 PITR（pg_basebackup + WAL 归档）

• 场景：整实例恢复、磁盘损坏、需要恢复到某一精确时间点（如误删表后 1 分钟）。
• 前提：已做过基础备份（如 pg_basebackup），并且 WAL 归档完整可用。
• 步骤：
  1. 停止数据库：sudo systemctl stop postgresql（或按版本停如 postgresql@17-main）。
  2. 备份当前数据目录：mv $PGDATA $PGDATA.bak。
  3. 将基础备份解压/复制到 $PGDATA（确保属主为 postgres:postgres）。
  4. 在 $PGDATA 创建恢复信号文件：touch $PGDATA/recovery.signal（PostgreSQL 12+ 使用此方式触发恢复）。
  5. 配置恢复参数（postgresql.conf 或 $PGDATA/postgresql.auto.conf）：
     • restore_command = ‘cp /path/to/wal_archive/%f %p’
     • 按需设置目标：
       • 恢复到最新：recovery_target_timeline = ‘latest’
       • 恢复到指定 LSN：recovery_target_lsn = ‘0/2000060’
       • 恢复到指定时间：recovery_target_time = ‘2025-08-15 10:30:00’
  6. 启动数据库：sudo systemctl start postgresql；恢复完成后实例自动进入可读写，recovery.signal 会被移除。
  7. 验证：连接数据库检查表/数据是否到位，并核对时间线是否切换（如需要）。

四、常见问题与排错要点

• 归档不完整导致停在“等待 WAL”：检查 archive_command 是否成功执行、归档目录磁盘空间、WAL 是否连续；必要时补齐缺失段或缩短恢复目标时间。
• 恢复过早/过晚：出现 “FATAL: recovery ended before configured recovery target was reached” 表示目标时间超出可恢复范围，适当提前几秒重试。
• 版本不兼容：物理恢复/PITR 建议同大版本；跨版本迁移优先用逻辑备份。
• 权限与路径：确保 $PGDATA 及归档目录权限为 postgres，路径在 restore_command 中写绝对路径。
• 云上或托管实例：优先使用云控制台提供的克隆/按时间点恢复能力；若下载备份到自建库，注意加密与版本限制（如 TDE 备份通常不支持恢复到自建环境）。

五、恢复后的验证与预防建议

• 验证：抽样查询关键表行数与校验和、核对应用能否正常连接、检查 时间线 ID 与 LSN 是否符合预期。
• 预防：
  • 定期做逻辑备份（单库/全集群）并定期演练恢复；
  • 启用并监控 WAL 归档，保留足够保留期；
  • 重要变更在事务中执行，必要时先备份；
  • 考虑使用专业工具（如 pgBackRest）提升备份/恢复可靠性与效率。

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