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

说明

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

版本支持

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

安装

下载插件

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

安装插件

解压

[postgres@lyp ~]$ ls -rlt pg_recovery-master.zip-rw-r--r--.1 postgres postgres 13023Feb1520:42 pg_recovery-master.zip[postgres@lyp ~]$ unzip pg_recovery-master.zipArchive:  pg_recovery-master.zip886fc628534b43eb27344aaa07aabcc85f4d0b0ecreating: pg_recovery-master/inflating: pg_recovery-master/.gitignoreinflating: pg_recovery-master/License  inflating: pg_recovery-master/Makefile  inflating: pg_recovery-master/README.mdinflating: pg_recovery-master/README_zh_CN.mdcreating: pg_recovery-master/expected/inflating: pg_recovery-master/expected/recovery.out  inflating: pg_recovery-master/pg_recovery--1.0.sqlinflating: pg_recovery-master/pg_recovery.cinflating: pg_recovery-master/pg_recovery.controlcreating: 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]$

创建extension

[postgres@lyp pg_recovery-master]$ psqlpsql (14.1)Type"help"for help.postgres=# create extension pg_recovery ;CREATE EXTENSIONpostgres=# \\dx pg_recoveryList of installed extensionsName|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 |  112 |  22
(2 rows)
postgres=#

recovery update

模拟update修改数据

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

recovery delete

模拟delete删除数据

postgres=#deletefrom lxs ;DELETE 2postgres=#select*from lxs;id1 | id2-----+-----(0 rows)postgres=#select*from pg_recovery('lxs')as(id1 int,id2 int);id1 | id2-----+-----1|112|223|334|44(4 rows)postgres=#

recovery rollback

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

postgres=#begin;BEGINpostgres=*# insert into lxs values(5,55);INSERT 01postgres=*# rollback ;ROLLBACKpostgres=#select*from lxs;id1 | id2-----+-----(0 rows)postgres=#select*from pg_recovery('lxs')as(id1 int, id2 int);id1 | id2-----+-----1|112|223|334|445|55(5 rows)postgres=#
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 |               112 |               223 |               334 |               445 |               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 01postgres=#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| t2|22| t3|33| t4|44| t5|55| t6|| f(6 rows)postgres=#
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文件中或在服务器命令行上设置。