3

From my understanding logical replication in Postgres does not replicate sequence data. Let's assume we have two multi-master postgres servers

server1
test_table
current sequence 100

server2
test_table
current sequence 90

If server1 goes down and server2 will take over for the writes, what would happen when it tries to insert a row with sequence of 90? Since server1 has already inserted a row that has an id of 90. What are some workarounds for this?

Arya
  • 8,473
  • 27
  • 105
  • 175

1 Answers1

1

setup the two sequences so that they do not clash.

eg use INCREMENT BY 2 on both sequences and have server 1 give odd numbers and server 2 give even numbers.

Jasen
  • 11,837
  • 2
  • 30
  • 48
  • thanks, that's a good option. Do you know if it's possible to have multi-master synchronization using physical replication? or my only choice would be logical replication if I want synchronous multi-master? – Arya May 02 '19 at 05:05
  • physical is only master-slave because it preserves order of operations. logical can be multi-master. – Jasen May 02 '19 at 05:07
  • What if we plan to have a 5 server cluster? what do you think of using UUID instead of sequence? – Arya May 02 '19 at 05:09
  • 1
    You cannot have multi-master replication with PostgreSQL logical replication (yet). Indeed it is usually best to avoid that anyway, since the ensuing replication conflicts will break any application that is not specifically designed for it. – Laurenz Albe May 02 '19 at 05:50
  • @LaurenzAlbe you are right, I was under the impression that it did support it, I have to look into active/standby failover – Arya May 02 '19 at 06:03
  • active/standby failover is physical (file) level again, single simultaneous master. – Jasen May 02 '19 at 22:01
  • to get multi-master you need to use logical (DML level) replication. I have not looked closely ar postgresql's built-in replication, with multi-master a way is needed for replication to not create feedback loops, else you get the software-laser effect. – Jasen May 02 '19 at 22:05