PostgreSQL 可以数据找回了，MySQL还不可以吗？

  • 开发
  • 96

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, Oceanbase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，（共2150人左右 1 + 2 + 3 + 4 +5） 新人直接分配到5群,另欢迎 OpenGauss 的技术人员加入。

12f1db58eb923c71aa0d39bb20f0d51d.png

对 MYSQL 还不可以找回，PG16已经有插件可以进行相关的功能，并进行数据找回，相对于MySQL, PostgreSQL的新功能是越来越多，最近添加了删除数据找回的功能，到底好用不好用，到底怎么回事，咱们来说说。首先咱们需要先下载pg_dirtyread的 extension， 同时需要在PG16版本上进行使用。若你此时问什么是 extension，那么你真的该好好学习，学习了。

https://github.com/df7cb/pg_dirtyread/releases/tag/2.6

a88b533c30f3ebc14316b647d69c5537.png

root@pg16:~/pg_dirtyread-2.6# source /home/postgres/.bash_profile 
root@pg16:~/pg_dirtyread-2.6# ls
contrib  dirtyread_tupconvert.c  expected  Makefile                  pg_dirtyread--1.0.sql  pg_dirtyread.c        README.md  tupconvert.c.upstream
debian   dirtyread_tupconvert.h  LICENSE   pg_dirtyread--1.0--2.sql  pg_dirtyread--2.sql    pg_dirtyread.control  sql        tupconvert.h.upstream
root@pg16:~/pg_dirtyread-2.6# make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I. -I./ -I/usr/local/postgres/includes/server -I/usr/local/postgres/includes/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pg_dirtyread.o pg_dirtyread.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I. -I./ -I/usr/local/postgres/includes/server -I/usr/local/postgres/includes/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o dirtyread_tupconvert.o dirtyread_tupconvert.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -shared -o pg_dirtyread.so pg_dirtyread.o dirtyread_tupconvert.o -L/usr/local/postgres/libs    -Wl,--as-needed -Wl,-rpath,'/usr/local/postgres/libs',--enable-new-dtags -fvisibility=hidden 
root@pg16:~/pg_dirtyread-2.6# make install
/usr/bin/mkdir -p '/usr/local/postgres/libs'
/usr/bin/mkdir -p '/pgdata/postgresql/extension'
/usr/bin/mkdir -p '/pgdata/postgresql/extension'
/usr/bin/install -c -m 755  pg_dirtyread.so '/usr/local/postgres/libs/pg_dirtyread.so'
/usr/bin/install -c -m 644 .//pg_dirtyread.control '/pgdata/postgresql/extension/'
/usr/bin/install -c -m 644 .//pg_dirtyread--1.0.sql .//pg_dirtyread--1.0--2.sql .//pg_dirtyread--2.sql  '/pgdata/postgresql/extension/'
root@pg16:~/pg_dirtyread-2.6#

我们目前需要通过编译的方式将pg_dirtyread打入到数据库中，进行使用，如果此部分有问题则说明当前系统中的postgresql相关的变量不对导致的，需要进行调整后，在进行相关的工作。

安装好插件后，我们直接进入到数据库里面将pg_dirtyread打入到数据库中，并且输入数据，在进行数据的更新，然后通过pg_dirtyread函数将我们所有的版本的数据都进行查看，发现我们看不见曾经原来的数据的原有的模样。

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# 
test=# 
test=# 
test=# 
test=# create extension pg_dirtyread;
CREATE EXTENSION
test=# 
test=# 
test=# 
test=# create table test_data (id serial primary key, name varchar(20));
CREATE TABLE
test=# insert into test_data (name) values ('sys');

insert into test_data (name) values ('sya');

insert into test_data (name) values ('syb');

insert into test_data (name) values ('sye');
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
test=# 
test=# 
test=# 
test=# select * from pg_dirtyread('test_data') test_data(id int,name varchar(20));
 id | name 
