Hi Fabio!
Thanks for looking into this.
> 19 дек. 2019 г., в 17:14, Fabio Ugo Venchiarutti <f.venchiarutti@ocado.com> написал(а):
>
>
> 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,
bothyour 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
allthe standbys listed in the above setting in order to correctly determine which one has the highest WAL location
withoutrunning 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_nameson the master whenever a failover occurs. That allows for a simpler failover mechanism as you
knowthey got the latest WAL location.
No, synchronous standby does not necessarily own latest WAL. It has WAL point no earlier than all commits acknowledged
toclient.
Thanks!
Best regards, Andrey Borodin.