常用 PostgreSQL 数据恢复方案及使用示例

作者：张连壮 PostgreSQL 研发负责人

从事多年 PostgreSQL 数据库内核开发，对 Citus 有非常深入的研究。

PostgreSQL 本身不具备数据闪回和数据误删除保护功能，但在不同场景下也有对应的解决方案。本文由作者在 2021 PCC 大会的演讲主题《PostgreSQL 数据找回》整理而来，介绍了常见 数据恢复和 预防数据丢失的相关工具实现原理及使用示例。

在盘点数据恢复方案之前，先简单了解一下数据丢失的原因。

数据丢失的原因

数据丢失通常是由 DDL 与 DML 两种操作引起。

DDL

在 PostgreSQL 数据库中，表以文件的形式，采用 OID 命名规则存储于 PGDATA/base/DatabaseId/relfilenode 目录中。当进行 DROP TABLE 操作时，会将文件整体删除。

由于在操作系统中表文件已经不存在，所以只能采用恢复磁盘的方法进行数据恢复。但这种方式找回数据的概率非常小，尤其是云数据库，恢复磁盘数据几乎不可能。

DML

DML 包含 UPDATE、DELETE 操作。根据 MVCC 的实现，DML 操作并不是在操作系统磁盘中将数据删除，因此数据可以通过参数vacuum_defer_cleanup_age 来调整 Dead 元组在数据库中的数量，以便恢复误操作的数据。

数据恢复方案

pg_resetwal

pg_resetwal[1] 是 PostgreSQL 自带的工具（9.6 及以前版本叫 pg_resetxlog）。可清除预写式日志（WAL）并且可以重置 pg_control 文件中的一些信息。也可以修改当前事务 ID，从而使数据库可以访问到未被 Vacuum 掉的 Dead 元组。

使用示例

pg_resetwal 通过设置事务号的方式来恢复数据，因此必须提前获取待恢复数据的事务号。

1. 查看当前 lsn 位置

— 在线查询
select pg_current_wal_lsn();

— 离线查询
./pg_controldata -D dj | grep ‘checkpoint location’