pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
postgresql cluster on SAN
12+ messages / 6 participants
[nested] [flat]

* postgresql cluster on SAN
@ 2005-09-20 06:47 Robert Ngo <robertngo@perridot.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Robert Ngo @ 2005-09-20 06:47 UTC (permalink / raw)
  To: pgsql-admin

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?




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* postgresql cluster on SAN
@ 2005-09-21 00:39 Robert Ngo <robertngo@perridot.com>
  2005-09-21 02:14 ` Re: postgresql cluster on SAN Chris Browne <cbbrowne@acm.org>
  0 siblings, 1 reply; 12+ messages in thread

From: Robert Ngo @ 2005-09-21 00:39 UTC (permalink / raw)
  To: pgsql-admin

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?





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: postgresql cluster on SAN
  2005-09-21 00:39 postgresql cluster on SAN Robert Ngo <robertngo@perridot.com>
@ 2005-09-21 02:14 ` Chris Browne <cbbrowne@acm.org>
  2005-09-21 05:37   ` Re: postgresql cluster on SAN Robert Ngo <robertngo@perridot.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Chris Browne @ 2005-09-21 02:14 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: postgresql cluster on SAN
  2005-09-21 00:39 postgresql cluster on SAN Robert Ngo <robertngo@perridot.com>
  2005-09-21 02:14 ` Re: postgresql cluster on SAN Chris Browne <cbbrowne@acm.org>
@ 2005-09-21 05:37   ` Robert Ngo <robertngo@perridot.com>
  2005-09-21 06:33     ` Re: postgresql cluster on SAN Chris Travers <chris@metatrontech.com>
  2005-09-21 13:06     ` Re: postgresql cluster on SAN Chris Browne <cbbrowne@acm.org>
  0 siblings, 2 replies; 12+ messages in thread

From: Robert Ngo @ 2005-09-21 05:37 UTC (permalink / raw)
  To: pgsql-admin

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?



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: postgresql cluster on SAN
  2005-09-21 00:39 postgresql cluster on SAN Robert Ngo <robertngo@perridot.com>
  2005-09-21 02:14 ` Re: postgresql cluster on SAN Chris Browne <cbbrowne@acm.org>
  2005-09-21 05:37   ` Re: postgresql cluster on SAN Robert Ngo <robertngo@perridot.com>
@ 2005-09-21 06:33     ` Chris Travers <chris@metatrontech.com>
  1 sibling, 0 replies; 12+ messages in thread

From: Chris Travers @ 2005-09-21 06:33 UTC (permalink / raw)
  To: Robert Ngo <robertngo@perridot.com>; pgsql-admin

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

Attachments:

chris.vcf (text/x-vcard, 125B) download begin:vcard fn:Chris Travers n:Travers;Chris email;internet:chris@metatrontech.com x-mozilla-html:FALSE version:2.1 end:vcard
^ permalink raw reply [nested|flat] 12+ messages in thread

* Re: postgresql cluster on SAN
  2005-09-21 00:39 postgresql cluster on SAN Robert Ngo <robertngo@perridot.com>
  2005-09-21 02:14 ` Re: postgresql cluster on SAN Chris Browne <cbbrowne@acm.org>
  2005-09-21 05:37   ` Re: postgresql cluster on SAN Robert Ngo <robertngo@perridot.com>
@ 2005-09-21 13:06     ` Chris Browne <cbbrowne@acm.org>
  1 sibling, 0 replies; 12+ messages in thread

From: Chris Browne @ 2005-09-21 13:06 UTC (permalink / raw)
  To: pgsql-admin

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)



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: postgresql cluster on SAN
@ 2005-09-21 07:19 Magnus Hagander <mha@sollentuna.net>
  0 siblings, 0 replies; 12+ messages in thread

From: Magnus Hagander @ 2005-09-21 07:19 UTC (permalink / raw)
  To: Robert Ngo <robertngo@perridot.com>; pgsql-admin

> 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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: postgresql cluster on SAN
@ 2005-09-23 13:07 Spiegelberg, Greg <gspiegelberg@cranel.com>
  2005-09-23 19:57 ` Re: postgresql cluster on SAN Chris Travers <chris@travelamericas.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Spiegelberg, Greg @ 2005-09-23 13:07 UTC (permalink / raw)
  To: Chris Browne <cbbrowne@acm.org>; pgsql-admin

This is possible.  Below is a brief recipe.

1. Use Veritas Storage Foundation HA with Cluster File System.
2. Have a volume with cfs available to 2 or more systems via SAN.
3. Install PostgreSQL and be sure PGDATA is on the cfs mount
4. Create a Storage Checkpoint of cfs mount (read-write virtual copy)
5. Bring up PostgreSQL on node1
6. Mount storage checkpoint on node2
7. Start PostgreSQL on node2

