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
