Thread: Database integrity and disaster recovery

Database integrity and disaster recovery

From
Martin Christensen
Date:
I've heard some rumours off Slashdot that PostgreSQL databases tend to
corrupt more than, say, MySQL, and that disaster recovery is more
difficult. So far I've not much database administration experience, so
I cannot say whether this is true or not. I rely on you for the
answer. Is this true or not?

Martin

--
GPG public key: http://home1.stofanet.dk/factotum/gpgkey.txt

Re: Database integrity and disaster recovery

From
Tom Lane
Date:
Martin Christensen <knightsofspamalot-factotum@mail1.stofanet.dk> writes:
> I've heard some rumours off Slashdot that PostgreSQL databases tend to
> corrupt more than, say, MySQL, and that disaster recovery is more
> difficult.

The MySQL people assert that, but their objectivity is, um, suspect.
I don't think I've heard any such claim from an independent third
party.

I've heard of very few cases of actual data corruption in Postgres
databases --- and several of the ones I have seen personally were
eventually traced to flaky disk or disk-controller hardware, not
software faults.  We have had some problems with index corruption
(although those bugs are progressively getting cleaned out), but the
basic table format is mighty simple and hard to do much damage to.

Recovery from a corrupted index on a user table is pretty easy: drop
and recreate the index.  Corrupted system indexes are nastier, since
dropping them may disable normal database operations entirely.
Beginning in 7.0 there is a REINDEX command that you can invoke in
standalone mode if you find yourself in that position.  It's a pretty
rare problem, however.

            regards, tom lane

Re: Database integrity and disaster recovery

From
Martin Christensen
Date:
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> I've heard of very few cases of actual data corruption in
Tom> Postgres databases --- and several of the ones I have seen
Tom> personally were eventually traced to flaky disk or
Tom> disk-controller hardware, not software faults.

Thank you, that's all I wanted to know. Corrupted indices are, as you
say, a very modest problem.

Martin

--
GPG public key: http://home1.stofanet.dk/factotum/gpgkey.txt