Re: Detecting DB corruption

Am 01.11.2012 16:10, schrieb 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.
> 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.

> 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 ;-)


> 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.

>
> 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   +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