工具 | 一条 SQL 实现 PostgreSQL 数据找回

作者:张连壮 PostgreSQL 研发工程师

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

快速找回丢失数据,是数据库的一项重要功能需求,一般建议使用官方推荐的工具。面向开源数据库,生态中也出现很多好用的开源工具。

PostgreSQL 是非常流行的开源数据库,接下来介绍一款近期在社区开源的 PostgreSQL 数据找回工具 pg_recovery ,并实例演示如何找回误操作而丢失的数据。

| 什么是 pg_recovery?

pg_recovery 是一款 PostgreSQL 数据找回工具。可以恢复 COMMIT / DELETE / UPDATE / ROLLBACK / DROP COLUMN 操作后导致的数据变化,并以表的形式返回。安装方便,操作简单。仓库地址:https://github.com/radondb/pg...

快速安装

根据环境配置 PG_CONFIG。

$ make PG_CONFIG=/home/lzzhang/PG/postgresql/base/bin/pg_config
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O0 -fPIC -I. -I./ -I/home/lzzhang/PG/postgresql/base/include/server -I/home/lzzhang/PG/postgresql/base/include/internal  -D_GNU_SOURCE   -c -o pg_recovery.o pg_recovery.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O0 -fPIC -shared -o pg_recovery.so pg_recovery.o -L/home/lzzhang/PG/postgresql/base/lib    -Wl,--as-needed -Wl,-rpath,'/home/lzzhang/PG/postgresql/base/lib',--enable-new-dtags  

$ make install PG_CONFIG=/home/lzzhang/PG/postgresql/base/bin/pg_config
/usr/bin/mkdir -p '/home/lzzhang/PG/postgresql/base/lib'
/usr/bin/mkdir -p '/home/lzzhang/PG/postgresql/base/share/extension'
/usr/bin/mkdir -p '/home/lzzhang/PG/postgresql/base/share/extension'
/usr/bin/install -c -m 755  pg_recovery.so '/home/lzzhang/PG/postgresql/base/lib/pg_recovery.so'
/usr/bin/install -c -m 644 .//pg_recovery.control '/home/lzzhang/PG/postgresql/base/share/extension/'
/usr/bin/install -c -m 644 .//pg_recovery--1.0.sql  '/home/lzzhang/PG/postgresql/base/share/extension/'

初始化插件成功,返回如下信息。

$ create extension pg_recovery ;
CREATE EXTENSION

| 数据找回演示

1. 准备初始化数据

准备一个表和一些数据。

$ create table lzzhang(id int, dp int);
CREATE TABLE
# insert into lzzhang values(1, 1);
INSERT 0 1
$ insert into lzzhang values(2, 2);
INSERT 0 1

2. 找回 UPDATE 数据

对数据进行变更操作,不加 WHERE 条件。

$ update lzzhang set id=3, dp=3;
UPDATE 2
lzzhang=# select * from pg_recovery('lzzhang') as (id int, dp int);
 id | dp 
----+----
  1 |  1
  2 |  2
(2 rows)

$ select * from lzzhang;
 id | dp 
----+----
  3 |  3
  3 |  3
(2 rows)

3. 找回 DELETE 数据

尝试恢复 DELETE 的数据。

$ delete from lzzhang;
DELETE 2
lzzhang=# select * from lzzhang;
 id | dp 
----+----
(0 rows)

$ select * from pg_recovery('lzzhang') as (id int, dp int);
 id | dp 
----+----
  1 |  1
  2 |  2
  3 |  3
  3 |  3
(4 rows)

4. 找回 ROLLBACK 数据

尝试恢复回滚操作之前的数据。

$ begin ;
BEGIN
$ insert into lzzhang values(4, 4);
INSERT 0 1
$ rollback ;
ROLLBACK
$ select * from lzzhang;
 id | dp 
----+----
(0 rows)

$ select * from pg_recovery('lzzhang') as (id int, dp int);
 id | dp 
----+----
  1 |  1
  2 |  2
  3 |  3
  3 |  3
  4 |  4
(5 rows)

5. 找回 DROP COLUMN 数据

尝试恢复表中被删除的列及数据。