Drawbacks?  Heck yes.  There are many and you should understand the
nature of Veritas Storage Checkpoints before using.  I have tested it
though in conjunction with dblink() and some other proprietary API's we
use for load balancing and the potential is there to manage many large
SELECT's.

Greg


-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Chris Browne
Sent: Tuesday, September 20, 2005 10:15 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] postgresql cluster on SAN

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: postgresql cluster on SAN
  2005-09-23 13:07 Re: postgresql cluster on SAN Spiegelberg, Greg <gspiegelberg@cranel.com>
@ 2005-09-23 19:57 ` Chris Travers <chris@travelamericas.com>
  2005-09-27 03:43   ` Re: postgresql cluster on SAN Robert Ngo <robertngo@perridot.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Chris Travers @ 2005-09-23 19:57 UTC (permalink / raw)
  To: Spiegelberg, Greg <gspiegelberg@cranel.com>; pgsql-admin

Spiegelberg, Greg wrote:

>This is possible.  Below is a brief recipe.
>
>1. Use Veritas Storage Foundation HA with Cluster File System.
>2. Have a volume with cfs available to 2 or more systems via SAN.
>3. Install PostgreSQL and be sure PGDATA is on the cfs mount
>4. Create a Storage Checkpoint of cfs mount (read-write virtual copy)
>5. Bring up PostgreSQL on node1
>6. Mount storage checkpoint on node2
>7. Start PostgreSQL on node2
>
>Drawbacks?  Heck yes.  There are many and you should understand the
>nature of Veritas Storage Checkpoints before using.
>
Drawback #1-- only works read-only on all but one node.  And I am not 
even sure what will happen when you start writing on that one node...

>  I have tested it
>though in conjunction with dblink() and some other proprietary API's we
>use for load balancing and the potential is there to manage many large
>SELECT's.
>  
>
Ok, but what happens when you start doing inserts, updates, and deletes.

Somehow I think that PostgreSQL's dependance on shmem and semaphores 
will cause corruption in your database as soon as you start writing to 
it on both nodes.  And I don't know enough about PostgreSQL's internal 
structure to know if writing on only one node is safe.

Best Wishes,
Chris Travers
Metatron Technology Consulting



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: postgresql cluster on SAN
  2005-09-23 13:07 Re: postgresql cluster on SAN Spiegelberg, Greg <gspiegelberg@cranel.com>
  2005-09-23 19:57 ` Re: postgresql cluster on SAN Chris Travers <chris@travelamericas.com>
@ 2005-09-27 03:43   ` Robert Ngo <robertngo@perridot.com>
  2005-09-27 04:20     ` Re: postgresql cluster on SAN Chris Browne <cbbrowne@acm.org>
  0 siblings, 1 reply; 12+ messages in thread

From: Robert Ngo @ 2005-09-27 03:43 UTC (permalink / raw)
  To: pgsql-admin

How about i have one SAN storage for the slony master node and another
SAN to be share by slave nodes that only do SELECT?

Can Dell|EMC AX100i iscsi san with sata drive serve as storage for 
postgresql server, will there be problem in I/O performance?

Chris Travers wrote:

> Spiegelberg, Greg wrote:
>
>> This is possible.  Below is a brief recipe.
>>
>> 1. Use Veritas Storage Foundation HA with Cluster File System.
>> 2. Have a volume with cfs available to 2 or more systems via SAN.
>> 3. Install PostgreSQL and be sure PGDATA is on the cfs mount
>> 4. Create a Storage Checkpoint of cfs mount (read-write virtual copy)
>> 5. Bring up PostgreSQL on node1
>> 6. Mount storage checkpoint on node2
>> 7. Start PostgreSQL on node2
>>
>> Drawbacks?  Heck yes.  There are many and you should understand the
>> nature of Veritas Storage Checkpoints before using.
>>
> Drawback #1-- only works read-only on all but one node.  And I am not 
> even sure what will happen when you start writing on that one node...
>
>>  I have tested it
>> though in conjunction with dblink() and some other proprietary API's we
>> use for load balancing and the potential is there to manage many large
>> SELECT's.
>>  
>>
> Ok, but what happens when you start doing inserts, updates, and deletes.
>
> Somehow I think that PostgreSQL's dependance on shmem and semaphores 
> will cause corruption in your database as soon as you start writing to 
> it on both nodes.  And I don't know enough about PostgreSQL's internal 
> structure to know if writing on only one node is safe.
>
> Best Wishes,
> Chris Travers
> Metatron Technology Consulting
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>
>





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: postgresql cluster on SAN
  2005-09-23 13:07 Re: postgresql cluster on SAN Spiegelberg, Greg <gspiegelberg@cranel.com>
  2005-09-23 19:57 ` Re: postgresql cluster on SAN Chris Travers <chris@travelamericas.com>
  2005-09-27 03:43   ` Re: postgresql cluster on SAN Robert Ngo <robertngo@perridot.com>
@ 2005-09-27 04:20     ` Chris Browne <cbbrowne@acm.org>
  0 siblings, 0 replies; 12+ messages in thread

