作者：Digital Observer（施嘉伟）
Oracle ACE Pro: Database
PostgreSQL ACE Partner
11年数据库行业经验，现主要从事数据库服务工作
拥有Oracle OCM、DB2 10.1 Fundamentals、MySQL 8.0 OCP、WebLogic 12c OCA、KCP、PCTP、PCSD、PGCM、OCI、PolarDB技术专家、达梦师资认证、数据安全咨询高级等认证
ITPUB认证专家、PolarDB开源社区技术顾问、HaloDB技术顾问、TiDB社区技术布道师、青学会MOP技术社区专家顾问、国内某高校企业实践指导教师

第一部分 文档描述

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

第二部分 操作步骤

2.1 创建测试表

创建测试表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

2.2 安装pageinspect扩展

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

dbtest=# create extension pageinspect ;
CREATE EXTENSION

2.3 查询表的事务操作记录

使用扩展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)

2.4 模拟删除表数据

删除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)

2.5 关闭表级别的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);

2.6 关闭数据库重置事务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

2.7 启动数据库

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

[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 dbtest

导出表数据后重命名原表

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)