「点击蓝字，关注我吧」

作者：Digital Observer（施嘉伟）

Oracle ACE Associate: Database

PostgreSQL ACE Partner

11年数据库行业经验，现主要从事数据库服务工作

拥有Oracle OCM、DB2 10.1 Fundamentals、MySQL 8.0 OCP、WebLogic 12c OCA、KCP、PCTP、PCSD、OCI、PolarDB技术专家、达梦师资认证、数据安全咨询高级等认证

ITPUB认证专家、PolarDB开源社区技术顾问

国内某高校企业实践指导教师

公众号：Digital Observer；CSDN：施嘉伟；ITPUB：sjw1933；PGFans：施嘉伟。

误操作

PostgreSQL中DML误操作通常是由于用户执行了错误的UPDATE或DELETE命令，没有正确使用事务控制或者没有充分测试操作导致的数据丢失或损坏。

描述

本文档适用数据表数据被DML类型的语句误操作情况下的重置恢复，需要满足数据库或数据表未被vacuum或者vacuum full。

操作步骤

01

创建测试表

创建测试表novels

dbtest=# create table novels (name varchar(200), id int);
CREATE TABLE
dbtest=# insert into novels select md5(random()::text),generate_series(1,10);
INSERT 0 10

02

安装pageinspect扩展

安装pageinspect扩展获取元组记录信息

dbtest=# create extension pageinspect ;
CREATE EXTENSION

03

查询表的事务操作记录

使用扩展pageinspect中的函数查询当前表的事务操作记录

dbtest=# select * from heap_page_items(get_raw_page('novels','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 |   8128 |        1 |     64 |    651 |      0 |        0 | (0,1)  |           2 |       2050 |     24 |        |       | \x43
613731663139343239303330323262313732613039383366633066396562663600000001000000
  2 |   8064 |        1 |     64 |    651 |      0 |        0 | (0,2)  |           2 |       2050 |     24 |        |       | \x43
386633363838333134386434323539306265613033636434386536393232383400000002000000
  3 |   8000 |        1 |     64 |    651 |      0 |        0 | (0,3)  |           2 |       2050 |     24 |        |       | \x43
363535323364663966616531663935666538383632646239383166633962623600000003000000
  4 |   7936 |        1 |     64 |    651 |      0 |        0 | (0,4)  |           2 |       2050 |     24 |        |       | \x43
333763383035386564303434316133366438656133333764613837626362616600000004000000
  5 |   7872 |        1 |     64 |    651 |      0 |        0 | (0,5)  |           2 |       2050 |     24 |        |       | \x43
663833356234353939623566336661336662376630323234363133663139663800000005000000
  6 |   7808 |        1 |     64 |    651 |      0 |        0 | (0,6)  |           2 |       2050 |     24 |        |       | \x43
626336623539373031316565396437333236363965313937323265373736333100000006000000
  7 |   7744 |        1 |     64 |    651 |      0 |        0 | (0,7)  |           2 |       2050 |     24 |        |       | \x43
646633323365396535356166376538386632376139666431643739303736356600000007000000
  8 |   7680 |        1 |     64 |    651 |      0 |        0 | (0,8)  |           2 |       2050 |     24 |        |       | \x43
393865616338366430323137363466626462616539333831636436646137333200000008000000
  9 |   7616 |        1 |     64 |    651 |      0 |        0 | (0,9)  |           2 |       2050 |     24 |        |       | \x43
333766383764653134633235666664643563663832313836643064326138653600000009000000
 10 |   7552 |        1 |     64 |    651 |      0 |        0 | (0,10) |           2 |       2050 |     24 |        |       | \x43
32333636613934363930393830316562376564366537376630366231626362610000000a000000
(10 rows)

04

模拟删除表数据

删除id为5的数据，再次查看该表的事务操作记录，可以看到标红处id为5的记录其删除事务号为652

dbtest=# delete from novels where id = 5;
DELETE 1
dbtest=# select * from heap_page_items(get_raw_page('novels','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 |   8128 |        1 |     64 |    651 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | \x43
613731663139343239303330323262313732613039383366633066396562663600000001000000
  2 |   8064 |        1 |     64 |    651 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x43
386633363838333134386434323539306265613033636434386536393232383400000002000000
  3 |   8000 |        1 |     64 |    651 |      0 |        0 | (0,3)  |           2 |       2306 |     24 |        |       | \x43
363535323364663966616531663935666538383632646239383166633962623600000003000000
  4 |   7936 |        1 |     64 |    651 |      0 |        0 | (0,4)  |           2 |       2306 |     24 |        |       | \x43
