Обсуждение: undo update
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.wm@gmail.com
______________________________
Yours sincerely, Ivan Kuznetsov aka Kuzma
mailto: kuzma.wm@gmail.com
On Thu, Mar 15, 2012 at 8:22 AM, Ivan <kuzma.wm@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.
I have installed xlogviewer and it gives me data like that:
--
______________________________
Yours sincerely, Ivan Kuznetsov aka Kuzma
mailto: kuzma.wm@gmail.com
[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.wm@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.wm@gmail.com
______________________________
Yours sincerely, Ivan Kuznetsov aka Kuzma
mailto: kuzma.wm@gmail.com
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