Re: [BUGS] Integrity check

On Tue, Jun 23, 2009 at 2:00 PM, Dave Page<dpage(at)pgadmin(dot)org> wrote:
> On Tue, Jun 23, 2009 at 1:38 PM, David Fetter<david(at)fetter(dot)org> wrote:
>> On Tue, Jun 23, 2009 at 03:38:35PM +0800, Prasad, Venkat wrote:
>
>>> * do you any tool to check postgreSQL database integrity check?
>>
>> No more than Oracle does.  We get it right in the first place.  The
>> existence of "integrity checking" tools means the DBMS is done with
>> high incompetence.

Uhm well, data corruption still happens even if the DBMS is entirely
bug-free (which Postgres hasn't always been in the past anyways...)
Hardware sometimes fails, filesystems have bugs, and there are gotchas
with hot backups which can result in corrupt databases. Witness the
recent attempts to implement CRC checksums which failed for technical
reasons. There is demand for this type of assurance but it's just a
lot harder to implement usefully than it sounds.

Consider also the problem that there's a limited amount of corruption
it could actually test for. A lot of corruption could happen within
the data itself, rather than in the metadata. Or metadata could be
corrupted but still look reasonable. Even if we had checksums it's
easy to get into a situation where the checksum is calculated based on
the corrupt data.

But the real reason we don't have such a tool, imho, is that it would
be boring unsexy work, especially when you factor in the maintenance
effort. And the various code paths for corrupt data would rarely get
used -- there's not much glory in writing a tool that all anyone sees
it print is "everything's ok".

>>> * how do we confirm that dump file is proper data?
>>
>> See above.
>
> Whilst I don't disagree with the sentiment, that sort of answer is
> unlikely to impress auditors in my experience. Annoyingly they tend to
> want you to be able to prove to them that your backups are good.

If I were an auditor the only proof i would believe that a backup was
good was to see it actually get restored successfully (and the
restored database pass integrity checks). Any special-purpose tool
will only prove that the backup passes the checks that that
special-purpose tool checks for. The best way to test what will happen
in a real restore situation is to actually simulate one.

--
greg
http://mit.edu/~gsstark/resume.pdf