postgresql data recovery after multiply rows update

Question

I am trying to retrieve data lost on wrong update query, using xlog and pg_xlogdump tool.

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

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

1) First line ( update) rel 1663/5880305/5880686 shows exactly the table I am looking for (5880305 oid of the db and 5880686 oid of the table) Also I have old backup and row with tuple 47275/8 there is the same row that was updated, with new tuple in updated version ( new tid : 293804/16 )

2) 4th line shows column1 update (11996103 is oid of that column ) , however I don't know how to find tuple 302/164 and how to retrieve 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

Before doing ANYTHING else, follow the advice here: https://wiki.postgresql.org/wiki/Corruption — Craig Ringer, Jun 19 '14 at 14:48
then look into `pg_dirtyread` and, yes, `pg_xlogdump`. If you're lucky you have a high `wal_level` and there's a full page write image in the xlogs. — Craig Ringer, Jun 19 '14 at 14:50

score 0 · Answer 1 · answered Mar 13 '15 at 12:35

0

if you archived your log files then simply go with a point in time recovery otherwise it is very complex

answered Mar 13 '15 at 12:35

Jameel Grand

2,294
16
32

postgresql data recovery after multiply rows update

1 Answers1