Re: help with data recovery from injected UPDATE

Success, of sorts. I was able to retrieve 90% the corrupted data by
dumping the heap file. Many thanks to those who replied with helpful
suggestions.

If you're interested in detail then read on. Otherwise, don't bother.

The data was still in the table -- I could see it using a hex editor.
This surprised me, as autovacuum was on at the time of corruption.
Perhaps vacuum didn't bother reclaiming storage space because the
database is relatively small and low-traffic.

The attempt at point-in-time-recovery via transaction logs was doomed
to failure, as I do not have a file system backup from before the
corruption. Still, I tried Merlin's trick with pg_resetxlog to no
avail.

I tried using the pg_filedump utility to dump the heap file, but it
wasn't what I needed. I later discovered a souped-up utility called
pg_dumpdata:
http://blogs.sun.com/avalon/entry/recovering_postgres_data
While this utility still didn't provide everything I needed, it was a
sufficient starting point.
(It's written for postgres 8.2, whereas I'm running 8.1 -- it
segfaulted when I first ran it on my heap file.)

I sifted through the postgres source tree looking for the code that
reads/writes the heap files, but I couldn't make head or tail of
anything. In the end, it was easier to reverse engineer the format
for user data and use the pg_dumpdata source as a base to get me to
the "items" in the heap files. The reason that I couldn't get 100% of
the lost data is that the heap tuple header that points to the user
data sometimes landed me at a random point in the middle of the item,
rather than at the beginning. At this point I gave up trying to get
the last 10% of the data -- I had run out of time and patience.

Having partially learned my lesson, I've set up a utility to run
pg_dump each day. After I've taken a break, I'll look into a
reasonabe set-up for file system backups with point-in-time recovery.
But really, what are the chances anything like this will ever happen
again? ;-)

-Gus

On Mon, Jun 15, 2009 at 9:02 AM, Merlin Moncure<mmoncure(at)gmail(dot)com> wrote:
> 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