333763383035386564303434316133366438656133333764613837626362616600000004000000
  5 |   7872 |        1 |     64 |    651 |    652 |        0 | (0,5)  |        8194 |        258 |     24 |        |       | \x43
663833356234353939623566336661336662376630323234363133663139663800000005000000
  6 |   7808 |        1 |     64 |    651 |      0 |        0 | (0,6)  |           2 |       2306 |     24 |        |       | \x43
626336623539373031316565396437333236363965313937323265373736333100000006000000
  7 |   7744 |        1 |     64 |    651 |      0 |        0 | (0,7)  |           2 |       2306 |     24 |        |       | \x43
646633323365396535356166376538386632376139666431643739303736356600000007000000
  8 |   7680 |        1 |     64 |    651 |      0 |        0 | (0,8)  |           2 |       2306 |     24 |        |       | \x43
393865616338366430323137363466626462616539333831636436646137333200000008000000
  9 |   7616 |        1 |     64 |    651 |      0 |        0 | (0,9)  |           2 |       2306 |     24 |        |       | \x43
333766383764653134633235666664643563663832313836643064326138653600000009000000
 10 |   7552 |        1 |     64 |    651 |      0 |        0 | (0,10) |           2 |       2306 |     24 |        |       | \x43
32333636613934363930393830316562376564366537376630366231626362610000000a000000
(10 rows)

05

关闭表级别的autovacuum

解析被删除的数据之前首先查询表在删除时间点之后是否被vacuum（包括手动vacuum和autovacuum）

dbtest=# \x
Expanded display is on.
dbtest=# select * from pg_stat_all_tables where relname = 'novels';
-[ RECORD 1 ]-------+------------------------------
relid               | 24783
schemaname          | public
relname             | novels
seq_scan            | 13
seq_tup_read        | 44
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 28
n_tup_upd           | 0
n_tup_del           | 22
n_tup_hot_upd       | 0
n_live_tup          | 6
n_dead_tup          | 18
n_mod_since_analyze | 50
n_ins_since_vacuum  | 24
last_vacuum         | 2022-02-09 11:27:30.501748+08
last_autovacuum     | 
last_analyze        | 
last_autoanalyze    | 
vacuum_count        | 1
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

如果没有被vacuum，则关闭表级别的autovacuum并开始解析步骤

dbtest=# alter table novels set (autovacuum_enabled = off);

06

关闭数据库重置事务id

关闭数据库服务，使用pg_resetwal工具重置事务id，使得下一个事务id从652开始

[postgres@VM-4-13-centos ~]$ pg_ctl stop
[postgres@VM-4-13-centos data]$ pg_resetwal -x 652 -D $PGDATA
Write-ahead log reset

07

启动数据库

启动数据库，查看数据是否找回，并重建表导出导入数据

[postgres@VM-4-13-centos ~]$ pg_ctl start
waiting for server to start....2022-02-09 14:56:51.938 CST [27058] LOG:  redirecting log output to logging collector process
2022-02-09 14:56:51.938 CST [27058] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@VM-4-13-centos ~]$ psql -ddbtest
psql (13.4)
Type "help" for help.

dbtest=# 
dbtest=# select xmin,xmax,id from novels ;
 xmin | xmax | id 
------+------+----
  651 |    0 |  1
  651 |    0 |  2
  651 |    0 |  3
  651 |    0 |  4
  651 |  652 |  5
  651 |    0 |  6
  651 |    0 |  7
  651 |    0 |  8
  651 |    0 |  9
  651 |    0 | 10
(10 rows)

可以使用pg_dump/pg_restore重建表

[postgres@VM-4-13-centos ~]$ pg_dump -Fc -U postgres -t novels -f novels.sql -d dbtes

导出表数据后重命名原表

dbtest=# alter table novels rename to novelsbak;

pg_restore恢复数据

[postgres@VM-4-13-centos ~]$ pg_restore -d dbtest novels.sql -c --if-exists

最后，验证数据完整性

dbtest=# select * from novels ;
               name               | id 
----------------------------------+----
 a63e2f63c5dbec9065a788f6e774b9ce |  1
 092465fb1c7b58adea3b35b4a5de5fd9 |  2
 1559907b48d5e8efaeddbdace55efad2 |  3
 3f47e858647d8ef838fbaa7e87b3bc07 |  4
 430f91a034857bf996f51cbe1dc6bef3 |  5
 56d65c982438ed56724e3dae4c5e7933 |  6
 4dbecc8fb3d3a8acca631ed359685011 |  7
 98023e6140984d125cc19ee4ba33608f |  8
 f2570c845be93fdf36e9d7c3f4d34ccf |  9
 7d9d3a65770df1573ec58c349d3216f0 | 10
(10 rows)