百木园作者：张连壮 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 * from xx
小结
- pg_resetwal 恢复数据操作及时，数据绝对可恢复。
- 在 SERVER 端操作所需权限较高，云数据库可能无法使用。
- 若 DDL 数据无法找回，虽然元信息已经恢复，但数据已经不在磁盘上。 ERROR: could not open file \"base/16392/16396\" 表明文件或目录已经不存在了。
- 启动数据库后，不可以进行任何影响事务号的操作。否则提升事务号将导致数据再次不可见。
- 通过 pg_resetwal 恢复数据前，需将数据 PGDATA 目录进行全量备份，只恢复所需数据
- pg_resetwal 操作难度大，需要掌握的 PG 知识较多。
pg_dirtyread
pg_dirtyread[2] 利用 MVCC 机制读取 Dead 元组。因此可以恢复 UPDATE、DELETE、DROPCOLUMN、ROLLBACK 等 MVCC 机制操作的数据。pg_dirtyread 不存在于 contrib 目录下，因此需要单独编译。
使用示例
CREATE TABLE foo (bar bigint， baz text);
INSERT INTO foo VALUES (1， \'Test\')， (2， \'New Test\');
DELETE FROM foo WHERE bar = 1;
SELECT * FROM pg_dirtyread(\'foo\') as t(bar bigint， baz text);
bar │ baz
─────┼──────────
1 │ Test
2 │ New Test
小结
- pg_dirtyread 使用非常方便，仅需要安装一个插件便可以找回数据。
- pg_dirtyread 会返回全部数据，包含未被删除的数据。例如示例中 bar=2 的数据。
- 基于 MVCC 机制的操作只能实现 DML 的数据找回。
pg_recovery
pg_recovery[3] 与 pg_dirtyread 类似，但是使用更灵活。目前的版本中默认只返回需要找回的数据 。pg_recovery 的目标致力于数据的找回，而不仅仅是读取 Dead 元组，在后续的版本中，会增加一些辅助数据找回的调试信息，来帮助用户更快的在众多数据中找到自己需要找回的数据。pg_recovery 不存在于 contrib 目录下，因此需要单独编译。
使用示例
CREATE TABLE foo (bar bigint， baz text);
INSERT INTO foo VALUES (1， \'Test\')， (2， \'New Test\');
DELETE FROM foo WHERE bar = 1;
SELECT * FROM pg_recovery(\'foo\') as t(bar bigint， baz text);
bar │ baz
─────┼──────────
1 │ Test
小结
- pg_recovery 的目标是用于数据找回，因此使用起来更方便。在未来的版本中，也会加入更多辅助数据找回的功能。
- pg_recovery(recoveryrow => false) 可以读取出全部数据。
- pg_recovery 只能找回 DML 的数据。
pg_filedump
pg_filedump[4] 是一款命令行工具， 因此只能在服务端执行，并且不需要连接数据库。该工具可以分析出数据文件中数据的详细数据，内容格式与 pageinspect 类似。
使用示例
./pg_filedump -D int，varchar dj/base/24679/24777
Item 1 -- Length: 30 Offset: 8160 (0x1fe0) Flags: NORMAL
COPY: 1 a
Item 2 -- Length: 113 Offset: 8040 (0x1f68) Flags: NORMAL
COPY: 2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
Item 3 -- Length: 203 Offset: 7832 (0x1e98) Flags: NORMAL
COPY: 2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
小结
- pg_filedump 可以直接读取文件，无需连接数据库，适用于严重灾难的情况。但是需要知道具体的文件位置，适用性不强。
- pg_filedump 可直接通过 SQL 将数据一键找回，需要编译找回数据方法。
- pg_filedump 无法找回自定义数据类型的数据。
- pg_filedump 由于只能在服务端执行，不适用于用于云数据库的数据找回。
WalMiner
WalMiner[5] 是从 PostgreSQL 的 WAL（write ahead logs）日志的解析工具，旨在挖掘 WAL 日志所有的有用信息，从而提供 PG 的数据恢复支持。目前主要有如下功能：
- 从 WAL 日志中解析出 SQL，包括 DML 和少量 DDL
解析出执行的 SQL 语句的工具，并能生成对应的 UNDO SQL语句。与传统的 logical decode 插件相比，WalMiner 不要求 logical 日志级别且解析方式较为灵活。
- 数据页挽回
当数据库被执行了 TRUNCATE 等不被 WAL 记录的数据清除操作或者发生磁盘页损坏时，可使用此功能从 WAL 日志中搜索数据，尽量挽回数据。
使用示例
postgres=# select record_database，record_user，op_text，op_undo from walminer_contents;
-[ RECORD 1 ]---+------------------------------------------------------------------------------------------------------
record_database | postgres
record_user | lichuancheng
op_text | INSERT INTO \"public\"。\"t2\"(\"i\"， \"j\"， \"k\") VALUES(1， 1， \'qqqqqq\');
op_undo | DELETE FROM \"public\"。\"t2\" WHERE \"i\"=1 AND \"j\"=1 AND \"k\"=\'qqqqqq\' AND ctid = \'(0，1)\';
小结
- WalMiner 通过 WAL 日志进行找回，只要日志保存量足够，便可以找回数据。
- WalMiner 可以通过与存储过程的结合，来实现一键数据找回的功能。
pageinspect
pageinspect[6] 是 PostgreSQL 自带的插件，存在于源码 contrib 目录中，具备更高的稳定。
pageinspace 可以查看数据二进制的存储方式，并且可以读取 Dead 元组，因此可以用于数据找回和查看所需找回的数据是否存在。
数据结构
struct varlena
{
char vl_len_[4]; /* Do not touch this field directly! */
char vl_dat[FLEXIBLE_ARRAY_MEMBER]; /* Data content is here */
};
使用示例
test=# SELECT tuple_data_split(\'lzzhang\'::regclass， t_data， t_infomask， t_infomask2， t_bits) FROM heap_page_items(get_raw_page(\'lzzhang\'， 0));
tuple_data_split
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{\"\\\\x01000000\"，\"\\\\x0561\"} {\"\\\\x02000000\"，\"\\\\xab616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161\"}
{\"\\\\x02000000\"，\"\\\\xbc020000616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161\"}
(3 行记录)
小结
- pageinspacet 通常用于底层数据存储的分析，极难恢复数据，复杂的自定义数据类型，恢复更加困难。虽然可以找回数据，但不推荐。
- 数据不直观，例如 {\"\\\\x01000000\"，\"\\\\x0561\"} 。
- 数据的先后顺序，需要参考 pg_attribute 来获知返回的数据对应的列。
- 需要对 PG 源码深度掌握，同一数据类型不同长度数据格式不同。例如\"\\\\x0561\"， \"\\\\xab6161\"， \"\\\\xbc020000616161”，61 代表字母 a 。
小贴士：保留多少 Dead 元组最合适？
因为 MVCC 机制，PG 本身自带 autovacuum，通常情况下无需手动维护 MVCC 。但autovacuum 的触发需要一定条件，数据库至少有 10% 以上的数据膨胀，严重的可能超过数据本身。
通过设置参数 vacuum_defer_cleanup_age 可保留部分 Dead 元组，减少数据膨胀对数据库产生的影响。若需要立即清理数据，可在数据存储过程调用 select * from txid_current(); 增加事务号，清空 Dead 元组。
但即使没有设置 vacuum_defer_cleanup_age ，由于 vacuum 不及时，及时操作也可以恢复出数据。
PG 数据恢复方案总结
不同方案适合的场景不同，从使用难易角度大致做了以下排名（个人建议）：
若无任何准备，如何恢复数据？推荐以下方法：
掌握数据恢复工具使用是必不可少的，但在事故发生前采取预防数据丢失的方案更有必要。下一期我们将从 DDL 和 DML 两类操作分别介绍如何预防数据丢失的方案。
参考引用
[1]：pg_resetwal：https://www.postgresql.org/docs/10/app-pgresetwal.html
[2]：pg_dirtyread：https://github.com/df7cb/pg_dirtyread
[3]：pg_recovery：https://github.com/radondb/pg_recovery
[4]：pg_filedump：https://github.com/ChristophBerg/pg_filedump
[5]：WalMiner：https://gitee.com/movead/XLogMiner
[6]：pageinspect：https://www.postgresql.org/docs/10/pageinspect.html
来源：https://www.cnblogs.com/radondb/p/15796728.html
图文来源于网络，如有侵权请联系删除。