Hi Fabio! Thanks for looking into this. > 19 дек. 2019 г., в 17:14, Fabio Ugo Venchiarutti <f.venchiarutti@xxxxxxxxx> написал(а): > > > You're hitting the CAP theorem ( https://en.wikipedia.org/wiki/CAP_theorem ) > > > You cannot do it with fewer than 3 nodes, as the moment you set your standby to synchronous to achieve consistency, both your nodes become single points of failure. We have 3 nodes, and the problem is reproducible with all standbys being synchronous. > With 3 or more nodes you can perform what is called a quorum write against ( floor(<total_nodes> / 2) + 1 ) nodes . The problem seems to be reproducible in quorum commit too. > With 3+ nodes, the "easy" strategy is to set a <quorum - 1> number of standby nodes in synchronous_standby_names ( https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES ) > > > This however makes it tricky to pick the correct standby for promotions during auto-failovers, as you need to freeze all the standbys listed in the above setting in order to correctly determine which one has the highest WAL location without running into race conditions (as the operation is non-atomic, stateful and sticky). After promotion of any standby we still can commit to old primary with the combination of cancel and retry. > I personally prefer to designate a fixed synchronous set at setup time and automatically set a static synchronous_standby_names on the master whenever a failover occurs. That allows for a simpler failover mechanism as you know they got the latest WAL location. No, synchronous standby does not necessarily own latest WAL. It has WAL point no earlier than all commits acknowledged to client. Thanks! Best regards, Andrey Borodin.
