undo update

From: Ivan <kuzma(dot)wm(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: undo update
Date: 2012-03-15 14:22:25
Message-ID: CAHJkLEvb8aOvrK0e9ECSsOY7RHKthts9FJ3mZ-PwjtsJBZGZuw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2012-03-15 14:25:15 Re: Backups
Previous Message Andrey Chursin 2012-03-15 13:15:29 Fetch from cursor with indexed sorting