Thread: Postgres High Availablity Solution needed for hot-standby and load balancing

Hi

our customer has got the following requirements:

Req1) Master master replication supported, not only master / slave
replication with only the master being writable. If you do have multiple
slave systems they are only useful from a backup and standby
perspective.  Our Application must have a db-connection it can write to.

Req2) Replication of schema should also be possible, not only data

Req3) Not only a hot-standby-solution is needed. Load Balancing is
wanted for the future.


Currently I am looking at EnterpriseDB but it seems that they dont
support multiple master-replication

best regards
Ragnar

Have you looked at pgCluster or Continuent's uni/Cluster?

--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

On 30.11.2007, at 02:34, Usama Dar wrote:

> Have you looked at pgCluster

I project where the "latest news" page shows the newest entry from
March 2005 and the install talks only about PostgreSQL 8.0 isn't
really inspiring confidence ...

Continuent is very active, but it limits the servers to Linux, it
seems it doesn't work on BSD or Mac OS X. At least, these are not
listed on the product page for uni/cluster.

The lack of an integrated multi master clustering solution in
PostgreSQL is the only real downside I can see. For me it is better to
have something that is well integrated, functional and supported but
only fits the needs for about 80% of the people in need for a multi
master than having nothing and always pointing to very old or poor or
commercial commercial solutions.

But that is just my personal view on that. I know that a multi master
cluster is a very complex feature, but you can't always throw more
(the existing solutions need AFAIK at least four servers to be
redundant) or bigger hardware or expensive solutions if you only need
load balancing but your app needs to be able to write to whatever
server it is connected.

In that respect, I really like the solution in FrontBase, where you
can do multi master with two servers, schema synchronization is
included, you can connect or disconnect servers from the cluster at
any time, it is fully transparent for the application, you just add
more addresses to the JDBC connection string. This might not fit the
needs for a couple of users, but it fits for the vast majority.

Personally I can live without a multi master solution for PG at the
moment as I just use a different product if I need it and live with
the downsides of said product (cost for Oracle and similar, less
configuration options and lower performance with FrontBase, other
problems with other DMBS).

cug

Guido Neitzer wrote:
> On 30.11.2007, at 02:34, Usama Dar wrote:
>
>> Have you looked at pgCluster
>
> I project where the "latest news" page shows the newest entry from March
> 2005 and the install talks only about PostgreSQL 8.0 isn't really
> inspiring confidence ...

Although they aren't the fastest with releases, they really aren't as
bad as that.

You would be looking at http://pgcluster.projects.postgresql.org/ which
has been neglected for some time. You can find the most current source
downloads at pgfoundry http://pgfoundry.org/projects/pgcluster/ which
has the last release at May 2007. The project home is now at
http://www.pgcluster.org (but they seem to use pgfoundry to store the
release tarballs)

Also Cybertec appears to have branched off from pgcluster
http://www.postgresql.at/english/pr_cybercluster_e.html


--

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz

On 30.11.2007, at 12:50, Shane Ambler wrote:

>> I project where the "latest news" page shows the newest entry from
>> March 2005 and the install talks only about PostgreSQL 8.0 isn't
>> really inspiring confidence ...
>
> Although they aren't the fastest with releases, they really aren't
> as bad as that.

Nicely said ... ;-) Yeah, I was looking at the wrong site. Maybe there
should be a redirect to the new page to avoid that.

I have last looked at pgcluster back in 2005 when I was trying to find
an affordable multi master solution an the setup sounded so horrific
that we spent about 10k EUR to get a different solution that is
actually working fine now. Setting this up on just two machines was
about 10 minutes work ...

I hope the setup has changed to the better.

cug

WHich solution are you using now, Guido?

Has anyone made experiences with cybercluster? I am thankful to hear
comments
and especially comparision to other products

thanks
Ragnar


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Guido Neitzer
> Sent: Freitag, 30. November 2007 21:03
> To: Shane Ambler
> Cc: Postgresql General
> Subject: Re: [GENERAL] Postgres High Availablity Solution
> needed for hot-standby and load balancing
>
> On 30.11.2007, at 12:50, Shane Ambler wrote:
>
> >> I project where the "latest news" page shows the newest entry from
> >> March 2005 and the install talks only about PostgreSQL 8.0 isn't
> >> really inspiring confidence ...
> >
> > Although they aren't the fastest with releases, they really
> aren't as
> > bad as that.
>
> Nicely said ... ;-) Yeah, I was looking at the wrong site.
> Maybe there should be a redirect to the new page to avoid that.
>
> I have last looked at pgcluster back in 2005 when I was
> trying to find an affordable multi master solution an the
> setup sounded so horrific that we spent about 10k EUR to get
> a different solution that is actually working fine now.
> Setting this up on just two machines was about 10 minutes work ...
>
> I hope the setup has changed to the better.
>
> cug
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

On 01.12.2007, at 09:35, Ragnar Heil wrote:

> WHich solution are you using now, Guido?

For the one installation we needed multi-master, we have FrontBase
running.

PostgreSQL multi-master just wasn't "right" for us. Too much hardware
needed to get real redundancy, the setup is too complex and didn't
provide what we needed: multi-master replication with two machines for
real redundancy not really performance (which is good enough with one
simple, cheap server), load-balancing is done in the apps (we have
instances connecting to one or the other server), automatic failover
in case of one server goes down. If we want to bring it back up, it's
a matter of some really simple commands and it synchronizes itself
with the master, no copying of files, no complex scripting, no complex
procedures at all.

Performance of one PostgreSQL server would be faster compared to that
setup, at least for the load we have, as FrontBase seems to be getting
slower with inserting in large tables with complex indexes. PostgreSQL
index creation is way faster. There are also other annoying things we
have seen, but overall the solution works fine.

cug

Hi Usama

yes, currently I am reading a brochure about Continuent uni/cluster for
PostgreSQL. Looks quite interesting.

Another product sounds promising: Cybercluster from www.postgres.at
English Product Description:
http://www.postgresql.at/picts/download/dokumentation/documentation_cybe
rcluster.pdf

Anyone has made experiences with it?


thanks
Ragnar



________________________________

    From: Usama Dar [mailto:munir.usama@gmail.com]
    Sent: Freitag, 30. November 2007 10:35
    To: Ragnar Heil
    Cc: pgsql-general@postgresql.org
    Subject: Re: [GENERAL] Postgres High Availablity Solution needed
for hot-standby and load balancing




    On 11/29/07, Ragnar Heil <Ragnar.Heil@fast.no> wrote:

        Hi

        our customer has got the following requirements:

        Req1) Master master replication supported, not only
master / slave
        replication with only the master being writable. If you
do have multiple
        slave systems they are only useful from a backup and
standby
        perspective.  Our Application must have a db-connection
it can write to.

        Req2) Replication of schema should also be possible, not
only data

        Req3) Not only a hot-standby-solution is needed. Load
Balancing is
        wanted for the future.


        Currently I am looking at EnterpriseDB but it seems that
they dont
        support multiple master-replication

        best regards
        Ragnar



        ---------------------------(end of
broadcast)---------------------------
        TIP 4: Have you searched our list archives?

                       http://archives.postgresql.org/



    Have you looked at pgCluster or Continuent's uni/Cluster?

    --
    Usama Munir Dar http://linkedin.com/in/usamadar
    Consultant Architect
    Cell:+92 321 5020666
    Skype: usamadar