Thread: Master-master replication with PostgreSQL

Hello

We're looking for an open-source database solution that has a Python interface and will do master-master replication. My client has a flawed MS SQL Server system, with the replication hand-coded in Python. They don't want to pay very high licence fees for the MS SQL Server Enterprise version at 20 sites across the UK.

There is one central server with 19 branches. Some tables need to replicate from the central server to the branches. Other tables are centralised from the branches into one totalling table at the centre. A few tables need to replicate in both directions.

From what I've read, Slony-I does only master-slave replication and Slony-II is not being actively developed. Is this right? Are there any viable master-master replication tools for PostgreSQL. (They could be commercial/paid for if necessary.)

Rob

El mié, 16-04-2008 a las 12:13 +0100, Rob Collins escribió:
> Hello
>
> We're looking for an open-source database solution that has a Python
> interface and will do master-master replication. My client has a
> flawed MS SQL Server system, with the replication hand-coded in
> Python. They don't want to pay very high licence fees for the MS SQL
> Server Enterprise version at 20 sites across the UK.
>
> There is one central server with 19 branches. Some tables need to
> replicate from the central server to the branches. Other tables are
> centralised from the branches into one totalling table at the centre.
> A few tables need to replicate in both directions.
>
> From what I've read, Slony-I does only master-slave replication and
> Slony-II is not being actively developed. Is this right? Are there any
> viable master-master replication tools for PostgreSQL. (They could be
> commercial/paid for if necessary.)
>
> Rob

Try with bucardo ("http://bucardo.org/") may be help you :).

Regards,

Julio Cesar Sánchez González.
www.sistemasyconectividad.com.mx
knowhow@sistemasyconectividad.com.mx

Hi,

Le mercredi 16 avril 2008, Rob Collins a écrit :
> There is one central server with 19 branches. Some tables need to replicate
> from the central server to the branches. Other tables are centralised from
> the branches into one totalling table at the centre. A few tables need to
> replicate in both directions.

I'm working on some projects here with this very need (and same scale), and I
plan to use londiste (master/slaves asynchronous solution) replication
solution, which I already use in production on some other project.

The fact is that we want the "branches" to still be fully available in case of
network or central server failure, so we don't buy into synchronous
replication; which is not available yet into PostgreSQL as far as I know,
even if one of the basics building-block alternatives is provided into -core,
namely Two Phase Commit.
  http://www.postgresql.org/docs/8.3/static/sql-prepare-transaction.html

You'll find londiste documentation at both following places, the latter is
up-to-date with last 2.1.6 release, the former I'm not sure about it.
  http://skytools.projects.postgresql.org/doc/
  http://pgsql.tapoueh.org/skytools/

Hope this helps, regards,
--
dim

Rob Collins escribió:
> Hello
>
> We're looking for an open-source database solution that has a Python
> interface and will do master-master replication. My client has a
> flawed MS SQL Server system, with the replication hand-coded in
> Python. They don't want to pay very high licence fees for the MS SQL
> Server Enterprise version at 20 sites across the UK.
>
> There is one central server with 19 branches. Some tables need to
> replicate from the central server to the branches. Other tables are
> centralised from the branches into one totalling table at the centre.
> A few tables need to replicate in both directions.
>
> From what I've read, Slony-I does only master-slave replication and
> Slony-II is not being actively developed. Is this right? Are there any
> viable master-master replication tools for PostgreSQL. (They could be
> commercial/paid for if necessary.)
>
> Rob
I am testing bucardo (http://www.bucardo.org) in star replication with
one office at Canada and 3 branches in different countries and it's
working really nice. It's not yet in production, but my test show that
it's really good for star replication as you need.

BTW, my replication is bi-directional and all the branches syncronized
with the others.

Rodrigo

Hello Dimitri

To clarify the requirement: much like you, we're not looking for synchronous replication, which would be too slow. The branches and central server can be different for about 5 to 10 minutes. But the branches need to be able to function independently if the network or central goes down. Londiste looks interesting, though the documentation seems a bit sparse. Is it really that simple to set up, or is there a fair amount of trial and error in the setup process?

Best wishes

Rob

On Apr 16, 2008, at 8:47 AM, Julio Cesar Sánchez González wrote:
>> From what I've read, Slony-I does only master-slave replication and
>> Slony-II is not being actively developed. Is this right? Are there
>> any
>> viable master-master replication tools for PostgreSQL. (They could be
>> commercial/paid for if necessary.)
>>
>> Rob
>
> Try with bucardo ("http://bucardo.org/") may be help you :).

