[GENERAL] undo update

2012-03-15 Thread Ivan 
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...@gmail.com

Re: [GENERAL] undo update

2012-03-15 Thread Scott Marlowe 
On Thu, Mar 15, 2012 at 8:22 AM, Ivan kuzma...@gmail.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)

PITR can't help you after the fact if you don't have a base backup and
archives of the pg_xlog dir etc.

You might be able to pg_resetxlog to make the old rows visible, but
I'm no expert on doing that.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] undo update

2012-03-15 Thread Ivan 
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...@gmail.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...@gmail.com




-- 
__
Yours sincerely, Ivan Kuznetsov aka Kuzma
mailto: kuzma...@gmail.com

Re: [GENERAL] undo update

2012-03-15 Thread Steve Crawford 

On 03/15/2012 07:22 AM, Ivan 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.


I would first stop PostgreSQL and *then* copy your PGDATA directory. 
Given how PostgreSQL handles updates in a MVCC-safe way, there is a 
reasonable possibility that the data is still contained somewhere in the 
file(s) associated with that table as long as you don't cause it to be 
overwritten by a CLUSTER, VACUUM FULL or VACUUM followed by more 
updates. However I cannot speak to the steps or difficulty involved in 
recovering it.


Cheers,
Steve


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general