postgresql插件—数据恢复工具pg-m6米乐安卓版下载

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文件中或在服务器命令行上设置。