Detecting DB corruption

[Raj Gandhi]

I'm looking for ways to detect DB index and any other type of corruption in
DB. It looks like there is no tool to verify if Postgres DB is corrupted
or not.

I would like to detect some of the following DB corruptions:
- corruption in DB indexes
- detect duplicate primary keys in a table (noticed in couple of
instances where duplciates keys were found becuase of corrupted indexes)
- any page or block is corrupted

Planning to do the following on every restart of Postgres DB. Can someone
suggest if this is the write approach? Or, suggest better and faster
approach to detect the corruption.
- reindex database <dbname>
- for each table run : select count(*) from <table name> //to ensure
no rows are corrupted
- for each table run update: begin; update <table name> set
<col_name> = "value" ; rollback; //to update whole table and then
rollback the transactions
- run "vacuum analyze"

If indexes are corrupted then it will be re-built. For other types of
corruption, pg_dump will be restored from last known good backup.

How do I write a generic SQL script to detect the corruption, remove
duplicate rows etc.?


Using Postgres 8.3.18 on Linux. Database has around 100 tables with average
rows in a table are 500.


Thanks in advance for your help.

[Craig Ringer]

Post by Raj Gandhi
I'm looking for ways to detect DB index and any other type of corruption
in DB. It looks like there is no tool to verify if Postgres DB is
corrupted or not.

There is no database verifier tool. One would be quite nice to have for
testing and development purposes, though I question whether corruption
should be a concern in production. If you face the realistic risk of
database corruption, you need to urgently address the problems in your
setup that make that possible.

I wrote a bit about that a while ago:

http://blog.ringerc.id.au/2012/10/avoiding-postgresql-database-corruption.html

Seriously, if you're facing DB corruption then something is already
horribly wrong with your setup.

PostgreSQL isn't like MySQL with MyISAM; corruption is not routine and
part of life. It's a sign of a very bad problem, one you should diagnose
and fix not paper over. Do you expect ext3 file system corruption
routinely? No? PostgreSQL should be the same.

Post by Raj Gandhi
- corruption in DB indexes

A query that scans the whole index (say, to sort on it) should generally
find damaged pages in indexes. "Corruption" can cover many different
things, though, and some damage would not be detected by simply using
the index.

Post by Raj Gandhi
- detect duplicate primary keys in a table (noticed in couple of
instances where duplciates keys were found becuase of corrupted indexes)

A REINDEX will find that. Alternately, you might be able to formulate
queries that ignore the indexes and do duplicate searches by grouping by
the primary key with `enable_indexscan = off`, `enable_indexonlyscan =
off`, etc etc.

Post by Raj Gandhi
- any page or block is corrupted

I'd want to use the `pageinspect' extension to scan the table manually.
Create some artificially damaged blocks in a just-for-testing table and
make sure that doing so actually finds them.

Post by Raj Gandhi
Using Postgres 8.3.18 on Linux. Database has around 100 tables with
average rows in a table are 500.

Well, you're on an old version, but not one with any known serious
issues AFAIK.

--
Craig Ringer

[Scott Ribe]

Post by Craig Ringer
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.
--
Scott Ribe
***@elevated-dev.com
http://www.elevated-dev.com/
[phone] voice

[Craig Ringer]

Post by Scott Ribe

Post by Craig Ringer
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

[Gunnar "Nick" Bluth]

Post by Craig Ringer

Post by Scott Ribe

Post by Craig Ringer
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?

Post by Craig Ringer

Post by Scott Ribe
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 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...

Post by Craig Ringer
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 [phone]
Email: ***@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

[Raj Gandhi]

Re-sending to correct addresses.

Post by Gunnar "Nick" Bluth

Post by Craig Ringer
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.

Post by Gunnar "Nick" Bluth
Raj, would you mind pasting your schema somewhere, at least of the tables

you experienced the corruption?



Each DB table has primary key that is populated using DB-sequence. There is
a UNIQUE constraint created on natural keys.
The problem on the test setup was because disk cache was enabled. Indexes
were corrupted when powering down the host. I have noticed that integrity
of both PK and UNIQUE constraint were violated - Table had rows with
duplicate primary keys and in other case there were rows with duplicate
unique key constraint.

We are now evaluating to turn off the disk cache to avoid this kind of
corruption.

I would still like to have some mechanism to detect any kind of corruption
in Postgres. As Craig pointed out I am planning to run "reindex database
<dbname>" which will detect any duplicates and will also remove any
corruption as indexes are recreated.

About the corruption in table - will running "VACUUM FULL" on all tables
detect the corruption?
I see 8.4 and later version has param 'vacuum_freeze_table_age' which by
setting to 0 will force regular "vacuum" to run on whole database and will
check every block. I don't see that param in 8.3 though so I guess "vacuum
full" is the only option.

If "vacuum full" is not going to detect the corruption then I am also
thinking to run "pg_dump" which should catch the corruption.


On Thu, Nov 1, 2012 at 4:06 AM, Gunnar "Nick" Bluth <

Post by Gunnar "Nick" Bluth

Post by Craig Ringer

Post by Craig Ringer
Seriously, if you're facing DB corruption then something is already

Post by Craig Ringer
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

Post by Craig Ringer

Post by Craig Ringer
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 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

Post by Craig Ringer
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 [phone]
______________________________**______________________________**
______________
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

[Gunnar "Nick" Bluth]

Post by Raj Gandhi
Each DB table has primary key that is populated using DB-sequence.
There is a UNIQUE constraint created on natural keys.

That does sound decent.

Post by Raj Gandhi
The problem on the test setup was because disk cache was enabled.
Indexes were corrupted when powering down the host. I have noticed
that integrity of both PK and UNIQUE constraint were

You should have mentioned that in the beginning. "Powering down" meant
"remove from the power line" in this case, right?
That is a situation that certainly can lead to corruption.

Post by Raj Gandhi
violated - Table had rows with duplicate primary keys and in other
case there were rows with duplicate unique key constraint.
We are now evaluating to turn off the disk cache to avoid this kind of
corruption.

Never too late ;-)

Post by Raj Gandhi
About the corruption in table - will running "VACUUM FULL" on all
tables detect the corruption?
I see 8.4 and later version has param 'vacuum_freeze_table_age' which
by setting to 0 will force regular "vacuum" to run on whole database
and will check every block. I don't see that param in 8.3 though so I
guess "vacuum full" is the only option.

CLUSTER will probably be the better approach here. Shouldn't take too
long on 500 record tables.

Post by Raj Gandhi
If "vacuum full" is not going to detect the corruption then I am also
thinking to run "pg_dump" which should catch the corruption.

<pun>In your current situation, pg_restore sounds more reasonable</pun>

I've luckily never been in your situation, but I'd guess pg_dump will
just happily dump what it sees. It's not like a seq scan will realize
"oh, I've seen that value before" and bail out. The _restore_ will bring
it to light though...

Good luck anyway.
--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil [phone]
Email: ***@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