Thread: Recovering / undoing transactions?

Hi,

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").

Ivan Voras <ivoras@freebsd.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

On 07/11/2007, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> 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.

Ok, just to verify I'm thinking about it in the right way: in
abstract, with PITR, I would need a known-good starting point (e.g. a
full backup) + files from pg_xlog created from the time of the
starting-point, then restore the starting-point backup and then
restore from PITR/xlog up to the point I want?

"Ivan Voras" <ivoras@freebsd.org> writes:
> Ok, just to verify I'm thinking about it in the right way: in
> abstract, with PITR, I would need a known-good starting point (e.g. a
> full backup) + files from pg_xlog created from the time of the
> starting-point, then restore the starting-point backup and then
> restore from PITR/xlog up to the point I want?

Right.

            regards, tom lane