Postgresql – Replicating from master to slaves and from slaves to master

postgresqlreplicationslony

I have a master and multiple slave machines. All SQL operations on the master node should be sent to the slave nodes. But the tables on the slave nodes may also be written to locally. These local changes on the slave node should be reflected to the master node.

For example let's say I have a master machine, lets call it "M", and two slave machines lets call them "A" and "B"

All these machines have a table named test_table with a column named "id".

I insert data to the test_table on M machine.

(M)test_table
   |id|
   |1 |

(A)test_table
   |id|

(B)test_table
   |id|

now this change is reflected to the slaves:

(M)test_table
   |id|
   |1 |

(A)test_table
   |id|
   |1 |

(B)test_table
   |id|
   |1 |

Now on slave A, I make a local change.

(M)test_table
   |id|
   |1 |

(A)test_table
   |id|
   |2 |

(B)test_table
   |id|
   |1 |

Now this change is reflected to the master server:

(M)test_table
   |id|
   |2 |

(A)test_table
   |id|
   |2 |

(B)test_table
   |id|
   |1 |

And then master server replicates this change to the slaves:

(M)test_table
   |id|
   |2 |

(A)test_table
   |id|
   |2 |

(B)test_table
   |id|
   |2 |

What is this kind of replication named? And how can I achieve this on postgresql?

Best Answer

What you are looking for is multi-master replication. Multi-master replication has a large number of potential problems and consequently the best anything can offer is a toolkit rather than a guaranteed solution (imagine two concurrent updates on the same row on different servers).

Bucardo will do multi-master replication for two servers, but not three. You could probably play some fun tricks with Slony and table partitions but this ends up getting complex and is definitely expert territory.

My recommendation is avoid multi-master replication if at all possible. It is mathematically impossible to make it just work all the time without making it more failure prone than a single system would be. Try hard to design so that you don't need it.

If you do need it, please look at getting some professional help whether it is a second set of eyes or a consultant (who can also get another consultant to review the design). There is so much that can go wrong here that you really want additional eyes on the problem.

Related Question