Debian PostgreSQL数据恢复实战案例

实战场景与恢复路径总览

场景	关键前提	首选方法	关键命令或要点
事务未提交	仍在同一个会话/事务中	直接回滚	在 psql 中执行 ROLLBACK;
已提交但未被 VACUUM 清理	表尚未发生自动清理	读取“已删除但未清理”的元组	安装并使用 pg_dirtyread 扩展导出到新表
已 VACUUM 但 WAL 含整页镜像 FPW	参数 full_page_writes=on 且 WAL 归档/保留完整	从 WAL 提取 FPW 并重建表页	pg_waldump --save-fullpage 提取，覆盖到表文件后用 pg_dirtyread 导出
有基础备份 + WAL 归档	已配置 WAL 归档	时间点恢复 PITR	配置 restore_command 与 recovery_target_time 启动恢复
只有逻辑备份	有 pg_dump 导出	逻辑恢复	psql -f backup.sql 或 pg_restore
WAL 损坏/无归档且无法回滚	无备份、WAL 不可用	最后手段：pg_resetwal	仅作兜底，可能丢数据，务必先全量备份当前目录

以上路径按可行性排序，优先尝试事务回滚与 pg_dirtyread，其次利用 WAL/FPW，再到备份恢复，最后才是 pg_resetwal。

场景一事务未提交

现象：误删后未执行 COMMIT;，或仍在同一会话。
处理：在 psql 中直接回滚。
- 示例：
  - BEGIN;
  - DELETE FROM orders WHERE id = 100;
  - ROLLBACK; – 数据立即恢复
要点：一旦提交，DELETE 仅将元组标记为“已删除”（设置 xmax），进入可被清理阶段，无法用 ROLLBACK 恢复。

场景二已提交但未被 VACUUM 清理

原理：DELETE 后数据仍在数据页中，直到 VACUUM/autovacuum 回收。关键时间窗取决于表大小和参数 autovacuum_vacuum_scale_factor（默认 20%）与 autovacuum 周期（常见约 1 分钟量级）。
步骤（Debian 12，示例库名 sales，表名 customers）：
1. 安装扩展（以 PostgreSQL 16 为例，其他版本替换为对应版本号）
  - sudo apt install postgresql-16-dirtyread
2. 在目标库创建扩展
  - CREATE EXTENSION pg_dirtyread;
3. 将“已删除但未清理”的数据导出到新表
  - CREATE TABLE recovered_customers AS SELECT * FROM pg_dirtyread(‘customers’) AS t(id integer, name text, email text, …); – 列定义需与原表一致
4. 校验后回灌
  - – 校验 SELECT COUNT(*) FROM recovered_customers WHERE email LIKE ‘%@example.com’;
  - – 回灌到原表（建议先备份原表或在一个事务中校验） INSERT INTO customers SELECT * FROM recovered_customers WHERE id NOT IN (SELECT id FROM customers);
限制：一旦 autovacuum 运行并回收了该页，pg_dirtyread 将无法读取被清理的元组。

场景三已 VACUUM 但 WAL 含整页镜像 FPW

场景四有基础备份 + WAL 归档的时间点恢复 PITR

前提：已配置 WAL 归档（archive_mode=on，archive_command 正确），并保有基础备份（如 pg_basebackup 或文件系统拷贝）。
步骤（Debian 常见数据目录 /var/lib/postgresql/16/main）：
1. 准备恢复目录并恢复基础备份
  - sudo systemctl stop postgresql
  - sudo -u postgres rsync -a /backup/base_2025-12-24/ /var/lib/postgresql/16/main/
2. 配置恢复参数（postgresql.auto.conf 或 recovery.conf）
  - restore_command = ‘cp /archive/%f %p’
  - recovery_target_time = ‘2025-12-25 10:20:00+08’ – 误删前的时间点
  - recovery_target_timeline = ‘latest’
3. 启动到恢复模式并自动回放
  - sudo systemctl start postgresql
  - 观察日志，确认已达目标时间点并自动进入只读恢复完成状态
  - 如为备用库演练，可在达到目标后执行 SELECT pg_wal_replay_resume(); 继续回放
4. 校验后提升为主库（如需要）
  - 在备库上执行：SELECT pg_promote();
要点：PITR 是最稳妥、可验证、可回滚的恢复方式；务必确保归档连续性与基础备份一致性。

场景五只有逻辑备份的恢复

全库逻辑备份恢复
- pg_dumpall -U postgres -f /backup/full_$(date +%F).sql
- 恢复：psql -U postgres -f /backup/full_2025-12-25.sql
单库/自定义格式备份恢复
- 备份：pg_dump -Fc -f db.pgdump dbname
- 恢复：pg_restore -d dbname db.pgdump
适用：无物理备份、仅需部分库/表、或跨大版本迁移。

场景六 WAL 损坏或无归档的最后手段 pg_resetwal

关键检查与常用命令清单

检查是否开启整页写入：SHOW full_page_writes;
查看当前 WAL LSN：SELECT pg_current_wal_lsn();
查看归档命令与会话参数：SHOW archive_command; ! env | grep PG
查看表与数据库标识：
- SELECT oid, datname FROM pg_database;
- SELECT relname, relfilenode FROM pg_class WHERE relname = ‘your_table’;
观察恢复进度与状态：
- 查看日志：journalctl -u postgresql -f
- 在恢复中：SELECT pg_is_in_recovery();
- 达到目标后提升：SELECT pg_promote();

预防与运维建议

最新问答