PostgreSQL学习随笔21 利用工具恢复误删除数据1之pg_recovery

CP的PostgreSQL厨房 2023-05-14

397

前言

数据丢失原因

数据丢失通常是由 DDL 与 DML 两种操作引起。

DDL

在 PostgreSQL 数据库中，表以文件的形式，采用 OID 命名规则存储于 PGDATA/base/DatabaseId/relfilenode 目录中。当进行 DROP TABLE 操作时，会将文件整体删除。

由于在操作系统中表文件已经不存在，所以只能采用恢复磁盘的方法进行数据恢复。但这种方式找回数据的概率非常小，尤其是云数据库，恢复磁盘数据几乎不可能。

DML

DML 包含 UPDATE、DELETE 操作。根据 MVCC 的实现，DML 操作并不是在操作系统磁盘中将数据删除，因此数据可以通过参数vacuum_defer_cleanup_age 来调整 Dead 元组在数据库中的数量，以便恢复误操作的数据。

数据恢复方式

1.利用备份恢复

2.备份+归档

3.PITR

4.特殊恢复方式 pg_resetwal等工具

vacuum_defer_cleanup_age

指定VACUUM和HOT更新在清除死亡行版本之前，应该推迟多久（以事务数量计）。默认值是零个事务，表示死亡行版本将被尽可能快地清除，即当它们不再对任何打开的事务可见时尽快清除。在一个支持热后备服务器的主服务器上，你可能希望把这个参数设置为一个非零值。这允许后备机上的查询有更多时间来完成而不会由于先前的行清除产生冲突。但是，由于该值是用在主服务器上发生的写事务的数目衡量的，很难预测对后备机查询可用的附加时间到底是多少。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。

可用工具

pg_recovery 使用简单，默认只有待找回数据；

pg_dirtyread 使用简单，默认返回全部数据；

WalMiner 需要对 walminer 全面掌握，并做好系统预设；

pg_resetwal 需要了解的内容较多；

pg_filedump 需要单独写一些脚本或工具来配合使用；

pageinspect 难度极大。

若无任何准备，如何恢复数据？推荐以下方法：

及时设置 vacuum_defer_cleanup_age

安装 pg_recover 或者 pg_dirtyread

无法安装插件可以采用 pg_resetwal ，无需任何额外工具

上科技

安装
下载地址 https://github.com/radondb/pg_recovery


[root@pgexp1 soft_file]# 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  




[root@pgexp1 pg_recovery-master]# make PG_CONFIG=/opt/pgsql/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/pgsql/include/server -I/opt/pgsql/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/pgsql/lib    -Wl,--as-needed -Wl,-rpath,'/opt/pgsql/lib',--enable-new-dtags 
  
[root@pgexp1 pg_recovery-master]# make install PG_CONFIG=/opt/pgsql/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 -shared -o pg_recovery.so pg_recovery.o -L/opt/pgsql/lib    -Wl,--as-needed -Wl,-rpath,'/opt/pgsql/lib',--enable-new-dtags  
/usr/bin/mkdir -p '/opt/pgsql/lib'
/usr/bin/mkdir -p '/opt/pgsql/share/extension'
/usr/bin/mkdir -p '/opt/pgsql/share/extension'
/usr/bin/install -c -m 755  pg_recovery.so '/opt/pgsql/lib/pg_recovery.so'
/usr/bin/install -c -m 644 .//pg_recovery.control '/opt/pgsql/share/extension/'
/usr/bin/install -c -m 644 .//pg_recovery--1.0.sql  '/opt/pgsql/share/extension/'

上狠活

初始化数据
postgres=# create extension pg_recovery ;
CREATE EXTENSION
postgres=# create table test(id int, dp int);
CREATE TABLE
postgres=# insert into test values(1, 1);
INSERT 0 1
postgres=# insert into test values(2, 2);
INSERT 0 1


更新恢复
postgres=# update test set id=3, dp=3;
UPDATE 2
postgres=# select * from pg_recovery('test') as (id int, dp int);
 id | dp 
----+----
  1 |  1
  2 |  2
(2 rows)


postgres=# select * from test;
 id | dp 
----+----
  3 |  3
  3 |  3
(2 rows)


恢复删除
postgres=# delete from test;
DELETE 2
postgres=# select * from test;
 id | dp 
----+----
(0 rows)


postgres=# select * from pg_recovery('test') as (id int, dp int);
 id | dp 
----+----
  1 |  1
  2 |  2
  3 |  3
  3 |  3
(4 rows)


恢复回滚
postgres=# begin;
BEGIN
postgres=*# insert into test values(4, 4);
INSERT 0 1
postgres=*# rollback;
ROLLBACK
postgres=# select * from test;
 id | dp 
----+----
(0 rows)


postgres=# select * from pg_recovery('test') as (id int, dp int);
 id | dp 
----+----
  1 |  1
  2 |  2
  3 |  3
  3 |  3
  4 |  4
(5 rows)


恢复DDL操作
postgres=# alter table test drop column dp;
ALTER TABLE
postgres=# select attnum from pg_attribute, pg_class where attrelid = pg_class.oid and pg_class.relname='test' and attname ~ 'dropped';
select * from test;
select * from pg_recovery('test') as (id int, dropped_attnum_2 int); attnum 
--------
      2
(1 row)


postgres=# select * from test;
 id 
----
(0 rows)


postgres=# select * from pg_recovery('test') as (id int, dropped_attnum_2 int);
 id | dropped_attnum_2 
----+------------------
  1 |                1
  2 |                2
  3 |                3
  3 |                3
  4 |                4
(5 rows)


显示所有数据
postgres=# insert into test values(5);
INSERT 0 1
postgres=# select * from test;
 id 
----
  5
(1 row)


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

保留恢复数据

pg_recovery 从 postgresql 读取死元组。所以如果元组是真空的，pg_recovery 就不能读取表数据。

vacuum_defer_cleanup_age
保留这些交易。

支持版本

目前仅支持 PostgreSQL-12/13/14/15

文章转载自CP的PostgreSQL厨房，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL学习随笔21 利用工具恢复误删除数据1之pg_recovery

保留恢复数据

评论