Re: [HACKERS] Finding corrupt data

Bruce Momjian wrote:

> > Bruce Momjian wrote:
> >
> > > > One RDBMS I used had a utility called 'dbcheck' which did some sort of
> > > > examination of indices, tables, etc., and issued an 'OK' or 'CORRUPT' for
> > > > each examined object.  Such a utility for pgsql might simply do some
> > > > combination of SELECT * or COPY TO as you suggest above.
> > >
> > > Does vacuum already do that?
> >
> > Not as far as I can tell.   Here's the kind of output I see from vacuum:
> >
> > DEBUG:  --Relation pg_class--
> > DEBUG:  Pages 10: Changed 0, Reapped 1, Empty 0, New 0; Tup 695: Vac 0, Keep/VTL
> > 0/0, Crash 0, UnUsed 35, MinLen 102, MaxLen 132; Re-using: Free/Avail. Space
> > 3828/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
> > DEBUG:  Index pg_class_relname_index: Pages 16; Tuples 695: Deleted 0. Elapsed
> > 0/0 sec.
> > DEBUG:  Index pg_class_oid_index: Pages 7; Tuples 695: Deleted 0. Elapsed 0/0
> > sec.
> >
> > Am I missing something?
>
> Vacuum does catch some problems, not all of them.

Yes, and vacuum appears to be the only known remedy to my current postgresql
showstopper.  For that, I'm grateful.  However, I think that misses the point I'm
trying to convey...

There are a three basic tasks critically important to an operationally viable
database, from my perspective.  First, I need to be able to easily create a backup of
the database at any point.  The pg_dump appears to serve that function.

Second, I need to be able to restore from a backup copy if something goes terribly
wrong.  Psql coupled with pg_dump output seems to support that.  So far, so good.

Third, and most importantly, I need to be able to tell *when* I need to restore from
a backup.  A restoration from a backup copy usually involves a likely loss of data,
and that can be a Very Big Deal.  "Is this database corrupt?", is a critically
important question.  And I need to be able to answer it without knowing the details
of postgresql C code.  If I can't somehow answer that question when a problem arises,
the total cost of ownership of the database jumps pretty dramatically due to wasted
time and data loss, and the operability/viability drops in tandem.

Cheers,
Ed Loehr