All geek questions in one place

Data recovery after migration after updating rows

I am trying to get the data lost due to an incorrect update request using the xlog tool and pg_xlogdump.

The query was as follows:
update table set column1 = 21 where column2> column3 (1 condition was skipped, so more rows were updated)

Example 1 of the update record shown below. It starts with 1 update line and 5 insert lines. I wonder if there is a way to find the number replaced by 21?

1) The first row (update) rel 1663/5880305/5880686 shows exactly the table I'm looking for (5880305 from db and 5880686 oid tables) In addition, I have an old backup and a row with tuple 47275/8, there is one same row that was updated with a new tuple in the updated version (new tid: 293804/16)

2) The 4th row shows the update of column1 (11996103 is this column), however I do not know how to find the tuple 302/164 and how to get the lost number

Thanks in advance.

rmgr: Heap        len (rec/tot):    163/  8391, tx:  692554090, lsn: 115/73F1D288, prev 115/73F1D240, bkp: 1000, desc: update: rel 1663/5880305/5880686; tid 47275/8 xmax 692554090 ; new tid 293804/16 xmax 0
backup bkp #0; rel 1663/5880305/5880686; fork: main; block: 47275; hole: offset: 228, length: 20

rmgr: Btree       len (rec/tot):     34/    66, tx:  692554090, lsn: 115/73F1F368, prev 115/73F1D288, bkp: 0000, desc: insert: rel 1663/5880305/11995979; tid 6587/293

rmgr: Btree       len (rec/tot):     34/    66, tx:  692554090, lsn: 115/73F1F3B0, prev 115/73F1F368, bkp: 0000, desc: insert: rel 1663/5880305/11996093; tid 6587/292

rmgr: Btree       len (rec/tot):     34/    66, tx:  692554090, lsn: 115/73F1F3F8, prev 115/73F1F3B0, bkp: 0000, desc: insert: rel 1663/5880305/11996103; tid 302/164

rmgr: Btree       len (rec/tot):     34/    66, tx:  692554090, lsn: 115/73F1F440, prev 115/73F1F3F8, bkp: 0000, desc: insert: rel 1663/5880305/11996135; tid 43502/2

rmgr: Btree       len (rec/tot):     34/    66, tx:  692554090, lsn: 115/73F1F488, prev 115/73F1F440, bkp: 0000, desc: insert: rel 1663/5880305/11996136; tid 1/2
+4
source share
1 answer

if you archived your log files just go with the time recovery point otherwise it is very difficult

0
source

Source: https://habr.com/ru/post/1545212/

More articles:


All Articles