Re: Master-Master replication

Hi,

Can someone tell me what's the best option for master-master replication on
PostgreSQL?

On 10/09/2013 10:59 PM, Ricardo Esteves wrote:
> Hi,
>
>
>
> Can someone tell me what’s the best option for master-master replication
> on PostgreSQL?
>

There are none, from what I know. You would set up a master-slave
replication and then utilize external tools that would monitor state of
the master. If the master goes down a slave would become new master.
When the old-master comes back it would reseed itself from the working
master to become a new standby slave.

I recommend reading 'clusters from scratch' on the pacemaker website to
get an idea what is needed to safely determine 'master is down'
situation. (For instance, you can have a monit or similair tool on the
slave that would constantly 'ping' master, and when that tool detects
master is not responding, it will intitiate a 'failover' procedure. But,
the way check is made is crucial. You can't relay only on ping, as it
could be an issue with the slave's network that's preventing the ping.)

I first went with pacemaker to set up a high-availability solution for
postgres, but ditchet it as pacemaker seemed overly complicated for this
particular task.

I'm assuming you're comming from MySQL where master-master configuration
is common. Be aware, however, that even with MySQL you need to make sure
you're writing only on ONE master at the same time (as MySQL replication
is not synchroneous), so you're in essence having master-slave
(actually, you have master-slave in both directions - that configuration
simplifies failover/failback situations, which is, IMHO, the only
'benefit' MySQL currently has over PostgreSQL).

Mario

P.S. If you're comming from some other RDBMS I'd like to hear how is m-m
replication implemented there.

On Wed, Oct 9, 2013 at 1:59 PM, Ricardo Esteves <maverick(dot)pt(at)gmail(dot)com>wrote:

> Can someone tell me what’s the best option for master-master replication
> on PostgreSQL?
>

You might want to take a look at Bucardo, to see if it suits your needs.

http://bucardo.org/

--
:wq

On Wed, Oct 09, 2013 at 09:59:23PM +0100, Ricardo Esteves wrote:
> Can someone tell me what's the best option for master-master replication on
> PostgreSQL?

There is no "best", it depends on what your definition of master-master is,
for one thing. As mentioned, Bucardo is one option. pg-pool might be another
one, as well as rubyrep. They all have their pros and cons, so you would
need to explain your use case. Bucardo is probably the one closest to
the traditional MySQL-like master-master.

--
Greg Sabino Mullane greg(at)endpoint(dot)com
End Point Corporation
PGP Key: 0x14964AC8

Ok, basically i have a two VMs, one VIP controlled by Red Hat Cluster
Suite, so what i wan't is that both VMs have PostgreSQL replicated and
both can read/write, if VM1 goes down, the VIP is loaded on VM2 and all
operations keep going automatically.

-----Original Message-----
From: Greg Sabino Mullane <greg(at)endpoint(dot)com>
To: Ricardo Esteves <maverick(dot)pt(at)gmail(dot)com>
Cc: pgsql-cluster-hackers(at)postgresql(dot)org
Subject: Re: [pgsql-cluster-hackers] Master-Master replication
Date: Thu, 10 Oct 2013 10:44:17 -0400

On Wed, Oct 09, 2013 at 09:59:23PM +0100, Ricardo Esteves wrote:
> Can someone tell me what's the best option for master-master replication on
> PostgreSQL?

There is no "best", it depends on what your definition of master-master is,
for one thing. As mentioned, Bucardo is one option. pg-pool might be another
one, as well as rubyrep. They all have their pros and cons, so you would
need to explain your use case. Bucardo is probably the one closest to
the traditional MySQL-like master-master.