Re: ideal postgresql install

On Mon, 10 May 2004, Campano, Troy wrote:

> Is there a guide to build an 'ideal' postgresql server?
> By ideal I mean the ideal to set up the postgresql server for a
> production environment.
> Stuff like separating log files on separate disks and other "Best
> Practices".

Unfortunately, there is no one best way. It really depends on just what
you're trying to accomplish. If you're building an OLAP database, you're
gonna do things differently than if you're gonna built an OLTP machine.

For certain storage arrays, there's no advantage whatsoever in trying to
partition out logs from data and indexes etc...

However, there are some absolute dos and don'ts that usually apply:

Don't turn off fsync unless you can reproduce all the data in the database
easily. fsync off is dangerous.

Don't use IDE drives with write caching enabled, at least for now, as they
seem to lie about fsyncing. I.e. using an IDE with write cache enabled is
pretty much functionally equivalent to turning off fsync. Only you might
THINK you have a reliable storage system because you've got fsync on.

RAID arrays are much faster than individual drives are.

Hardware RAID is usually faster at writing data than software arrays,
especially if you use battery backed cache.

Test your hardware, especially memory, CPU, controllers and drives, very
thoroughly. Any machine I'm putting online gets burnt in running
literally tens of millions of transactions before ever going live.

Don't store your data on a network share. It's generally bad form, and
many network file sharing systems are notoriously unreliable should power
be lost etc...

Worry more about I/O bandwidth, amount of memory, and memory bandwidth, in
that order, than about CPU speed.