----+------
  5 | sys
  6 | sya
  7 | syb
  8 | sye
(4 rows)

test=# 
test=# 
test=# update test_data set name = '1' where id > 5;
UPDATE 3
test=# 
test=# 
test=# select * from pg_dirtyread('test_data') test_data(id int,name varchar(20));
 id | name 
----+------
  5 | sys
  6 | sya
  7 | syb
  8 | sye
  6 | 1
  7 | 1
  8 | 1
(7 rows)

select * from pg_dirtyread('test_data') test_data(id int,name varchar(20));

上图中我们通过pg_dirtyread函数，对于指定的表进行了修改数据的找回的工作。

test=# select * from test_data;
 id | name 
----+------
  5 | sys
  6 | 1
  7 | 1
  8 | 1
(4 rows)

test=# update test_data set name = 'sya' where id =6;
UPDATE 1
test=# update test_data set name = 'sya' where id =7;
UPDATE 1
test=# update test_data set name = 'sye' where id =8;
UPDATE 1
test=# 
test=# select * from test_data;
 id | name 
----+------
  5 | sys
  6 | sya
  7 | sya
  8 | sye
(4 rows)

test=# select * from pg_dirtyread('test_data') test_data(id int,name varchar(20));
 id | name 
----+------
  5 | sys
  6 | sya
  7 | syb
  8 | sye
  6 | 1
  7 | 1
  8 | 1
  6 | sya
  7 | sya
  8 | sye
(10 rows)

通过主键和表的自然顺序我们可以直接的将修改错误的数据的版本和修改的历史进行一个比对我们可以很快速的写出UPDATE 还原的语句，直接将数据进行还原。

以上是UPDATE 的还原方法，如果是DELETE 则有会快速的方法来进行数据点额还原。如：

7a45343bef632a7de32b7d4ac1f8c575.png

test=# insert into test_data select * from pg_dirtyread('test_data') test_data(id int,name varchar(20)) where id = 6 order by  id desc limit 1;
INSERT 0 1
test=# insert into test_data select * from pg_dirtyread('test_data') test_data(id int,name varchar(20)) where id = 7 order by  id desc limit 1;
INSERT 0 1
test=# insert into test_data select * from pg_dirtyread('test_data') test_data(id int,name varchar(20)) where id = 8 order by  id desc limit 1;
INSERT 0 1
test=# select * from test_data;
 id | name 
----+------
  5 | sys
  6 | sya
  7 | syb
  8 | sye
(4 rows)

test=# select * from pg_dirtyread('test_data') test_data(xmin xid,id int,name varchar(20));
 xmin | id | name 
------+----+------
  779 |  5 | sys
  780 |  6 | sya
  781 |  7 | syb
  782 |  8 | sye
  783 |  6 | 1
  783 |  7 | 1
  783 |  8 | 1
  784 |  6 | sya
  785 |  7 | sya
  786 |  8 | sye
  788 |  5 | sys
  789 |  8 | 1
  789 |  8 | sye
  790 |  8 | sye
  790 |  8 | 1
  791 |  6 | sya
  792 |  7 | syb
  793 |  8 | sye
(18 rows)

上图可以看到我们通过pg_dirtyread 函数通过来读取到所有的数据并通过XMIN查看数据的变动的历史，当然也可以在添加XMAX，懂得PG原理的可以很容易的找到数据变动的历史和数据是被UPDATE OR  DELETE处理的。

实际上这个插件仅仅是利用了PG的MVCC UNDO 存储在数据表的原理，将数据库中不进行展示的数据，进行了展示，联合PG的DBA 懂得原理而进行数据还原的一个方案。

但这个方案也有一个问题，就是怕 vacuum and autovacuum 对你刚刚删除或UPDATE的表进行处理，如果他们进行了处理，则这个插件的功能就无法实现了。如具体的操作可以看下面的注解，在表进行vacuum操作后，dead tuple被清理了，那么这个插件也会看不见已经被清理的行，数据的找回功能也就失效了。

