Re: How to determine a database is intact?

Wes wrote:
> On 9/3/04 3:11 AM, "Richard Huxton" <dev@archonet.com> wrote:
>
>
>>You shouldn't have to verify anything. PG's job is to never corrupt your
>>data, and providing your hardware is good it should do so. If you are
>>getting problems almost daily that would suggest a RAM/disk problem to
>>me (sig 11 usually implies RAM). Can't guarantee it's not PG but it's
>>record of reliability is pretty good.
>
>
> I believe SEGV typically just indicates it de-referenced a bad pointer (i.e.
> NULL  or out of range).  The problem is not occurring on a daily basis.  The
> database has been in service since December of last year.  It's just that
> the symptoms progressed from no apparent symptoms, to a clearly corrupt DB.
> My guess is that some minor corruption fed upon itself until the DB couldn't
> even be dumped.

Or even just that block of index was never used.

>>Steps I'd take:
>>1. Check your version number against the release notes and see if you
>>should upgrade. You don't mention your version, but it's always worth
>>having the last dot-release (7.2.5, 7.3.7, 7.4.5)
>>2. Schedule time to run memory/disk tests against your hardware. Finding
>>48 hours might not be easy, but you need to know where you stand.
>>3. Setup slony or some other replication so I can schedule my downtime.
>
>
> I thought I mentioned the level in my original mail - 7.4.1.  We are
> planning on running some diagnostics.

Ah - first thing you can do is move to 7.4.5, that won't require a
dump/reload. Do read the release notes first though.

> Whether there is a bug in PostgreSQL, or there was a memory hit, or whatever
> doesn't really matter to the original question.  The database can become
> corrupt.  How can I tell that a database is fully intact at any given point
> in time?  If I reload from a system backup before the known corruption, how
> can I be sure that the original corruption that precipitated the failure is
> not still there and will again rear its ugly head?

Put bluntly, you can't. The only way to verify the database as a whole
is to check every single value in it. If actual values get corrupted
then you may never even notice (e.g. a text field with a single
character corrupted).
However, if you dump and restore then three things can be guaranteed:
  1. All values are valid for their type
  2. All indexes are rebuilt
  3. Constraints will be satisfied on all data.
Is that good enough in your case?

--
   Richard Huxton
   Archonet Ltd