On Tue, Jun 23, 2009 at 2:05 PM, Gus Gutoski<shared.entanglement@xxxxxxxxx> wrote: > 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? ;-) Regular scheduled pg_dump is often enough :-) merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
