盘点 | 经常使用 PG 数据恢复方案概览【建议收藏】

2022年01月14日 阅读数:5
这篇文章主要向大家介绍盘点 | 经常使用 PG 数据恢复方案概览【建议收藏】,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

做者:张连壮 PostgreSQL 研发负责人html

从事多年 PostgreSQL 数据库内核开发,对 Citus 有很是深刻的研究。node

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

在盘点数据恢复方案以前,先简单了解一下数据丢失的缘由。github

数据丢失的缘由

数据丢失一般是由 DDL 与 DML 两种操做引发。sql

DDL

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

因为在操做系统中表文件已经不存在,因此只能采用恢复磁盘的方法进行数据恢复。但这种方式找回数据的几率很是小,尤为是云数据库,恢复磁盘数据几乎不可能。数据结构

DML

DML 包含 UPDATE、DELETE 操做。根据 MVCC 的实现,DML 操做并非在操做系统磁盘中将数据删除,所以数据能够经过参数vacuum_defer_cleanup_age 来调整 Dead 元组在数据库中的数量,以便恢复误操做的数据。app

数据恢复方案

pg_resetwal

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

使用示例

pg_resetwal 经过设置事务号的方式来恢复数据,所以必须提早获取待恢复数据的事务号。post

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 数据恢复方案总结

不一样方案适合的场景不一样,从使用难易角度大体作了如下排名(我的建议):

  1. pg_recovery 使用简单,默认只有待找回数据;
  2. pg_dirtyread 使用简单,默认返回所有数据;
  3. WalMiner 须要对 walminer 全面掌握,并作好系统预设;
  4. pg_resetwal 须要了解的内容较多;
  5. pg_filedump 须要单独写一些脚本或工具来配合使用;
  6. pageinspect 难度极大。

若无任何准备,如何恢复数据?推荐如下方法:

  1. 及时设置 vacuum_defer_cleanup_age
  2. 安装 pg_recover 或者 pg_dirtyread
  3. 没法安装插件能够采用 pg_resetwal ,无需任何额外工具

掌握数据恢复工具使用是必不可少的,但在事故发生前采起预防数据丢失的方案更有必要。下一期咱们将从 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