软件开发架构师

PostgreSQL 误删数据怎么办?-InfoQ

运维 24 2019-09-02 23:10

本文经授权转载自 PostgreSQL 中文社区。

PostgreSQL 并未有闪回和数据误删除保护的功能,但是在一些场景下也可以实现。

此方法仅仅针对 DML 操作有效,DDL 操作(drop/truncate 等会将数据页面删除)无法找回数据。由于 PG 是多版本实现机制,因此数据仍然都在,只是不可见而已,vacuum_defer_cleanup_age 可以防止最近这些事务的元祖被删除,即保留这些事务操作的元组,可以闪回到这些操作的任意时间点

开始准备数据

复制代码
test=# select pg_current_wal_lsn();
pg_current_wal_lsn
0/ 2003B58( 1 row)
test=# create table lzzhang(id int);
CREATE TABLE
test=# insert into lzzhang values( 1);
INSERT 0 1
test=# insert into lzzhang values( 2);
INSERT 0 1
test=# insert into lzzhang values( 3);
INSERT 0 1
test=# insert into lzzhang values( 4);
INSERT 0 1
test=# insert into lzzhang values( 5);
INSERT 0 1
test=# insert into lzzhang values( 6);
INSERT 0 1
test=# select xmin, xmax, cmin, cmax, * from lzzhang;xmin | xmax | cmin | cmax | id——+——+——+——+—-
588 | 0 | 0 | 0 | 1
589 | 0 | 0 | 0 | 2
590 | 0 | 0 | 0 | 3
591 | 0 | 0 | 0 | 4
592 | 0 | 0 | 0 | 5
593 | 0 | 0 | 0 | 6
( 6 rows)

回滚 Insert

分析 redo,根据时间找到自己的错误操作,并找到对应的事务号

复制代码
./pg_waldump -b -s 0/ 2003B58 -p dj
rmgr: Transaction len (rec/tot): 34/ 34, tx: 589, lsn: 0/ 0201A660, prev 0/ 0201A620, desc: COMMIT 2019 -03 -26 10: 55: 05.685536 CST
rmgr: Heap len (rec/tot): 59/ 59, tx: 590, lsn: 0/ 0201A688, prev 0/ 0201A660, desc: INSERT off 3
blkref # 0: rel 1663/ 16392/ 16393 fork main blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 590, lsn: 0/ 0201A6C8, prev 0/ 0201A688, desc: COMMIT 2019 -03 -26 10: 55: 07.749260 CST
rmgr: Heap len (rec/tot): 59/ 59, tx: 591, lsn: 0/ 0201A6F0, prev 0/ 0201A6C8, desc: INSERT off 4
blkref # 0: rel 1663/ 16392/ 16393 fork main blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 591, lsn: 0/ 0201A730, prev 0/ 0201A6F0, desc: COMMIT 2019 -03 -26 10: 55: 09.893856 CST
rmgr: Heap len (rec/tot): 59/ 59, tx: 592, lsn: 0/ 0201A758, prev 0/ 0201A730, desc: INSERT off 5
blkref # 0: rel 1663/ 16392/ 16393 fork main blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 592, lsn: 0/ 0201A798, prev 0/ 0201A758, desc: COMMIT 2019 -03 -26 10: 55: 11.917570 CST
rmgr: Heap len (rec/tot): 59/ 59, tx: 593, lsn: 0/ 0201A7C0, prev 0/ 0201A798, desc: INSERT off 6

此示例中,我们回滚 5 和 6 的数据。根据 wal 信息找到事务号为 592。

复制代码
关闭数据库
[lzzhang@lzzhang-pc bin]$ ./pg_resetwal -D dj -x 592
启动数据库

查询

复制代码
test=# select xmin, xmax, cmin, cmax, * from lzzhang;
xmin | xmax | cmin | cmax | id
------+------+------+------+----
588 | 0 | 0 | 0 | 1
589 | 0 | 0 | 0 | 2
590 | 0 | 0 | 0 | 3
591 | 0 | 0 | 0 | 4

5/6 的数据已经没有了。增长事务号,看看情况!!!

复制代码
test=# select * from txid_current();
txid_current
--------------
592
( 1 row)
复制代码
test=# select * from txid_current();txid_current
--------------
593
( 1 row)
复制代码
test=# select * from txid_current();
txid_current
--------------
594
复制代码
test=# select xmin, xmax, cmin, cmax, * from lzzhang;
xmin | xmax | cmin | cmax | id
------+------+------+------+----
588 | 0 | 0 | 0 | 1
589 | 0 | 0 | 0 | 2
590 | 0 | 0 | 0 | 3
591 | 0 | 0 | 0 | 4
592 | 0 | 0 | 0 | 5
593 | 0 | 0 | 0 | 6

由于 5/6 仍然在数据库中,所以 5/6 又可见了。

回滚 delete

删除数据

复制代码
test=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/ 3000190
( 1 row)
复制代码
test=# delete from lzzhang where id = 5 or id = 6;
DELETE 2

找到事物号 (595)

