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

通过查询来确定 lsn 的大致的位置。

2. 获取事务号

3. 设置事务号

4. 查看所需数据

小结 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 目录下，因此需要单独编译。

使用示例

小结 pg_dirtyread 使用非常方便，仅需要安装一个插件便可以找回数据。pg_dirtyread 会返回全部数据，包含未被删除的数据。例如示例中 bar=2 的数据。基于 MVCC 机制的操作只能实现 DML 的数据找回。 pg_recovery

pg_recovery[3] 与 pg_dirtyread 类似，但是使用更灵活。目前的版本中默认只返回需要找回的数据 。pg_recovery 的目标致力于数据的找回，而不仅仅是读取 Dead 元组，在后续的版本中，会增加一些辅助数据找回的调试信息，来帮助用户更快的在众多数据中找到自己需要找回的数据。pg_recovery 不存在于 contrib 目录下，因此需要单独编译。

使用示例

小结 pg_recovery 的目标是用于数据找回，因此使用起来更方便。在未来的版本中，也会加入更多辅助数据找回的功能。pg_recovery(recoveryrow => false) 可以读取出全部数据。pg_recovery 只能找回 DML 的数据。 pg_filedump

pg_filedump[4] 是一款命令行工具， 因此只能在服务端执行，并且不需要连接数据库。该工具可以分析出数据文件中数据的详细数据，内容格式与 pageinspect 类似。

使用示例

小结 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 日志中搜索数据，尽量挽回数据。

使用示例

小结 WalMiner 通过 WAL 日志进行找回，只要日志保存量足够，便可以找回数据。WalMiner 可以通过与存储过程的结合，来实现一键数据找回的功能。 pageinspect

pageinspect[6] 是 PostgreSQL 自带的插件，存在于源码 contrib 目录中，具备更高的稳定。

pageinspace 可以查看数据二进制的存储方式，并且可以读取 Dead 元组，因此可以用于数据找回和查看所需找回的数据是否存在。

数据结构

使用示例

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

不同方案适合的场景不同，从使用难易角度大致做了以下排名（个人建议）：

pg_recovery 使用简单，默认只有待找回数据；pg_dirtyread 使用简单，默认返回全部数据；WalMiner 需要对 walminer 全面掌握，并做好系统预设；pg_resetwal 需要了解的内容较多；pg_filedump 需要单独写一些脚本或工具来配合使用；pageinspect 难度极大。

若无任何准备，如何恢复数据？推荐以下方法：

及时设置 vacuum_defer_cleanup_age安装 pg_recover 或者 pg_dirtyread无法安装插件可以采用 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

到此这篇关于常用 PostgreSQL 数据恢复方案概览【建议收藏】的文章就介绍到这了,更多相关PostgreSQL 数据恢复内容请搜索七叶笔记以前的文章或继续浏览下面的相关文章希望大家以后多多支持七叶笔记！