Re: How to determine a database is intact?

On 9/3/2004 10:59 AM, 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.

Right, that's what a SIGSEGV is. And the usual reason for the bad value
in that pointer is bad memory. What do you base your guess of a self
multiplying corruption on? Or is this pure handwaving in self-defense?

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

So you are running a Release that had 4 official bugfix releases from
the vendor on hardware that is in an unknown condition? Is the server at
least configured with ECC Ram, or is the data not important enough to
justify for quality hardware?

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

Dump and restore. You don't need to restore onto the same server. Any
test system with enough disk space would do.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #