Re: How to determine a database is intact?

On Thu, Sep 09, 2004 at 12:30:31PM -0500, Wes wrote:
> On 9/9/04 11:07 AM, "Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>
> > Believe it or not, we haven't gotten many requests for this feature,
> > partly because such corruption is so rare. Also, any checker isn't
> > going to find a change from "Baker" to "Faker" in a text field.
>
> Yep, unless you added a CRC (and accepted the performance hit) to each
> record the best you could do is verify that the database is consistent.
> That would still be quite valuable, though - all block headers are valid,
> indexes don't point out into oblivion, etc.
>
> I expect there are only a handful of huge databases running a heavy load -
> the vast majority are probably tens no larger than 10's (maybe hundreds) of
> megabytes, or do not experience a heavy update load?

I have a CRM-type application installed at a number of customer
sites. Database sizes are in the 5 - 50 gigabyte range, and update
rates are pretty high - hundreds of thousands a day, certainly, probably
over a million a day at some sites.

They've been running continuously for several years, on a mix of
Solaris and Linux systems ranging from big SMP Sun boxes with nice
RAID systems down to dinky little dual-CPU Linux/x86 boxes with a
couple of software mirrored drives.

A lot of the data is of a form where data corruption will be
immediately recognised by the application (has to be one of a few
pre-defined strings, or integers that have to be in a given range,
that sort of thing).

I don't believe that any customer has had database-related downtime
apart from the scheduled, painful 7.2->7.4 upgrade. I've never seen a
problem due to data corruption, despite a number of kernel panics,
power failures and other such facts of life.

And that's with no manual database maintenance at all. Just an
application-specific maintenance daemon that does cleanup, vacuuming
and analysis.

Cheers,
Steve