Re: help with data recovery from injected UPDATE

On Sun, 2009-06-14 at 10:32 -0400, Gus Gutoski wrote:

> 0. Shortly after the corruption on June 9, 2009, I shut down the
> server and backed up the entire data directory. The recovery
> procedure described herein begins with this file system backup.
> 1. The most recent non-corrupted snapshot of the database is a pg_dump
> from May 13, 2009. (I don't have any file system backups from before
> the corruption.) I restored the database to this snapshot by
> executing the commands from the May 13 pg_dump on the June 9 corrupted
> data.
> 2. I removed the files in the pg_xlog directory and replaced them
> with the contents of pg_xlog from the corrupted file system backup
> from June 9.

That really, REALLY won't work. It just doesn't work like that.

You're trying to use a block-level restore process (the transaction
logs) with a base backup that's at a much higher level, and isn't
block-for-block the same as the old database files. Additionally, you're
trying to do so over a known corrupt database.

The only thing that confuses me is how you convinced Pg to run recovery
using the xlog files you put in place. It should've refused, surely?

> I guess it's too much to ask postmaster to do a PITR from a pg_dump
> backup, as opposed to a file system backup. Bummer.

Yep. No hope.

--
Craig Ringer