Re: How to determine a database is intact? - Mailing list pgsql-general
From | Richard Huxton |
---|---|
Subject | Re: How to determine a database is intact? |
Date | |
Msg-id | 41388A5C.7040709@archonet.com Whole thread Raw |
In response to | How to determine a database is intact? (Wes <wespvp@syntegra.com>) |
Responses |
Re: How to determine a database is intact?
(Wes Palmer <wespvp@syntegra.com>)
|
List | pgsql-general |
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
pgsql-general by date: