Part One Document Description
This document applies to reset and restore when data table data is misoperated by DML type statements. It is necessary to satisfy that the database or data table is not vacuum or vacuum full.
Part 2 Operation steps
2.1 Create a test table
Create test table 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 Install pageinspect extension
Install pageinspect extension to get tuple record information
dbtest=# create extension pageinspect ; CREATE EXTENSION
2.3 Transaction operation records of the query table
Use the function in extended pageinspect to query the transaction operation record of the current table
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 Simulate deletion table data
Delete data with id 5 and check the transaction operation record of the table again. You can see that the record with id 5 at the red mark has its delete transaction number of 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 Close the table level autovacuum
Before parsing deleted data, first check whether the table is vacuumed after the deleted time point (including manual vacuum and 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
If it is not vacuumed, close the table level autovacuum and start the parsing step
dbtest=# alter table novels set (autovacuum_enabled = off);
2.6 Close the database and reset the transaction id
Close the database service and use the pg_resetwal tool to reset the transaction id so that the next transaction id starts at 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 Start the database
Start the database, check whether the data is retrieved, and rebuild the table to export and import data
[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)
You can use pg_dump/pg_restore to rebuild the table
[postgres@VM-4-13-centos ~]$ pg_dump -Fc -U postgres -t novels -f -d dbtest
Rename the original table after exporting the table data
dbtest=# alter table novels rename to novelsbak;
pg_restore recovery data
[postgres@VM-4-13-centos ~]$ pg_restore -d dbtest -c --if-exists
Finally, verify data integrity
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)
This is the article about the recovery method of PostgreSQL data DML error operation. For more related PostgreSQL DML error operation recovery content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!