test=# select * from pg_dirtyread('test_data') test_data(xmin xid,id int,name varchar(20));
 xmin | id | name 
------+----+------
  779 |  5 | sys
  780 |  6 | sya
  781 |  7 | syb
  782 |  8 | sye
  783 |  6 | 1
  783 |  7 | 1
  783 |  8 | 1
  784 |  6 | sya
  785 |  7 | sya
  786 |  8 | sye
  788 |  5 | sys
  789 |  8 | 1
  789 |  8 | sye
  790 |  8 | sye
  790 |  8 | 1
  791 |  6 | sya
  792 |  7 | syb
  793 |  8 | sye
(18 rows)

test=# vacuum test_data;
VACUUM
test=# select * from pg_dirtyread('test_data') test_data(xmin xid,id int,name varchar(20));
 xmin | id | name 
------+----+------
  788 |  5 | sys
  791 |  6 | sya
  792 |  7 | syb
  793 |  8 | sye
(4 rows)

test=# select * from test_data;
 id | name 
----+------
  5 | sys
  6 | sya
  7 | syb
  8 | sye
(4 rows)

test=#

9811cce7ed438ad2ad461b8f06f4f13d.png

RE最后最后COVERING DELETED DATA FROM PO的STGRESQL TABLES

阅读全部

相关推荐

  2. AIGC 架构：RAG (retrieval augumented generation) 应用可以使用 PostgreSQL 作为向量数据库组件吗？

    2024-04-12 11:40:02       62 阅读

  8. 抖店被清退身份证还能入驻吗？被清退保证金可以退吗？

    2024-04-12 11:40:02       99 阅读

  9. 光伏发电在什么情况下不可以使用？安装后可以拆除吗？

    2024-04-12 11:40:02       99 阅读

最近更新

  3. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-04-12 11:40:02       94 阅读

  4. Could not load dynamic library ‘cudart64_100.dll‘

    2024-04-12 11:40:02       100 阅读

  9. 在Django里面运行非项目文件

    2024-04-12 11:40:02       82 阅读

  19. Python语言-面向对象

    2024-04-12 11:40:02       91 阅读

  20. 如何分清楚常见的 Git 分支管理策略Git Flow、GitHub Flow 和 GitLab Flow

    2024-04-12 11:40:02       85 阅读

热门阅读

  1. 0基础刷图论最短路 1（从ATcoder 0分到1800分）

    2024-04-12 11:40:02       37 阅读

  6. 关于conda安装pytorch gpu总是会自动变成cpu版本

    2024-04-12 11:40:02       41 阅读

  7. 时间戳与时间锁区别与联系

    2024-04-12 11:40:02       48 阅读

  11. 【数据结构】2.包装类&简单认识泛型

    2024-04-12 11:40:02       39 阅读

  13. 【备忘】npm yarn pnpm 命令对比

    2024-04-12 11:40:02       43 阅读

  14. Spring Boot 经典面试题（三）

    2024-04-12 11:40:02       36 阅读
  15. 4.11Qt

    4.11Qt

    2024-04-12 11:40:02      42 阅读

  16. 【浮点数加法】

    2024-04-12 11:40:02       46 阅读

  17. Circuits--Sequential--More circuits

    2024-04-12 11:40:02       41 阅读

  19. unity之URP多相机和URP多通道渲染

    2024-04-12 11:40:02       38 阅读

  23. 蓝桥杯 总结经典基础题型

    2024-04-12 11:40:02       29 阅读

  26. 基于springboot的大学生就业招聘系统源码数据库

    2024-04-12 11:40:02       34 阅读

  27. 【AcWing】蓝桥杯集训每日一题Day25|最大公约数|算数基本定理|4199.公约数(C++)

    2024-04-12 11:40:02       36 阅读

  30. Android 系统编译 and 应用裁剪

    2024-04-12 11:40:02       42 阅读