Re: Master-Master replication

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.