Doesn't bucardo handle conflicts with "whichever one I apply last
wins"?  That doesn't seem safe in all situations (or any, IMO).

Julio Cesar Sánchez González wrote:
>
> El mié, 16-04-2008 a las 12:13 +0100, Rob Collins escribió:

> > We're looking for an open-source database solution that has a Python
> > interface and will do master-master replication. My client has a
> > flawed MS SQL Server system, with the replication hand-coded in
> > Python. They don't want to pay very high licence fees for the MS SQL
> > Server Enterprise version at 20 sites across the UK.
> >
> > There is one central server with 19 branches. Some tables need to
> > replicate from the central server to the branches.
>
> Try with bucardo ("http://bucardo.org/") may be help you :).

Unfortunately, Bucardo only seems to work with 2 masters -- you can't
have 20.

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

On Apr 16, 2008, at 8:44 AM, Rob Collins wrote:
> Hello Dimitri
>
> To clarify the requirement: much like you, we're not looking for
> synchronous replication, which would be too slow. The branches and
> central server can be different for about 5 to 10 minutes. But the
> branches need to be able to function independently if the network or
> central goes down. Londiste looks interesting, though the
> documentation seems a bit sparse. Is it really that simple to set
> up, or is there a fair amount of trial and error in the setup process?

Yes, it really is pretty simple to set up.  If you give it a shot and
run up against any issues or find something unclear, please, offer
suggestions or patches on the skytools mailing list.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Hi,

Le mercredi 16 avril 2008, Rob Collins a écrit :
> To clarify the requirement: much like you, we're not looking for
> synchronous replication, which would be too slow. The branches and central
> server can be different for about 5 to 10 minutes. But the branches need to
> be able to function independently if the network or central goes down.

Exactly my need, with the addition that central edited tables and branches
local edited tables are disjoint sets, which makes the point for master/slave
replication choice.
I'm experiencing 1.5s and 4.8s average lag on my production setup, with
respectively 20tps and 200tps (insert/update/delete only).

> Londiste looks interesting, though the documentation seems a bit sparse. Is
> it really that simple to set up, or is there a fair amount of trial and
> error in the setup process?

I had the same question some time ago when I wanted to choose between londiste
and other solutions, and came to write up a part of the current
documentation.
In my experience, londiste is really that easy to set up, the only trial and
errors I've had where either bad reading of the doc (forgot to launch pgq
daemon, for example) or bad psycopg version usage.

In my case, but several people on skytools mailing-list have different
experience, I had to stick with psycopg 1 and avoid 2.0.5 (debian stable
version). YMMV.

I want to add skytools-users@pgfoundry.org mailing list is pretty responsive,
don't hesitate asking there :)

Regards,
--
dim

Hello

http://www.postgresql.at/english/pr_cybercluster_e.html

didn't test it myself though

Sincerely

Dragan

Rob Collins wrote:

Hello Dimitri

To clarify the requirement: much like you, we're not looking for synchronous replication, which would be too slow. The branches and central server can be different for about 5 to 10 minutes. But the branches need to be able to function independently if the network or central goes down. Londiste looks interesting, though the documentation seems a bit sparse. Is it really that simple to set up, or is there a fair amount of trial and error in the setup process?

Best wishes

Rob

On 16/04/2008, Dimitri Fontaine <dfontaine@hi-media.com> wrote:

Hi,

Le mercredi 16 avril 2008, Rob Collins a écrit :

> There is one central server with 19 branches. Some tables need to replicate
> from the central server to the branches. Other tables are centralised from
> the branches into one totalling table at the centre. A few tables need to
> replicate in both directions.


I'm working on some projects here with this very need (and same scale), and I
plan to use londiste (master/slaves asynchronous solution) replication
solution, which I already use in production on some other project.

The fact is that we want the "branches" to still be fully available in case of
network or central server failure, so we don't buy into synchronous
replication; which is not available yet into PostgreSQL as far as I know,
even if one of the basics building-block alternatives is provided into -core,
namely Two Phase Commit.
  http://www.postgresql.org/docs/8.3/static/sql-prepare-transaction.html

