PostgreSQL 可以数据找回了，MySQL还不可以吗？
PostgreSQL 可以数据找回了，MySQL还不可以吗？
AustinDatabases
发布于 2024-04-15 15:20:17
发布于 2024-04-15 15:20:17
对 MYSQL 还不可以找回，PG16已经有插件可以进行相关的功能，并进行数据找回，相对于MySQL, PostgreSQL的新功能是越来越多，最近添加了删除数据找回的功能，到底好用不好用，到底怎么回事，咱们来说说。首先咱们需要先下载pg_dirtyread的 extension， 同时需要在PG16版本上进行使用。若你此时问什么是 extension，那么你真的该好好学习，学习了。
https://github.com/df7cb/pg_dirtyread/releases/tag/2.6
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 则有会快速的方法来进行数据点额还原。如：
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=#
RE最后最后COVERING DELETED DATA FROM PO的STGRESQL TABLES
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2024-04-09，如有侵权请联系 cloudcommunity@tencent.com 删除
本文分享自 AustinDatabases 微信公众号，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体分享计划 ，欢迎热爱写作的你一起参与！
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。