I currently have a master postgresql 9.4 server containing many databases on Ubuntu 14.04.
I tried to use Barman to setup backup, but would like to set up streaming replica so that when the master goes down, I can promote the standby replica to be the new master. I looked in to repmgr, but this will only produce a read-only hot standby server. Does this mean I should go with multi-master solution if I want to promote standby to accept write transaction as well?
I am envision the system to be
M1 (master) -------------via ???-------------- S1 (standby/another master)
| |
|via Barman | via Barman
| |
B1 (backup server#1) B2 (backup server#2)
Ideally, after the M1 goes down, I can promote S1 to be the new master and it can accept read/write transaction not just read-only. Once we bring back M1, M1 can stays as standby but will streaming with S1.
Is postgres 9.4 BDR (Bi-Directional Replication) a good solution to stream between M1 and S1? Or is there any commercial product can do this?
I am not a DBA, and would really appreciate your opinions.
Thank you so much!
It sounds like standard streaming replication is what you're after.
In this scenario, you have a single master that takes all the writes, and then one or more read-only replicas which you can split the reads across to reduce load on the master.
If anything happens to the master database server, you can promote one of the replicas to master. Once a replica has been promoted to a master database it can then be written to.
Check out http://www.slideshare.net/jkshah/py-pg-day2013harep for more info on how these setups work (skip ahead to slide 21 for the postgresql stuff)