You'll find londiste documentation at both following places, the latter is
up-to-date with last 2.1.6 release, the former I'm not sure about it.
  http://skytools.projects.postgresql.org/doc/
  http://pgsql.tapoueh.org/skytools/

Hope this helps, regards,

--
dim

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

>> Try with bucardo ("http://bucardo.org/") may be help you :).

> Doesn't bucardo handle conflicts with "whichever one I apply last
> wins"?  That doesn't seem safe in all situations (or any, IMO).

With Bucardo, conflict handling is chosen as a standard method
(of which one is "latest") or a custom handler. "Latest" has its
places, but the usual recommendation is to pick one of the servers
as the trusted source, or to write your own handler based on your
business logic.

http://www.bucardo.org/bucardo.html#BucardoConflictHandling

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200804161143
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkgGHogACgkQvJuQZxSWSshdAACg6ouoh909Ts1r40YwGscax2M1
y+gAoOQdOG7ToqnYNfVQ+3sS8ypBrpFS
=NC0p
-----END PGP SIGNATURE-----

Using star replication I have all the nodes as master....so n
master-master replication with the common DB as master to all the nodes
and all the nodes as master to the common DB

Alvaro Herrera escribió:
> Julio Cesar Sánchez González wrote:
>
>> El mié, 16-04-2008 a las 12:13 +0100, Rob Collins escribió:
>>
>
>
>>> We're looking for an open-source database solution that has a Python
>>> interface and will do master-master replication. My client has a
>>> flawed MS SQL Server system, with the replication hand-coded in
>>> Python. They don't want to pay very high licence fees for the MS SQL
>>> Server Enterprise version at 20 sites across the UK.
>>>
>>> There is one central server with 19 branches. Some tables need to
>>> replicate from the central server to the branches.
>>>
>> Try with bucardo ("http://bucardo.org/") may be help you :).
>>
>
> Unfortunately, Bucardo only seems to work with 2 masters -- you can't
> have 20.
>
>

Rodrigo Gonzalez escribió:
> Using star replication I have all the nodes as master....so n
> master-master replication with the common DB as master to all the nodes
> and all the nodes as master to the common DB

Oh, so the Bucardo docs are incorrect.

http://www.bucardo.org/bucardo.html#BucardoLimitations



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

Yes and no....

bucardo cannot handle master-master-master

What I am doing is

masterA-masterB
masterA-masterC
masterA-masterD

That is something that bucardo can handle and allow me to replicate
masterB-masterC but everything goes thru masterA always

Alvaro Herrera escribió:
> Rodrigo Gonzalez escribió:
>
>> Using star replication I have all the nodes as master....so n
>> master-master replication with the common DB as master to all the nodes
>> and all the nodes as master to the common DB
>>
>
> Oh, so the Bucardo docs are incorrect.
>
> http://www.bucardo.org/bucardo.html#BucardoLimitations
>
>
>
>

Rodrigo Gonzalez escribió:
> Yes and no....
>
> bucardo cannot handle master-master-master
>
> What I am doing is
>
> masterA-masterB
> masterA-masterC
> masterA-masterD

Oh, I see -- makes sense.  Quite misleading docs, then.

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

Yes, basically there is something that is not there....makedelta is what
allow me to do that....I took that information from bucardo mailing list....

But the important thing is that is possible and maybe one day will be a
true grid multimaster replication system....right now I didnt find
anything better that work with pgsql....

Alvaro Herrera escribió:
> Rodrigo Gonzalez escribió:
>
>> Yes and no....
>>
>> bucardo cannot handle master-master-master
>>
>> What I am doing is
>>
>> masterA-masterB
>> masterA-masterC
>> masterA-masterD
>>
>
> Oh, I see -- makes sense.  Quite misleading docs, then.
>
>

On Wed, 16 Apr 2008, Rob Collins wrote:

> My client has a flawed MS SQL Server system

There's another kind?

> From what I've read, Slony-I does only master-slave replication and
> Slony-II is not being actively developed. Is this right? Are there any
> viable master-master replication tools for PostgreSQL.

Your later comments suggest you specifically need an asynchronous
multi-master system, which rules out several suggestions.

I've been collecting information on this topic at
http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling
which you might find useful.  Nothing jumps out at me as being more
appropriate for the situation you describe than Bucardo.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Thanks everyone for your very helpful comments. I'm setting up some spikes to check performance and functionality of various alternatives.

Rob