From: Chris Browne @ 2005-09-27 04:20 UTC (permalink / raw)
  To: pgsql-admin

robertngo@perridot.com (Robert Ngo) writes:
> How about i have one SAN storage for the slony master node and
> another SAN to be share by slave nodes that only do SELECT?

That seems like a reasonable sort of idea...

> Can Dell|EMC AX100i iscsi san with sata drive serve as storage for
> postgresql server, will there be problem in I/O performance?

I would imagine that the SAN would provide better I/O performance than
having a couple of IDE drives; whether you'll have a "problem in I/O
performance" depends heavily on what kind of load the system will
experience.
-- 
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/rdbms.html
"Linux is only free if your time has no value."  -- Jamie Zawinski



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: postgresql cluster on SAN
@ 2005-09-29 13:15 Spiegelberg, Greg <gspiegelberg@cranel.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Spiegelberg, Greg @ 2005-09-29 13:15 UTC (permalink / raw)
  To: Chris Travers <chris@travelamericas.com>; pgsql-admin

Chris,

You're partially correct.  Technically, you'd only be able to keep one
"version" of the database even though both are writable.  No semaphore
or locking issues.  You may do INSERT's, UPDATES, etc on either copy or
both but in the end only one may be kept.  You may get around this with
synchronization software to keep both copies up to date.

We had a couple things in mind when we explored this setup.  First was
to be able to run 2 SELECT's in a join concurrently with dblink().  From
a "cache" database we'd start one SELECT INTO FROM dblink() in the
background, start the second in the background, wait for both to
complete then do the join on the 2 tables created.  Worked fairly well
for some JOINs but not all.

Another potential benefit was backups.  Have the original, create the
second, use Slony-I, PG Cluster, or some other synchronization software
so keep the two in step on the two node's and when you're ready for
backups break the sync process, and backup the "copy".  However, pg_dump
isn't that painful and PITR has evolved.

Greg

 

-----Original Message-----
From: Chris Travers [mailto:chris@travelamericas.com] 
Sent: Friday, September 23, 2005 3:58 PM
To: Spiegelberg, Greg; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] postgresql cluster on SAN

Spiegelberg, Greg wrote:

>This is possible.  Below is a brief recipe.
>
>1. Use Veritas Storage Foundation HA with Cluster File System.
>2. Have a volume with cfs available to 2 or more systems via SAN.
>3. Install PostgreSQL and be sure PGDATA is on the cfs mount
>4. Create a Storage Checkpoint of cfs mount (read-write virtual copy)
>5. Bring up PostgreSQL on node1
>6. Mount storage checkpoint on node2
>7. Start PostgreSQL on node2
>
>Drawbacks?  Heck yes.  There are many and you should understand the
>nature of Veritas Storage Checkpoints before using.
>
Drawback #1-- only works read-only on all but one node.  And I am not 
even sure what will happen when you start writing on that one node...

>  I have tested it
>though in conjunction with dblink() and some other proprietary API's we
>use for load balancing and the potential is there to manage many large
>SELECT's.
>  
>
Ok, but what happens when you start doing inserts, updates, and deletes.

Somehow I think that PostgreSQL's dependance on shmem and semaphores 
will cause corruption in your database as soon as you start writing to 
it on both nodes.  And I don't know enough about PostgreSQL's internal 
structure to know if writing on only one node is safe.

Best Wishes,
Chris Travers
Metatron Technology Consulting



^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2005-09-29 13:15 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-20 06:47 postgresql cluster on SAN Robert Ngo <robertngo@perridot.com>
2005-09-21 00:39 postgresql cluster on SAN Robert Ngo <robertngo@perridot.com>
2005-09-21 02:14 ` Chris Browne <cbbrowne@acm.org>
2005-09-21 05:37   ` Robert Ngo <robertngo@perridot.com>
2005-09-21 06:33     ` Chris Travers <chris@metatrontech.com>
2005-09-21 13:06     ` Chris Browne <cbbrowne@acm.org>
2005-09-21 07:19 Re: postgresql cluster on SAN Magnus Hagander <mha@sollentuna.net>
2005-09-23 13:07 Re: postgresql cluster on SAN Spiegelberg, Greg <gspiegelberg@cranel.com>
2005-09-23 19:57 ` Chris Travers <chris@travelamericas.com>
2005-09-27 03:43   ` Robert Ngo <robertngo@perridot.com>
2005-09-27 04:20     ` Chris Browne <cbbrowne@acm.org>
2005-09-29 13:15 Re: postgresql cluster on SAN Spiegelberg, Greg <gspiegelberg@cranel.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox