Synchronous vs Asynchronous

[rez0n]

Hi there,
I choosing between synchronous or asynchronous mode of 3 node cluster (+2 external etcd) for a low count of writes database (it can be represented as typical e-commerce website)
I need maximum stability (writes & reads) and performance (reads)

I've tested asynchronous mode by making few inserts and disconnecting network of primary node but not faced with any data loss. I still unsure, how much risk of data loss in practical point of view, it becomes critical on high level of writes or it's random?

In addition, I not expect frequent failovering by real outage, most of them should to be due software issues or reboot (I have issue in my testing lab with random reboots by watchdog, most probably because I reduced patroni_ttl without proper understanding what is it for)

Thanks in advance.

[vitabaks]

Hi @rez0n

It’s good that there is no requirement for write speed in favor of data safety.
To minimize the risk of losing data on autofailover, you can configure settings in the following way:

• synchronous_mode: 'true'
• synchronous_mode_strict: 'true'
• synchronous_commit: 'remote_apply'

And, if you only have two replicas:

• synchronous_node_count: 1

For more confidence, it is desirable:

• synchronous_node_count: 2

but this is possible if you have at least 3 replica servers in the Cluster since writes will not be possible if less than 2 replica servers are available.

I recommend using synchronous replication in a production environment if possible.

Details:

• https://patroni.readthedocs.io/en/latest/replication_modes.html
• https://postgresqlco.nf/doc/en/param/synchronous_commit/

[rez0n]

Hi @vitabaks
Thanks for sharing your thoughts.
Could you please advice me, how (according Patroni docs looks like it is possible) to change running cluster mode sync>async (and vice versa)

I need just update /etc/patroni/patroni.yml on all nodes and restart everything or maybe it is possible to do using playbook?

[vitabaks]

there is no need to do this manually as the configuration change is automated.

1. Change the values of variables in vars/main.yml
2. Perform playbook config_pgcluster.yml

Example:

ansible-playbook config_pgcluster.yml --tags patroni_conf