Re: undo update

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