1

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!

Improve this question

2 Answers 2

Reset to default
0

It looks like you asked 2 questions:

Question 1

Does this mean I should go with multi-master solution if I want to promote standby to accept write transaction as well?

Built-in PostgreSQL features are enough to get a WAL-based, read-only standby (a.k.a. secondary, a.k.a. slave) server. However, they are not enough to get multi-master operation.

This R/O slave can be promoted at any time to standalone, R/W server by using pg_ctl.

pg_ctl promote -D /var/lib/postgresql/9.4/main

(note: pg_ctl tool might be hidden in default debian/ubuntu setup. Look in /usr/lib/postgresql/X.Y/bin to see it)

When it's promoted, replication stops and slave is disconnected from primary. See relevant fragments on promote command in pg_ctl documentation and failover docs.

Question 2

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 don't know BDR (maybe it's a good solution), but do you REALLY need both servers in R/W mode? If not, I strongly recommend using built-in streaming replication (with streaming or log shipping).

To redirect traffic from primary to standby you need some external tool which will do the failover procedure - using either dns-based or IP-based or other failover method. PostgreSQL itself does not know how to redirect traffic or do anything outside the database scope. Popular tools are pgpool (in layer 7) or Linux HA or corosync and friends (in lower layers).

Improve this answer
3
  • Thank you so much! I am not aware I can promote a R/O standby to R/W server by using pg_ctl. I cannot seems to find pg_ctl on Ubuntu 14.04. Is it replaced by pg_ctlcluster, if you know by any chance? Thanks again!
    – wolf97084
    Feb 25, 2015 at 14:22
  • Another question regarding pg_ctl promote, do I need to redirect traffic from primary to standby? How exactly do you do this without touching DNS entries at all? Thanks again.
    – wolf97084
    Feb 25, 2015 at 15:07
  • @wolf97084, you have to do this with external tools, PostgreSQL itself does not know how to redirect traffic and it does not do anything outside the database.
    – filiprem
    Mar 26, 2015 at 14:42
0

i use http://en.wikipedia.org/wiki/Corosync_%28project%29 and peacemaker. maybe http://clusterlabs.org/wiki/PgSQL_Replicated_Cluster can help you.

Improve this answer
4
  • Try not to just post links, per the site guidelines. Add descriptions / explanations of the content contained within.
    – LowlyDBA - John M
    Feb 24, 2015 at 21:54
  • 1
    i'm not allowed to comment the other answer. but ubuntu uses pg_ctlcluster as a wrapper-script. /usr/lib/postgresql/9.x/pg_ctl is not in the path by default.
    – maletin
    Mar 9, 2015 at 10:09
  • The restrictions are in place for a reason
    – LowlyDBA - John M
    Mar 9, 2015 at 12:37
  • thx @maletin I included your hints in my answer
    – filiprem
    Mar 26, 2015 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.