postgresql数据库中误删数据，闪回方法

简介

      在开发过程中可能会由于一些错误操作，误删了数据库。及时禁止表上的autovacuum ，使用插件pg_dirtyread，可以实现未被vacuumed的误删数据闪回。

安装插件

这个是一个第三方插件，可以选择在github摘取。

git clone https://github.com/df7cb/pg_dirtyread.git  --插件下载地址

make 
make install --构建插件

示例

创建测试数据

 create extension pg_dirtyread;  --创建插件

--创建测试数据
create table text_del(id serial,del_1 varchar(20));


--插入测试数据
insert into text_del(del_1)values('误删数据'),('剩余数据');

--误删操作
delete from text_del where id = 1;

--立刻禁用表级vacuum
 ALTER TABLE text_del SET (
    autovacuum_enabled = false, toast.autovacuum_enabled = false
  );

这里禁用了表级别的vacuum 

备注

autovacuum_enabled =false：将autovacuum_enabled选项设置为false，
表示禁用自动化清理（autovacuum）进程对该表的自动清理操作。
自动清理是PostgreSQL中的一项功能，用于定期清理和维护表数据，
以减少空间占用和提高性能。

toast.autovacuum_enabled = false：将toast.autovacuum_enabled选项设置为false，
表示禁用自动化清理进程对该表的TOAST表（用于存储超过单个表页大小的大型值）的自动清理操作。

--使用插件查看误删数据的表

 SELECT * FROM pg_dirtyread('text_del') as t(id integer, del_1 varchar(20));

 此时可以看到已误删数据

对于使用update 误修改的数据 也可以看到

update text_del  set del_1 ='误删数据（update修改）' where ID= 2;
--立刻禁用表级vacuum
 ALTER TABLE text_del SET (
    autovacuum_enabled = false, toast.autovacuum_enabled = false
  );

SELECT * 
        FROM pg_dirtyread('text_del') as t(id integer, del_1 varchar(20)) 
        where id = 2;

说明：

这里插件只能对update,delete，alter table tablename drop column col1(整列删除) 误操作的执行可以看见 ，如果是使用了truncate table 便需要使用PITR方法进行数据回滚。

插件提供了一个表示表达式，可以读取这个表在删除，修改步骤中所属的事务ID 以及当前值和过去的值。

 SELECT * FROM pg_dirtyread('text_del')
      AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,
           id integer, del_1 varchar(20));

 表中字段含义

注释

tableoid (oid): 表示数据所属的表的对象标识符。
ctid (tid): 重试过程中返回的当前行的物理位置标识符。
xmin (xid): 事务ID，表示修改行的最早的事务ID。
xmax (xid): 事务ID，表示修改行的最后一个事务ID。
cmin (cid): 修改行的最早的命令ID。
cmax (cid): 修改行的最后一个命令ID。
dead (boolean): 指示行是否已删除的标志。如果为true，表示该行已经被删除。
id (integer): 表示表中的某个整数类型字段，对应的是名为"id"的列。
del_1 (varchar(20)): 表示表中的某个文本类型字段，对应的是名为"del_1"的列。
这个表达式使用了pg_dirtyread函数，可以用于查询包括被删除的行（未被VACUUM清除）在内的实际行版本。通过使用这个表达式，可以查看某个表（“text_del”）的所有行以及它们的相关信息和属性。