1

I setup Postgres Master to master replication using bucardo a few days back. It was working good till yesterday until I found an extremely rare issue. The issue was as follows. one table has auto-generated primary key. Someone Inserted a record in Master-1 Table. At the same time someone I inserted a record in Master-2 Table. I think both got the same ID and at last both Master had data which was inserted on Master 2.

Bucardo works on basis of triggers. If Master-1 data was inserted first then it should be replicated on Master-2 as well and Master-2 should have inserted Data with 1 incremented ID. I thought a lot but didn't find any satisfactory answer.

How can we avoid these type of situations?

YogeshR
  • 1,606
  • 2
  • 22
  • 43
  • 2
    Any asynchronous multi-master system can have such conflicts. You need sequences that are non-overlapping, e.g. use sequences that move in steps of 10, and offset them so node 1 generates 1, 11, 21, etc, node 2 generates 2, 12, 22, ... and so on. There are other issues with concurrent updates etc that are not so easily dealt with. The BDR documentation (a different multi-master tool for PostgreSQL) discusses conflicts a bit. – Craig Ringer Oct 06 '17 at 12:59

0 Answers0