Re: Adding foreign key constraints without integrity

On 6/19/06 4:48 PM, "Scott Marlowe" <smarlowe(at)g2switchworks(dot)com> wrote:

>
> I think Jim was thinking of RAID 1+0. If you gotta use RAID 5, at least
> have a contoller with several hundred megs of battery backed cache.
> Better yet, use a RAID controller with a gig or so of BBU cache and run
> RAID 1+0 on it. If you can.

Yes, I realize that is ideal (I meant to write RAID 10 not 1). That takes
hardware I don't have, and can't justify spending the money on right now.
I/O doesn't appear to be a major issue, although I wasn't constantly
monitoring it. Most of the time, the I/O load was not that high - CPU was
pegged. Normal production updates and queries are well within (not at all
low) expectations.

> Actually, the same question about why your frequently dumping and
> restoring applies to full vacuums. Why are you doing them? A properly
> running database should not need vacuum fulls. Nor reindexing.

Simple... VACUUM FULL reads the entire database. Between a full source
dump and a full vacuum, I've got a better chance of finding and recovering
from corruption sooner. Once bit... There is no (last time I checked)
utility to verify full database integrity, and there are no CRC's on data
blocks. A pg_dumpall doesn't touch the indexes. While this won't catch
everything, seems pretty good at catching corrupted indexes. I've seen no
more effective ways of verifying the entire database. The kind of hardware
problem we encountered last year is rare, but it does happen. I've seen
similar things many times over the years. RAID doesn't help you when a
controller mirrors garbage.

> You should look into slony. You can replicate from one version of pgsql
> to another, a feature intended to be used for updating.

I'd love to, but that requires hardware I don't have.

Wes