Re: undo update

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.