Re: help with data recovery from injected UPDATE

On Sun, Jun 14, 2009 at 10:32 AM, Gus
Gutoski<shared(dot)entanglement(at)gmail(dot)com> wrote:
> Merlin Moncure wrote:
>>> postgresql  8.1 supports pitr archiving.  you can
>>> do continuous backups and restore the database to just before the bad
>>> data.
>
> I tried using point-in-time-recovery to restore the state of the
> database immediately before the corruption.  It didn't work, but it
> was quite a show.  Here's the story.

yes, I'm sorry...you misunderstood my suggestion. the database
supports continuous *archiving* from which a recovery can be made. No
archives, no recovery :-). Here is what I'd do if I in your shoes:

From a copy of your filesystem backup, set up the database to run and
attempt pg_resetxlog before starting it up. Log in and see if your
data is there...if it is, you hit the jackpot...if not...the next step
is to determine if the data is actually _in_ the table. There are a
couple of ways to do this..tinkering around with transaction
visibility is one...simply dumping the heap file for the table and
inspecting it is another.

merlin