From: Gunnar \"Nick\" Bluth <gunnar.bluth@pro-open.de>
To: Raj Gandhi <raj01gandhi@gmail.com>
Cc: Craig Ringer <ringerc@ringerc.id.au>
Cc: Scott Ribe <scott_ribe@elevated-dev.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Detecting DB corruption
Date: Thu, 01 Nov 2012 17:30:07 +0100
Message-ID: <5092A38F.3020400@pro-open.de> (raw)
In-Reply-To: <CALU_HCNLua7XvAgVexj+YFy2etFrLT_z1sc-5OeG+KQ3xDuhUg@mail.gmail.com>
References: <CALU_HCPC7Vq60cq7=U7KDUdNGWwBbAhNvHb05EF5iOfvAc16Mw@mail.gmail.com>
<5091E377.2030505@ringerc.id.au>
<F4929CDE-D583-4075-8061-4285E3D813C8@elevated-dev.com>
<50920CF5.7010506@ringerc.id.au>
<50922D72.2020401@pro-open.de>
<CALU_HCNLua7XvAgVexj+YFy2etFrLT_z1sc-5OeG+KQ3xDuhUg@mail.gmail.com>
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
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: gunnar.bluth@pro-open.de, raj01gandhi@gmail.com, ringerc@ringerc.id.au, scott_ribe@elevated-dev.com
Subject: Re: Detecting DB corruption
In-Reply-To: <5092A38F.3020400@pro-open.de>
* 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