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!