Обсуждение: postgresql cluster on SAN

Поиск
Список
Период
Сортировка

postgresql cluster on SAN

От
Robert Ngo
Дата:
Hi,

Can i create create a cluster of postgresql server by sharing the
database file on a SAN? I am also looking into slony but slony replicate
data to each server and my database will potentially have terabytes of
data. I am thinking about a solution where a cluster of database server
will share database files on SAN, can this be done? I am also looking
for a load balancing salution for the postgresql database. Any sugestion?



Re: postgresql cluster on SAN

От
Chris Browne
Дата:
robertngo@perridot.com (Robert Ngo) writes:
> Can i create create a cluster of postgresql server by sharing the
> database file on a SAN? I am also looking into slony but slony replicate
> data to each server and my database will potentially have terabytes of
> data. I am thinking about a solution where a cluster of database server
> will share database files on SAN, can this be done? I am also looking
> for a load balancing salution for the postgresql database. Any sugestion?

What you wish can NOT be done.

There can only be ONE postmaster for each database cluster; you can
NOT have multiple servers sharing a single cluster.
--
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://cbbrowne.com/info/internet.html
"The real  romance is   out   ahead and   yet to come.    The computer
revolution hasn't started yet. Don't be misled by the enormous flow of
money into bad defacto standards for unsophisticated buyers using poor
adaptations of incomplete ideas." -- Alan Kay

Re: postgresql cluster on SAN

От
Robert Ngo
Дата:
Robert Ngo wrote:

> Chris Browne wrote:
>
>> robertngo@perridot.com (Robert Ngo) writes:
>>
>>
>>> Can i create create a cluster of postgresql server by sharing the
>>> database file on a SAN? I am also looking into slony but slony
>>> replicate
>>> data to each server and my database will potentially have terabytes of
>>> data. I am thinking about a solution where a cluster of database server
>>> will share database files on SAN, can this be done? I am also looking
>>> for a load balancing salution for the postgresql database. Any
>>> sugestion?
>>>
>>
>>
>> What you wish can NOT be done.
>>
>> There can only be ONE postmaster for each database cluster; you can
>> NOT have multiple servers sharing a single cluster.
>>
>>
> so there is no ways for the file storage to be shared between postgres
> cluster? Then i will need to add a huge amount of storage every time
> new server is added.
>
Is there any way i can reduce the storage requirement of the database
server cluster?

Re: postgresql cluster on SAN

От
"Magnus Hagander"
Дата:
> Hi,
>
> Can i create create a cluster of postgresql server by sharing
> the database file on a SAN? I am also looking into slony but
> slony replicate data to each server and my database will
> potentially have terabytes of data. I am thinking about a
> solution where a cluster of database server will share
> database files on SAN, can this be done? I am also looking
> for a load balancing salution for the postgresql database.
> Any sugestion?
>

Yes, you can, but *only* if you use some HA clustering software that
makes sure that *only one node at a time accesses the disk resources*.
This means that one node will be completely passive (postmaster not even
started! filesystem not even mounted!) and will only be activated on
failover (at which time the HA software must *ensure* that the old
postmaster can no longer access the disk resources).
(Don't know what they are called on linux, I'm sure they exist, as well
as for any commercial Unix. It should work fine with MSCS if you're on
win32)

This will give you failover, but *not* load balancing. For load
balancing you need to replicate the data in some way (slony being one of
the options, but there are others). Once you've done this, you can front
the system with pgpool for load balancing.

//Magnus

Re: postgresql cluster on SAN

От
Chris Browne
Дата:
robertngo@perridot.com (Robert Ngo) writes:

> Robert Ngo wrote:
>
>> Chris Browne wrote:
>>
>>> robertngo@perridot.com (Robert Ngo) writes:
>>>
>>>
>>>> Can i create create a cluster of postgresql server by sharing the
>>>> database file on a SAN? I am also looking into slony but slony
>>>> replicate
>>>> data to each server and my database will potentially have terabytes of
>>>> data. I am thinking about a solution where a cluster of database server
>>>> will share database files on SAN, can this be done? I am also looking
>>>> for a load balancing salution for the postgresql database. Any
>>>> sugestion?
>>>>
>>>
>>>
>>> What you wish can NOT be done.
>>>
>>> There can only be ONE postmaster for each database cluster; you can
>>> NOT have multiple servers sharing a single cluster.
>>>
>> so there is no ways for the file storage to be shared between
>> postgres cluster? Then i will need to add a huge amount of storage
>> every time new server is added.
>>
> Is there any way i can reduce the storage requirement of the database
> server cluster?

Well, the base amount of storage for each instance is only about 26MB.

sh-3.00$ /opt/OXRS/dbs/pgsql8/bin/initdb -D .
Success. You can now start the database server using:

    /opt/OXRS/dbs/pgsql8/bin/postmaster -D .
or
    /opt/OXRS/dbs/pgsql8/bin/pg_ctl -D . -l logfile start

sh-3.00$ du
176     ./global
4       ./pg_xlog/archive_status
16412   ./pg_xlog
12      ./pg_clog
12      ./pg_subtrans
4640    ./base/1
4704    ./base/17229
9348    ./base
4       ./pg_tblspc
25992   .
sh-3.00$

For any sort of interesting-sized database, this bit of overhead,
which is mostly WAL files, doesn't strike me as being terribly
material.  I can't see 26MB being considered "huge" in a SAN context.

You only need additional storage (beyond that) when your applications
are actually requesting to store data.  There is no magical way to
avoid that need for additional storage.
--
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/languages.html
"The newsreader abuse likely  stems from more fundamental, than merely
just the  UI, design disagreements. Requests from  Unix programmers to
replicate  Free Agent  rightfully so  should trigger  the  throwing of
sharp heavy objects at the requesting party."
-- jedi@dementia.mishnet (jedi)

Re: postgresql cluster on SAN

От
Chris Travers
Дата:
Robert Ngo wrote:

> Robert Ngo wrote:
>
>> Chris Browne wrote:
>>
>>> robertngo@perridot.com (Robert Ngo) writes:
>>>
>>>
>>>> Can i create create a cluster of postgresql server by sharing the
>>>> database file on a SAN? I am also looking into slony but slony
>>>> replicate
>>>> data to each server and my database will potentially have terabytes of
>>>> data. I am thinking about a solution where a cluster of database
>>>> server
>>>> will share database files on SAN, can this be done? I am also looking
>>>> for a load balancing salution for the postgresql database. Any
>>>> sugestion?
>>>>
>>>
>>>
>>>
>>> What you wish can NOT be done.
>>>
>>> There can only be ONE postmaster for each database cluster; you can
>>> NOT have multiple servers sharing a single cluster.
>>>
>>>
>> so there is no ways for the file storage to be shared between
>> postgres cluster? Then i will need to add a huge amount of storage
>> every time new server is added.
>>
> Is there any way i can reduce the storage requirement of the database
> server cluster?

Maybe you can provide us with more information on your environment,
application, expected load, etc. so we can help you explore other
possibilities.
Best Wishes,
Chris Travers

Вложения