PostgreSQL误删数据怎么破？__PostgreSQL并未有闪回和数据误删除保护的功能，但是在一些场景下也可以实现。_

PG中文社区 /

mdi-home

首页

社区新闻

中文文档

加入ACE

mdi-home

首页

mdi-chat-processing

社区新闻

mdi-book-open-variant

中文文档

mdi-account-multiple-check

加入ACE

mdi-file-multiple-outline

相关资料

mdi-blank

mdi-exit-to-app

退出账号

PostgreSQL误删数据怎么破？

原作者：张连壮　　创作时间：2019-03-28 11:33:57+08　　

redraiment 发布于2019-03-28 11:33:57　　

评论: 1　　

顶: 433　

踩: 453　

## 作者简介

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

## 背景

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

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

## 开始准备数据

```
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插件

```sql
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)
```

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

![CENTER_PostgreSQL_Community](/images/news/2016/pg_bot_banner.jpg)

评论:1　　

顶: 433　

踩: 453　

请在登录后发表评论，否则无法保存。

1# __ xiaowu 回答于 2024-04-23 14:50:36+08

书香气质女孩名字:https://www.nanss.com/shenghuo/5206.html 迎财神文案:https://www.nanss.com/wenan/5888.html 经典短句子人生感悟:https://www.nanss.com/yulu/4841.html 失望至极的文案简短:https://www.nanss.com/wenan/5890.html 偷偷地造句子一年级:https://www.nanss.com/xuexi/5742.html 合家团圆:https://www.nanss.com/yuedu/5547.html 主打一成语:https://www.nanss.com/shenghuo/5425.html 吊唁短信:https://www.nanss.com/shenghuo/5342.html 最红的网名:https://www.nanss.com/mingcheng/5683.html 节哀短信:https://www.nanss.com/yulu/5450.html 表达爱意的网名:https://www.nanss.com/mingcheng/5651.html 深夜喝酒发朋友圈说说:https://www.nanss.com/wenan/5722.html 带有青字最好听的网名:https://www.nanss.com/mingcheng/5846.html 当兵祝福语短句:https://www.nanss.com/yulu/5475.html 企业价值观口号大全:https://www.nanss.com/gongzuo/5935.html 清纯好听有雪字的网名:https://www.nanss.com/mingcheng/5778.html 谭嗣同名言:https://www.nanss.com/xuexi/4211.html 四字静心禅语:https://www.nanss.com/yulu/5979.html 男士网名稳重内涵:https://www.nanss.com/mingcheng/5993.html 情侣签名一对:https://www.nanss.com/wenan/5010.html 地球自述作文500字六年级变形记:https://www.nanss.com/xuexi/5257.html 追思亲人的句子:https://www.nanss.com/yulu/4584.html 女生适合用的标签:https://www.nanss.com/wenan/6002.html 活动场地布置:https://www.nanss.com/gongzuo/4615.html 倩女幽魂爆率高的名字:https://www.nanss.com/mingcheng/5641.html 坏脾气留给最亲近的人的说说:https://www.nanss.com/wenan/5654.html 恶有恶报:https://www.nanss.com/yuedu/5521.html 关于月亮的名字超仙:https://www.nanss.com/mingcheng/5827.html 二年级形容生气的词语:https://www.nanss.com/xuexi/5343.html 抖音上必火童装文案:https://www.nanss.com/wenan/6000.html