Thread: PostgreSql replication and load balancing ( is Slony-I a solution?)

Hello,
We're building database system with replication. Slony-I seems to be a quite
good solution for the replication, but beside the replication
(master-to-multiple slaves), we need load balancing aswell - multiple users
will access the database at the same time=multiple queries.
Is Slony-I capable of load balancing and how to set it up? We searched the
web and some people mentioned that Slony-I could do load balancing, but
haven't found how to make Slony-I to do it.

Simple applications like pgpool could do load balancing, too, but they seem
to be too simple to be used as replication system.

Thanks for any help.

J.

On 5/1/07, Jan Bilek <bilekj@gmail.com> wrote:
> Is Slony-I capable of load balancing and how to set it up? We searched the
> web and some people mentioned that Slony-I could do load balancing, but
> haven't found how to make Slony-I to do it.

Slony does not do load balancing. Personally, I recommend doing the
load balancing in the application if possible; you will need to funnel
updates to the master and distribute queries to the slaves, and the
app is in the best position to determine whether something is an
update or a query. Any intermediate middleware is going to add
additional latency and overhead and will add just another single point
of failure to an already complex system.

Alexander.

> On 5/1/07, Jan Bilek <bilekj@gmail.com> wrote:
>> Is Slony-I capable of load balancing and how to set it up? We searched
>> the
>> web and some people mentioned that Slony-I could do load balancing, but
>> haven't found how to make Slony-I to do it.
>
> Slony does not do load balancing. Personally, I recommend doing the
> load balancing in the application if possible; you will need to funnel
> updates to the master and distribute queries to the slaves, and the
> app is in the best position to determine whether something is an
> update or a query. Any intermediate middleware is going to add
> additional latency and overhead and will add just another single point
> of failure to an already complex system.
>
> Alexander.

Thanks for info.

Alexander Staubo wrote:
> On 5/1/07, Jan Bilek <bilekj@gmail.com> wrote:
>> Is Slony-I capable of load balancing and how to set it up? We
>> searched the
>> web and some people mentioned that Slony-I could do load balancing, but
>> haven't found how to make Slony-I to do it.
>
You might want to check pgcluster out
http://pgcluster.projects.postgresql.org/ witch does both.

On 5/2/07, Jamie Deppeler <jamie@doitonce.net.au> wrote:
> You might want to check pgcluster out
> http://pgcluster.projects.postgresql.org/ witch does both.

That page will give you the impression that this project is dead and
abandoned -- the last update is from early 2005. PGCluster does seem
to be active on PgFoundry:

  http://pgfoundry.org/projects/pgcluster/

Mind you, PGCluster is synchronous replication. For updates it scales
even worse than Slony, since every update has to wait for all masters
to complete.

Alexander.

Alexander Staubo wrote:
> On 5/2/07, Jamie Deppeler <jamie@doitonce.net.au> wrote:
> >You might want to check pgcluster out
> >http://pgcluster.projects.postgresql.org/ witch does both.
>
> That page will give you the impression that this project is dead and
> abandoned -- the last update is from early 2005. PGCluster does seem
> to be active on PgFoundry:
>
>  http://pgfoundry.org/projects/pgcluster/

Huh, that page (the one Jamie linked to) _is_ pgfoundry's "project page."

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On 5/2/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Alexander Staubo wrote:
> > On 5/2/07, Jamie Deppeler <jamie@doitonce.net.au> wrote:
> > >You might want to check pgcluster out
> > >http://pgcluster.projects.postgresql.org/ witch does both.
> >
> > That page will give you the impression that this project is dead and
> > abandoned -- the last update is from early 2005. PGCluster does seem
> > to be active on PgFoundry:
> >
> >  http://pgfoundry.org/projects/pgcluster/
>
> Huh, that page (the one Jamie linked to) _is_ pgfoundry's "project page."

I don't know what's going on here, but:

$ curl -s http://pgcluster.projects.postgresql.org/ | md5
dad4aaf6659f1a65f228cee1ec71eba4
$ curl -s http://pgfoundry.org/projects/pgcluster/ | md5
3c6645ab3bbffa4e9f77a1ccab6a663a

They're different pages. The first one is horribly out of date;
unfortunately, it is (for me) the first hit on Google, whereas the
PgFoundry project page is the third.

Alexander.

Alexander Staubo wrote:
> On 5/2/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> >Alexander Staubo wrote:
> >> On 5/2/07, Jamie Deppeler <jamie@doitonce.net.au> wrote:
> >> >You might want to check pgcluster out
> >> >http://pgcluster.projects.postgresql.org/ witch does both.
> >>
> >> That page will give you the impression that this project is dead and
> >> abandoned -- the last update is from early 2005. PGCluster does seem
> >> to be active on PgFoundry:
> >>
> >>  http://pgfoundry.org/projects/pgcluster/
> >
> >Huh, that page (the one Jamie linked to) _is_ pgfoundry's "project page."
>
> I don't know what's going on here, but:
>
> $ curl -s http://pgcluster.projects.postgresql.org/ | md5
> dad4aaf6659f1a65f228cee1ec71eba4
> $ curl -s http://pgfoundry.org/projects/pgcluster/ | md5
> 3c6645ab3bbffa4e9f77a1ccab6a663a
>
> They're different pages. The first one is horribly out of date;
> unfortunately, it is (for me) the first hit on Google, whereas the
> PgFoundry project page is the third.

Sure, they are different pages, but the first one is supposed to be
maintained by the pgcluster guys.  It is linked from the second page,
and it is hosted in pgfoundry.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On 5/2/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> > They're different pages. The first one is horribly out of date;
> > unfortunately, it is (for me) the first hit on Google, whereas the
> > PgFoundry project page is the third.
>
> Sure, they are different pages, but the first one is supposed to be
> maintained by the pgcluster guys.  It is linked from the second page,
> and it is hosted in pgfoundry.

My entire point was that the first one *isn't* maintained. So avoid it
if you're looking for up-to-date information about this project.

Alexander.

Alexander Staubo wrote:
> On 5/2/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> >> They're different pages. The first one is horribly out of date;
> >> unfortunately, it is (for me) the first hit on Google, whereas the
> >> PgFoundry project page is the third.
> >
> >Sure, they are different pages, but the first one is supposed to be
> >maintained by the pgcluster guys.  It is linked from the second page,
> >and it is hosted in pgfoundry.
>
> My entire point was that the first one *isn't* maintained. So avoid it
> if you're looking for up-to-date information about this project.

My entire point was that that page is also on pgfoundry :-)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support