Обсуждение: postgresql cluster on SAN
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?
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
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
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 > >
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
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