Обсуждение: 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