Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
1  
Thanks for the answer. Based on your answer and some research I decided to go another way. In the scenario I described above, I will not make the change directly on the database of slave A, the slave will make this change on the master database (by connecting to the master database on 5432 port) and after that the replication will occur and only after the replication slave A and the other slaves will be able to see the change on its own database. –  Alptugay Aug 29 '13 at 15:14
    
One option youd have would be to have local writes go into log tables that could be checked by an external process and written back to the master. This would give you some downtime protection but it could require changes to how your application queries data in order to make it effective. –  Chris Travers Aug 30 '13 at 0:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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