Re: Actual meaning from pg_stat_replication.sync_state potential vs. sync and the usage of synchronous_standby_names

[Karen Jex <karenjex@xxxxxxxxxxx>]

On 09 September 2020 11:12 Dirk Krautschick <Dirk.Krautschick@xxxxxxxxxxxx> wrote:

my goal is to create a replication cluster with several nodes and all guaranteed in sync to provide

read only load balancing with exact the same set of data over all nodes.

 

Basically a pretty well known use case so I set the value for synchronous_standby_names to all

nodes. Maybe with a * or just a list of all nodes.

 

Now I see in the pg_stat_replication that mostly not all nodes are in sync_state = sync but mostly

in potential. What does potential exactly (need a reliable answer) mean? I would assume that in

this case the WAL information is already streamed to the other node but not applied yet. From

a DR perspective fine but not for my scenario.

Potential in this case means that it is in the list of standbys that are available to respond to a synchronous commit request but that it is not the highest priority member of the list

So I set the parameter on all nodes synchronous_commit to remote_apply to make sure that every nodes has

to be committed to finish a transaction.

 

But even then it has the same effect.

This is expected; the synchronous_commit parameter tells Postgres when it can acknowledge commit of a transaction, not how many standbys it must wait for.

But then the weird part….if I set the synchronous_standby_names to something like FIRST 2 ( …list of nodes)

then I reach the state that every node is in sync. So I have to use this way of declaration even if I list every

node in there. Just to say all nodes won’t work. Bug, Feature, works as designed? J

The behaviour you describe here is exactly as expected, assuming you have 2 standbys. The "2" in your synchronous_standby_nodes setting is "num_sync", ie the number of synchronous standbys that transactions need to wait for replies from.

If you do not include this, the Postgres interprets your request as "I want to be sure that my transaction has committed on at least one of the standbys in the following list"

The FIRST or ANY just tells Postgres in which order it should consider the standbys that you list.

Best Regards,

Karen JEX