Re: Clustering & Load Balancing & Replication

Thanks Bruce... just read it, still running 8.1 so didnt see 8.2
documentation.
Nice to know my work has not been duplicated... quote from
http://www.postgresql.org/docs/8.2/static/high-availability.html

"PostgreSQL does not offer this type of replication, though PostgreSQL
two-phase commit (PREPARE TRANSACTION and COMMIT PREPARED) can be used to
implement this in application code or middleware"

Suggest you download my little application and read the documentation,
you'll see its very different, maybe even interesting.
Maybe they should change that to.... Postgres DOES HAVE a free multi-master
replication system :)

One comment they make.... "Heavy write activity can cause excessive locking,
leading to poor performance. In fact, write performance is often worse than
that of a single server. Read requests can be sent to any server."
I'm not sure I agree with that... or maybe MVCC is just fantastic.... I
tested it.
The 2 phase commit locking is definitely happening at record level, so only
if the multimasters all hit the same record is there the potential for lock
conflict.
Why will dB's being randomly used, hit the same records, I think its a low
probability to begin with?
Not happy with that, I wrote a multithreaded routine and got them to all
smack the same record, it NEVER ROLLED BACK, and if there is performance
degradation, I didnt notice it... again probably a testament to the MVCC
design.
In any event if you look at the documentation, you'll see SPAR is not
multimaster or nothing. Can use say one server in an office and another to
pump data to a remote web site... not sure if you would even call that
multimaster, thats the point, I'm not sure SPAR fits any pure theory
category.

Anyway have a look, nice thing is you can play with it as well... becomes a
pragmatic discussion.
http://spar.orgfree.com/index.html

Now that I got replication going, I need to make a site mirror which I'll
piggy back on the replication... I'll dump it on the site as well when its
done... if you want it.
Then I'll probably be so "off topic"... I'll have to join another mailing
list :)

Before I go... just want to formally thank the Postgres community...
It may not be everything, and its easy to focus on what it doesnt do yet...
but what you have done is amazing, and what you have got, is fantastic.
I will use nothing else....
Thanks, Johnny

----- Original Message -----
From: "Bruce Momjian" <bruce(at)momjian(dot)us>
To: <org(at)kewlstuff(dot)co(dot)za>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Saturday, December 23, 2006 6:34 PM
Subject: Re: [GENERAL] Clustering & Load Balancing & Replication

>
> I assume you have read this new documentation for 8.2:
>
> http://www.postgresql.org/docs/8.2/static/high-availability.html
>
> ---------------------------------------------------------------------------
>
> org(at)kewlstuff(dot)co(dot)za wrote:
>> >Hi,
>> >
>> >I am currently investigating the preferred method of clustering a
>> >postgresql database on Redhat?
>> >
>> >i would really appreciate some suggestions or experiences you guys have
>> >had.
>> >
>> >note: performance & redundancy are both equally desirable and i have
>> >plenty of resources.
>> >
>> >we already have licences for Redhat Enterprise Linux ES v4, i was
>> >wondering if Redhat's >Cluster Manager is a candidate? ~ or if either
>> >PGCluster or Slony1 have >advantages or drawbacks etc.
>>
>> Couple of months ago, when I started looking at replication, and
>> clustering I saw this as a fairly basic question, but the more I got into
>> it the more I realized there's clustering, clustering and clustering, and
>> replication, replication and replication.
>> I dont think anyone can give you a prefered method, not without a system
>> spec.
>>
>> I looked at Slony, even tried to help debug it on windows, and eventually
>> realised its Master Slave arrangement would not work for me.
>> I looked at dBMirror, which I liked in concept but I didnt fancy the way
>> the code was done in a project that seems to be dead.
>> Then I looked at some really weird and wonderful schemes that work on a
>> core technology like a reliable message queue, not for me.
>>
>> In the end I decided I wanted real time synchronous multi master
>> replication, without conflict resolution, and that I didnt really want a
>> dBCluster, I needed to cluster and load balance at web level, allowing
>> for things like session management, multiple clusters, and remote sites.
>>
>> Bad news is I couldnt find it, not for free anyway.
>>
>> If you dont find a solution, try http://spar.orgfree.com/index.html
>> Please keep in mind, its new and written by a novice out of desparation,
>> not one of the community super geeks, but it takes a very pragmatic
>> approach to replication and maybe just what the doctor ordered.... Good
>> Luck.
>>
>
> --
> Bruce Momjian bruce(at)momjian(dot)us
> EnterpriseDB http://www.enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>