Re: Recovering / undoing transactions?

Ivan Voras <ivoras(at)freebsd(dot)org> writes:
> About a month or so ago I read a blog entry or an article which seems to
> have described a method, using dirty hackery with pg_resetxlog and
> possibly other tools, to forcibly "undo" the database to a previous
> state. The problem described was that some employee had executed a
> "DELETE" or "UPDATE" without "WHERE" or something like it in autocommit
> mode and the goal was to undo it.

> I can't find the article now so can someone describe the technique here
> or point to the article?

> (I'm possibly misremembering important details about the article so the
> correct answer to my question could be "no, it can't be done").

It's not really possible to do that. The blogger might've thought he'd
accomplished something but I seriously doubt that his database was
consistent afterward. You can go back in time using PITR, if you had
the foresight and resources to set up continuous archiving, but just
whacking pg_xlog around is far from sufficient.

regards, tom lane