$ alter table lzzhang drop column dp;
ALTER TABLE
$ select attnum from pg_attribute, pg_class where attrelid = pg_class.oid and pg_class.relname='lzzhang' and attname ~ 'dropped';
 attnum 
--------
      2
(1 row)

$ select * from lzzhang;
 id 
----
(0 rows)

$ select * from pg_recovery('lzzhang') as (id int, dropped_attnum_2 int);
 id | dropped_attnum_2 
----+------------------
  1 |                1
  2 |                2
  3 |                3
  3 |                3
  4 |                4
(5 rows)

-- dropped_attnum_2: if the drop attnum is 5, set dropped_attnum_2 to dropped_attnum_5

6. 显示找回数据

显示该表历史上所有写入过的数据。

$ insert into lzzhang values(5);
INSERT 0 1
$ select * from lzzhang;
 id 
----
  5
(1 row)

$ select * from pg_recovery('lzzhang', recoveryrow => false) as (id int, recoveryrow bool);
 id | recoveryrow 
----+-------------
  1 | t
  2 | t
  3 | t
  3 | t
  4 | t
  5 | f
(6 rows)

注意事项

  • 支持的 PostgreSQL 版本

目前 pg_revovery工具已支持 PostgreSQL 12/13/14 。

  • 可恢复事务数

PostgreSQL 通过参数 vacuum_defer_cleanup_age 值大小,可限制可恢复的事务数。如果预期需要恢复的数据量较大,可通过配置参数值,提高可恢复的事务数。

pg_recovery 通过读取 PostgreSQL dead 元组来恢复不可见的表数据。如果元组被 vacuum 清除掉,那么 pg_recovery 便不能恢复数据。

  • 锁请求

pg_recovery 使用期间,支持正常的读表的锁请求。此外 pg_recovery未使用期间,不会对数据库造成任何额外的开销或是影响,无需暂停服务。

RadonDB 开源社区,一个面向云原生、容器化的数据库开源社区!

7 声望
8 粉丝
0 条评论
推荐阅读
容器化|自建 MySQL 集群迁移到 Kubernetes
如果你有自建的 MySQL 集群,并且已经感受到了云原生的春风拂面,想将数据迁移到 Kubernetes 上,那么这篇文章可以给你一些思路。

RadonDB阅读 294

万字详解,吃透 MongoDB!
MongoDB 是一个基于 分布式文件存储 的开源 NoSQL 数据库系统,由 C++ 编写的。MongoDB 提供了 面向文档 的存储方式,操作起来比较简单和容易,支持“无模式”的数据建模,可以存储比较复杂的数据类型,是一款非常...

JavaGuide6阅读 913

封面图
一次偶然机会发现的MySQL“负优化”
今天要讲的这件事和上述的两个sql有关,是数年前遇到的一个关于MySQL查询性能的问题。主要是最近刷到了一些关于MySQL查询性能的文章,大部分文章中讲到的都只是一些常见的索引失效场合,于是我回想起了当初被那个...

骑牛上青山8阅读 1.9k评论 2

源码编译安装Postgresql 11.4
postgresql版本: 11.4(因为12还是beta版)2 准备工作2.1 安装gcc编辑器 {代码...} gcc用于编译源码2.2 安装make {代码...} make用于批量编译链接源码,实际干活的还是gcc2.3 安装readline {代码...} readline是...

SHIYIBODEC1阅读 8.2k

手写一个同步服务端时间的小工具
在前端开发的过程中,开发者经常会用到 new Date() 来获取当前时间,但是 new Date() 是获取的当前操作系统的时间,由于用户可以修改当前电脑时间,所以它是不准确的。

jump__jump2阅读 937

Mysql索引覆盖
通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但...

京东云开发者2阅读 746

封面图
译文 | A poor man's API
在 API 日渐流行的年代,越来越多的非技术人员也希望能从 API 的使用中获利,而创建一套成熟的 API 方案需要时间成本和金钱两方面的资源加持。在这个过程中,你需要考虑模型、设计、REST 原则等,而不仅仅是编写...

Apache_APISIX2阅读 863

RadonDB 开源社区,一个面向云原生、容器化的数据库开源社区!

7 声望
8 粉丝
宣传栏