复制代码
rmgr: Heap len (rec/tot): 59/ 299, tx: 595, lsn: 0/ 030001B8, prev 0/ 03000180, desc: DELETE off 5 KEYS_UPDATED , blkref # 0: rel 1663/ 16392/ 16393 blk 0 FPW
复制代码
rmgr: Heap len (rec/tot): 54/ 54, tx: 595, lsn: 0/ 030002E8, prev 0/ 030001B8, desc: DELETE off 6 KEYS_UPDATED , blkref # 0: rel 1663/ 16392/ 16393 blk 0
复制代码
rmgr: Transaction len (rec/tot): 34/ 34, tx: 595, lsn: 0/ 03000320, prev 0/ 030002E8, desc: COMMIT 2019 -03 -26 11: 00: 23.410557 CST

回滚数据

复制代码
关闭数据库
[lzzhang@lzzhang-pc bin]$ ./pg_resetwal -D dj -x 595
启动数据库

查看数据

复制代码
test=# select xmin, xmax, cmin, cmax, * from lzzhang;
xmin | xmax | cmin | cmax | id
------+------+------+------+----
588 | 0 | 0 | 0 | 1
589 | 0 | 0 | 0 | 2
590 | 0 | 0 | 0 | 3
591 | 0 | 0 | 0 | 4
592 | 595 | 0 | 0 | 5
593 | 595 | 0 | 0 | 6

提升事务号,5/6 又被删除

复制代码
test=# select * from txid_current();
txid_current
--------------
595
( 1 row)
复制代码
test=# select * from txid_current();
txid_current
--------------
596
( 1 row)
复制代码
test=# select xmin, xmax, cmin, cmax, * from lzzhang;
xmin | xmax | cmin | cmax | id
------+------+------+------+----
588 | 0 | 0 | 0 | 1
589 | 0 | 0 | 0 | 2
590 | 0 | 0 | 0 | 3
591 | 0 | 0 | 0 | 4
( 4 rows)

回滚 update

回滚 update 的操作类似,就不在讲述。

回滚 drop table

回滚后表可见,但是数据已经没有了 test=# select * from zz;ERROR: could not open file “base/16392/16396”: 没有那个文件或目录根据你的需要回滚 / 闪回指定操作,然后使用 txid_current() 提升事物号,并不影响数据库整体的可用性。

确定数据仍然存在

安装 pageinspect 插件

create extension pageinspect

检查数据是否存在

查看目前表中的内容

复制代码
test=# select xmin, xmax, cmin, cmax, * from lzzhang;
xmin | xmax | cmin | cmax | id
——+——+——+——+—–
588 | 0 | 0 | 0 | 1
589 | 0 | 0 | 0 | 2
590 | 0 | 0 | 0 | 3
597 | 0 | 0 | 0 | 100

查看表的 page 内容(无效 / 不可见的元组都可以查看到)

复制代码
test= # select * from heap_page_items(get_raw_page(‘lzzhang’, ‘main’, 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
—-+——–+———-+——–+——–+——–+———-+——–+————-+————+——–+——–+——-+————
1 | 8160 | 1 | 28 | 588 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 | 589 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x02000000
3 | 8096 | 1 | 28 | 590 | 0 | 0 | (0,3) | 1 | 2304 | 24 | | | \x03000000
4 | 8064 | 1 | 28 | 591 | 597 | 0 | (0,7) | 16385 | 1280 | 24 | | | \x04000000
5 | 8032 | 1 | 28 | 592 | 595 | 0 | (0,5) | 8193 | 1280 | 24 | | | \x05000000
6 | 8000 | 1 | 28 | 593 | 595 | 0 | (0,6) | 8193 | 1280 | 24 | | | \x06000000
7 | 7968 | 1 | 28 | 597 | 0 | 0 | (0,7) | 32769 | 10496 | 24 | | | \x64000000

vacuum 后查看 page 数据 vacuum lzzhang;

复制代码
test= # select * from heap_page_items(get_raw_page(‘lzzhang’, ‘main’, 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
—-+——–+———-+——–+——–+——–+———-+——–+————-+————+——–+——–+——-+————
1 | 8160 | 1 | 28 | 588 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 | 589 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x02000000
3 | 8096 | 1 | 28 | 590 | 0 | 0 | (0,3) | 1 | 2304 | 24 | | | \x03000000
4 | 7 | 2 | 0 | | | | | | | | | |
5 | 0 | 0 | 0 | | | | | | | | | |
6 | 0 | 0 | 0 | | | | | | | | | |
7 | 8064 | 1 | 28 | 597 | 0 | 0 | (0,7) | 32769 | 10496 | 24 | | | \x64000000
(7 rows)
{1}

4/5/6 已经被 vacuum 掉, 通过 waldump 可以找到对应的事务号的元祖已经不在,所有无法找回。

作者简介:

张连壮,多年 PostgreSQL 数据库内核研发经验,高可用 / 数据复制方面经验较为丰富,目前主要从事分布式数据库 Citus 相关工作,CitusDB 中国【站主】专注于 Citus 技术分享的全信息平台。

原文链接:

https://mp.weixin.qq.com/s/FbA8haWVOwRcw4CKNP0Vig

文章评论