PostgreSQL 9.4 High Availability – Best Practices

postgresql-9.4replication

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!

Best Answer

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).

Related Question