Re: Detecting DB corruption - Mailing list pgsql-admin
From | Gunnar \"Nick\" Bluth |
---|---|
Subject | Re: Detecting DB corruption |
Date | |
Msg-id | 50922D72.2020401@pro-open.de Whole thread Raw |
In response to | Re: Detecting DB corruption (Craig Ringer <ringerc@ringerc.id.au>) |
Responses |
Re: Detecting DB corruption
|
List | pgsql-admin |
Am 01.11.2012 06:47, schrieb Craig Ringer: > 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. "Horribly" is not strong enough a word IMHO when we're discussing double primary key values... except if Raj is not using sequences to generate them. Although on the other hand, in that case, it's in turn an even more horrible setup; questionable DB design on unreliable hardware. Raj, would you mind pasting your schema somewhere, at least of the tables you experienced the corruption? >> True, but. In a past life, complaints from the db (it was a db that stored a checksum with every block) were the veryfirst symptom when something went horribly wrong with the hardware. (Partial short between wires of an internal SCSIcable; 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 think checksums are currently being worked on and are to be expected for 9.3. Might be interesting to scan -hackers for that once more... > 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 > > On a side note, Raj, you might want to read the descriptions of MVCC and WAL once more, then re-think about your idea of updating all rows and rolling back the transaction. That would potentially produce the effect you're looking for with InnoDB or Oracle, but not with PG. Cheers, -- Gunnar "Nick" Bluth RHCE/SCLA Mobil +49 172 8853339 Email: gunnar.bluth@pro-open.de __________________________________________________________________________ In 1984 mainstream users were choosing VMS over UNIX. Ten years later they are choosing Windows over UNIX. What part of that message aren't you getting? - Tom Payne
pgsql-admin by date: