Re: Database corruption: finding the bad block

On Thu, 2007-07-12 at 15:09 +0200, Csaba Nagy wrote:
> Luckily I remembered I have a WAL logging based replica, so I
> recovered
> the rest of the truncated file from the replica's same file... this
> being an insert only table I was lucky I guess that this was an
> option.
> To my surprise, the same block on the replica was not mangled... I say
> to my surprise, because on other occasions the bad blocks readily
> replicated over. In any case if you have a WAL logged replica you
> might
> be lucky to recover the corrupt block(s) from there (or just switch
> over, but that is risky too, you can't know for sure in what state the
> replica is, and that is actually harder to investigate than the
> master,
> as you can execute no SQL on the replica).

The corruption could only migrate if the WAL records themselves caused
the damage, which is much less likely than corruption of the data blocks
at hardware level. ISTM that both Slony and Log shipping replication
protect fairly well against block corruption on the standby, but only
log shipping allows you to recover the precise block, as you describe.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com