作者:张连壮 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'
通过查询来确定 lsn 的大致的位置。
2. 获取事务号
./pg_waldump -b -s 0/2003B58 -p dj
rmgr: Heap len (rec/tot): 59/ 299, tx: 595, lsn: 0/030001B8, prev 0/03000180, desc: DELETE off 5 KEYS_UPDATED , blkref #0: rel 1663/16392/16393 blk 0 FPW
rmgr: Heap len (rec/tot): 54/ 54, tx: 595, lsn: 0/030002E8, prev 0/030001B8, desc: DELETE off 6 KEYS_UPDATED , blkref #0: rel 1663/16392/16393 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 595, lsn: 0/03000320, prev 0/030002E8, desc: COMMIT 2019-03-26 11:00:23。410557 CST
3. 设置事务号
-- 关闭数据
./pg_resetwal -D dj -x 595
-- 启动数据库
4. 查看所需数据
select</