Re: Asynchronous replication in postgresql

On 3/11/15 5:27 AM, Deole, Pushkar (Pushkar) wrote:
> Hi,
>
> I am new to postgresql and evaluating the streaming replication for my
> use case. My use case is:
>
> 1.Need to replicate data from primary database (master) to secondary
> database (slave) asynchronously.
>
> 2.If master goes down, the slave should automatically be promoted to
> master.
>
> 3.Later, when the original primary server (original master) is brought
> up again, it should obtain back its master role and the new master
> should assume the slave again as it was with original setup.
>
> For #1, the streaming replication of postgresql is good enough.
>
> For #2, we need to create the trigger file. How can we do this
> automatically?

You'll need to use something else to figure out that the master node
died. But that's not the big problem... the big problem is you need to
be careful to ensure you don't get into a 'split brain' state.

Say you promoted the slave, so now it's responding to all queries. Now
the master suddenly starts. Not only is the master missing data that's
been written to the slave, but if you have a load balancer now *both*
databases are acting as if they're the master.

That's bad. :)

Typically, you want some way to "Shoot The Other Node In The Head"
before you promote the slave. For example, you could modify the
configuration of something in your network so it's no longer possible to
reach the old master.

> For #3, this seems to be quite complicated. Is this even possible using
> streaming replication? If yes, how can this be achieved?

You basically need to replace the master with a new replica built off
the new master. There's been some recent work to make this easier/faster
to do, but it's not terribly trivial, and you have to be careful to do
it correctly.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com