PostgreSQL插件—数据恢复工具pg_recovery使用详解

PostgreSQL插件—数据恢复工具pg_recovery使用详解

说明

pg_recovery 是一款基于PostgreSQL的数据恢复工具。针对表做了 update/delete/rollback/dropcolumn 后的数据恢复。

版本支持

pg_revovery当前支持 PostgreSQL 12/13/14 。

安装

下载插件

摩天轮下载地址：https://www.modb.pro/download/434516
github下载地址：https://github.com/radondb/pg_recovery

安装插件

解压

[postgres@lyp ~]$ ls -rlt pg_recovery-master.zip 
-rw-r--r--. 1 postgres postgres 13023 Feb 15 20:42 pg_recovery-master.zip
[postgres@lyp ~]$ unzip pg_recovery-master.zip 
Archive:  pg_recovery-master.zip
886fc628534b43eb27344aaa07aabcc85f4d0b0e
   creating: pg_recovery-master/
  inflating: pg_recovery-master/.gitignore  
  inflating: pg_recovery-master/License  
  inflating: pg_recovery-master/Makefile  
  inflating: pg_recovery-master/README.md  
  inflating: pg_recovery-master/README_zh_CN.md  
   creating: pg_recovery-master/expected/
  inflating: pg_recovery-master/expected/recovery.out  
  inflating: pg_recovery-master/pg_recovery--1.0.sql  
  inflating: pg_recovery-master/pg_recovery.c  
  inflating: pg_recovery-master/pg_recovery.control  
   creating: pg_recovery-master/sql/
  inflating: pg_recovery-master/sql/recovery.sql  
[postgres@lyp ~]$ 

编译安装

[postgres@lyp ~]$ cd pg_recovery-master/
[postgres@lyp pg_recovery-master]$ make PG_CONFIG=/opt/pgsql14.1/bin/pg_config
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal  -D_GNU_SOURCE   -c -o pg_recovery.o pg_recovery.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pg_recovery.so pg_recovery.o -L/opt/pgsql14.1/lib    -Wl,--as-needed -Wl,-rpath,'/opt/pgsql14.1/lib',--enable-new-dtags  
[postgres@lyp pg_recovery-master]$ 

[postgres@lyp pg_recovery-master]$ make install PG_CONFIG=/opt/pgsql14.1/bin/pg_config
/usr/bin/mkdir -p '/opt/pgsql14.1/lib'
/usr/bin/mkdir -p '/opt/pgsql14.1/share/extension'
/usr/bin/mkdir -p '/opt/pgsql14.1/share/extension'
/usr/bin/install -c -m 755  pg_recovery.so '/opt/pgsql14.1/lib/pg_recovery.so'
/usr/bin/install -c -m 644 .//pg_recovery.control '/opt/pgsql14.1/share/extension/'
/usr/bin/install -c -m 644 .//pg_recovery--1.0.sql  '/opt/pgsql14.1/share/extension/'
[postgres@lyp pg_recovery-master]$ 

创建extension

[postgres@lyp pg_recovery-master]$ psql
psql (14.1)
Type "help" for help.

postgres=# create extension pg_recovery ;
CREATE EXTENSION
postgres=# \dx pg_recovery
                                     List of installed extensions
    Name     | Version | Schema |                             Description                             
-------------+---------+--------+---------------------------------------------------------------------
 pg_recovery | 1.0     | public | recovery table data of update/delete/rollback rows and drop columns
(1 row)

postgres=# 

示例

创建测试数据

postgres=# create table lxs(id1 int,id2 int);
CREATE TABLE
postgres=# insert into lxs values (1,11);
INSERT 0 1
postgres=# insert into lxs values (2,22);
INSERT 0 1
postgres=# select * from lxs;
 id1 | id2 
-----+-----
   1 |  11
   2 |  22
(2 rows)

postgres=# 

recovery update

模拟update修改数据

postgres=# update lxs set id1 = 3,id2=33 where id1=1 and id2=11;
UPDATE 1
postgres=# update lxs set id1 = 4,id2=44 where id1=2 and id2=22;
UPDATE 1
postgres=# select * from lxs;
 id1 | id2 
-----+-----
   3 |  33
   4 |  44
(2 rows)

postgres=# select * from pg_recovery('lxs') as (id1 int,id2 int);
 id1 | id2 
-----+-----
   1 |  11
   2 |  22
(2 rows)

postgres=# 

recovery delete

模拟delete删除数据

postgres=# delete from lxs ;
DELETE 2
postgres=# select * from lxs;
 id1 | id2 
-----+-----
(0 rows)

postgres=# select * from pg_recovery('lxs') as (id1 int,id2 int);
 id1 | id2 
-----+-----
   1 |  11
   2 |  22
   3 |  33
   4 |  44
(4 rows)

postgres=# 

recovery rollback

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

postgres=# begin ;
BEGIN
postgres=*# insert into lxs values(5, 55);
INSERT 0 1
postgres=*# rollback ;
ROLLBACK
postgres=# select * from lxs;
 id1 | id2 
-----+-----
(0 rows)

postgres=# select * from pg_recovery('lxs') as (id1 int, id2 int);
 id1 | id2 
-----+-----
   1 |  11
   2 |  22
   3 |  33
   4 |  44
   5 |  55
(5 rows)

postgres=# 

recovery drop column

模拟删除的列

postgres=# alter table lxs drop column id2;
ALTER TABLE
postgres=# select attnum from pg_attribute a, pg_class where attrelid = pg_class.oid and pg_class.relname='lxs' and attname ~ 'dropped';
 attnum 
--------
      2
(1 row)

postgres=# select * from lxs;
 id1 
-----
(0 rows)

postgres=# select * from pg_recovery('lxs') as (id1 int, dropped_attnum_2 int);
 id1 | dropped_attnum_2 
-----+------------------
   1 |               11
   2 |               22
   3 |               33
   4 |               44
   5 |               55
(5 rows)

postgres=# 

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

show all data

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

postgres=# insert into lxs values(6);
INSERT 0 1
postgres=# select * from lxs;
 id1 
-----
   6
(1 row)

postgres=# select * from pg_recovery('lxs', recoveryrow => false) as (id1 int, dropped_attnum_2 int, recoveryrow bool);
 id1 | dropped_attnum_2 | recoveryrow 
-----+------------------+-------------
   1 |               11 | t
   2 |               22 | t
   3 |               33 | t
   4 |               44 | t
   5 |               55 | t
   6 |                  | f
(6 rows)

postgres=# 

注意事项

pg_recovery是通过读取postgresql表中的死元组进行数据恢复。

如果表做了vacuum或者vacuum full操作清理了死元组后，pg_recovery无法对表数据进行恢复。

涉及参数

• vacuum_defer_cleanup_age (integer)

  postgres=# show vacuum_defer_cleanup_age;
   vacuum_defer_cleanup_age 
  --------------------------
   0
  (1 row)
  
  postgres=# 
  

  指定VACUUM和HOT更新在清除死亡行版本之前，应该推迟多久（以事务数量计）。默认值是零个事务，表示死亡行版本将被尽可能快地清除，即当它们不再对任何打开的事务可见时尽快清除。

  这个参数只能在postgresql.conf文件中或在服务器命令行上设置。