Re: postgresql cluster on SAN

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(at)travelamericas(dot)com]
Sent: Friday, September 23, 2005 3:58 PM
To: Spiegelberg, Greg; pgsql-admin(at)postgresql(dot)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