Re: undo update

I have installed xlogviewer and it gives me data like that:

[cur:0/5770E87C, xid:355075, rmid:10(Heap), len:88/116, prev:0/5770E840]
> update: s/d/r:1663/90693/107093 block 1 off 36 to block 107 off 30
> [cur:0/5770E8F0, xid:355075, rmid:11(Btree), len:34/62, prev:0/5770E87C]
> insert_leaf: s/d/r:1663/90693/107099 tid 20/101
> [cur:0/5770E930, xid:355075, rmid:11(Btree), len:38/66, prev:0/5770E8F0]
> insert_leaf: s/d/r:1663/90693/107100 tid 42/146
> [cur:0/5770E974, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770E930]
> insert_leaf: s/d/r:1663/90693/107101 tid 21/97
> [cur:0/5770E9B0, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770E974]
> insert_leaf: s/d/r:1663/90693/107102 tid 28/7
> [cur:0/5770E9EC, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770E9B0]
> insert_leaf: s/d/r:1663/90693/107103 tid 33/2
> [cur:0/5770EA28, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770E9EC]
> insert_leaf: s/d/r:1663/90693/107104 tid 18/232
> [cur:0/5770EA64, xid:355075, rmid:11(Btree), len:54/82, prev:0/5770EA28]
> insert_leaf: s/d/r:1663/90693/107105 tid 46/109
> [cur:0/5770EAB8, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770EA64]
> insert_leaf: s/d/r:1663/90693/107106 tid 17/99
> [cur:0/5770EAF4, xid:355075, rmid:10(Heap), len:84/112, prev:0/5770EAB8]
> update: s/d/r:1663/90693/107093 block 1 off 37 to block 107 off 31
> [cur:0/5770EB64, xid:355075, rmid:11(Btree), len:34/62, prev:0/5770EAF4]
> insert_leaf: s/d/r:1663/90693/107099 tid 20/143
> [cur:0/5770EBA4, xid:355075, rmid:11(Btree), len:34/62, prev:0/5770EB64]
> insert_leaf: s/d/r:1663/90693/107100 tid 30/80
> [cur:0/5770EBE4, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770EBA4]
> insert_leaf: s/d/r:1663/90693/107101 tid 21/132
> [cur:0/5770EC20, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770EBE4]
> insert_leaf: s/d/r:1663/90693/107102 tid 28/7
> [cur:0/5770EC5C, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770EC20]
> insert_leaf: s/d/r:1663/90693/107103 tid 33/2
> [cur:0/5770EC98, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770EC5C]
> insert_leaf: s/d/r:1663/90693/107104 tid 18/232
> [cur:0/5770ECD4, xid:355075, rmid:11(Btree), len:50/78, prev:0/5770EC98]
> insert_leaf: s/d/r:1663/90693/107105 tid 40/100
> [cur:0/5770ED24, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770ECD4]
> insert_leaf: s/d/r:1663/90693/107106 tid 30/137
> [cur:0/5770ED60, xid:355075, rmid:10(Heap), len:84/112, prev:0/5770ED24]
> update: s/d/r:1663/90693/107093 block 1 off 38 to block 107 off 32
> [cur:0/5770EDD0, xid:355075, rmid:11(Btree), len:34/62, prev:0/5770ED60]
> insert_leaf: s/d/r:1663/90693/107099 tid 20/187
> [cur:0/5770EE10, xid:355075, rmid:11(Btree), len:34/62, prev:0/5770EDD0]
> insert_leaf: s/d/r:1663/90693/107100 tid 31/43
> [cur:0/5770EE50, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770EE10]
> insert_leaf: s/d/r:1663/90693/107101 tid 21/152
> [cur:0/5770EE8C, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770EE50]
> insert_leaf: s/d/r:1663/90693/107102 tid 28/7
> [cur:0/5770EEC8, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770EE8C]
> insert_leaf: s/d/r:1663/90693/107103 tid 33/2
> [cur:0/5770EF04, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770EEC8]
> insert_leaf: s/d/r:1663/90693/107104 tid 18/232
> [cur:0/5770EF40, xid:355075, rmid:11(Btree), len:50/78, prev:0/5770EF04]
> insert_leaf: s/d/r:1663/90693/107105 tid 56/107
> [cur:0/5770EF90, xid:355075, rmid:11(Btree), len:30/58, prev:0/5770EF40]
> insert_leaf: s/d/r:1663/90693/107106 tid 18/28

Is there any way to use this data for undo?

On Thu, Mar 15, 2012 at 4:22 PM, Ivan <kuzma(dot)wm(at)gmail(dot)com> wrote:

> Hi all.
>
> Today an accident happened on one of my databases. I have a table named
> "payments" with about 5400 rows. I have done a query "update payments set
> amount = 0; where id in (2354,2353,1232)". Please note the semicolon inside
> — I missed it =(
>
> Now all my data is lost. And after this happened I realized that backups
> script is broken (my fault, I know)
>
> I googled a little and have found that there is a chance to restore my
> data using files in pg_xlog directory. But everybody say about PITR and I
> don't use it. Also there is a xlogviewer project (from old 2006) that I'm
> trying to install on my Gentoo right now.
>
> I copied all PGDATA directory and made a dump of all databases. Also I
> turned off my webserver. Postgres is still running.
>
> Please give me some step-by step guide what should I do next? Is there any
> chance to restore my data?
>
> I use postgresql 8.4 with default config (autovacuum is commented)
>
> --
> ______________________________
> Yours sincerely, Ivan Kuznetsov aka Kuzma
> mailto: kuzma(dot)wm(at)gmail(dot)com
>

--
______________________________
Yours sincerely, Ivan Kuznetsov aka Kuzma
mailto: kuzma(dot)wm(at)gmail(dot)com