From: Craig Ringer <ringerc@ringerc.id.au>
To: Scott Ribe <scott_ribe@elevated-dev.com>
Cc: Raj Gandhi <raj01gandhi@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Detecting DB corruption
Date: Thu, 01 Nov 2012 13:47:33 +0800
Message-ID: <50920CF5.7010506@ringerc.id.au> (raw)
In-Reply-To: <F4929CDE-D583-4075-8061-4285E3D813C8@elevated-dev.com>
References: <CALU_HCPC7Vq60cq7=U7KDUdNGWwBbAhNvHb05EF5iOfvAc16Mw@mail.gmail.com>
<5091E377.2030505@ringerc.id.au>
<F4929CDE-D583-4075-8061-4285E3D813C8@elevated-dev.com>
On 11/01/2012 01:10 PM, Scott Ribe wrote:
> On Oct 31, 2012, at 8:50 PM, Craig Ringer wrote:
>
>> Seriously, if you're facing DB corruption then something is already
>> horribly wrong with your setup.
> True, but. In a past life, complaints from the db (it was a db that stored a checksum with every block) were the very first symptom when something went horribly wrong with the hardware. (Partial short between wires of an internal SCSI cable; eventually we determined that about every 1MB, 1 bit would get flipped between the controller & disk.)
>
> So, if there were an official db verifier tool for PG, I for one would have it run periodically.
If there were a way to reliably detect corruption, so would I. As things
stand there are no block checksums, so if a bit gets flipped in some
random `text` field you're never going to know, corruption-checker or
no. Some forms of random corruption - like bad blocks on disks causing
I/O errors, zeroed blocks, truncated files, etc - will become apparent
with general checking, but others won't be detectable unless you know
what the expected vs actual data is.
If page checksumming or any other reliable method of detecting possible
incipient corruption were available I'd quite likely want to use it for
much the same reason you outlined. For that matter, if there were a
general "sanity check my tables and indexes" tool I'd probably use that
too. However, no such tool exists - and in a good setup, none should be
needed. I'd want to use one anyway purely out of paranoia.
--
Craig Ringer
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ringerc@ringerc.id.au, scott_ribe@elevated-dev.com, raj01gandhi@gmail.com
Subject: Re: Detecting DB corruption
In-Reply-To: <50920CF5.7010506@ringerc.id.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox