Asynchronous replication in postgresql
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?
For #3, this seems to be quite complicated. Is this even possible using streaming replication? If yes, how can this be achieved?
Your help is greatly appreciated in this regard.
Thank you very much..
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I've recently open sourced this template for managing state for PostgreSQL:
https://github.com/compose/governor
Take a test drive around it. As long as the old Leader is verifiably dead
or stopped at the forked WAL log point, I've not had issues with inserting
a `recovery.conf` to tail the new Leader.
On Tue, Apr 7, 2015 at 5:16 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
Show quoted text
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--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general