PostgreSQL插件—數據恢復工具pg_recovery使用詳解

PostgreSQL插件—數據恢復工具pg_recovery使用詳解

說明

pg_recovery 是一款基于PostgreSQL的數據恢復工具。針對表做了 update/delete/rollback/dropcolumn 后的數據恢復。

版本支持

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

安裝

下載插件

摩天輪下載地址：http://www.sunline.cc/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文件中或在服務器命令行上設置。