Re: Does PostgreSQL Stores its database in multiple disks?

On Fri, 2004-10-01 at 15:01, Igor Maciel Macaubas wrote:
> Hi Guys,
>
> I have a PostgreSQL server, running 7.4.2, that will store a really
> large amount of data (200GB) being migrated from an Oracle database. I
> have a machine with two 120GB Ultra ATA IDE disks,

I'd normally advise against ATA drives in a production system. If you
must use them, make sure the write cache is turned off or you risk data
corruption should the machine lose power.

> and I'd like to know if PostgreSQL could split it over both disks
> (that gives me 240GB or storage). Does he do this automatically ?

Not automatically, no. With 8.0's table space feature, you could put
half your database tables etc... on one drive and one half on another.
This would require you to keep track of how much your putting in one or
other, and could get rather tedious.

If you don't mind shutting down the database and copying / linking the
individual table files you could do that, but again, that's gonna
require you to do a lot of housekeeping on your own.

Lastly, you could use some kind of RAID or LVM setup to accomplish this.

For testing purposes running this on a RAID-0 with linux's MD or LVM
modules should work fine. But RAID-0 has no redundancy and a single
drive failure means the whole set goes down.

With 4 drives you could set up a RAID 1+0 array (or 0+1) and place your
data there. That setup could survive a single drive failure and keep on
going.

If your database will be writing a lot, it would be well worth your
while to investigate a SCSI RAID controller with battery backed cache
set to write back.

> Or I'll have to split it manually, creating symbolic links on my file
> system (ext3)?

That would be a lot of work.

> Does anyone ever stored a database (see, it's ONE database only, not a
> cluster) on multiple disks ?

Standard answer is use RAID arrays.

> What about PgSQL 8? It'll include this feature?

Not really. While it will support table spaces, the job of combining
multiple drives into one large logical drive is the domain of your
operating system / underlying hardware.

I recommend the LSI MegaRAID series controllers, and a few other folks
on the list have had good luck with the Escalade IDE RAID controllers,
which aren't the toys most IDE RAID controllers are, they support
multiple RAID levels as well as battery backed cache.