Patroni replication behaves the same regardless of the value set for synchronous_mode_strict

PostgreSQL Percona Operator for PostgreSQL
1

Percona Operator Version: 2.8.2

Postgres Version: 16

We have defined the below parameters related to Patroni replication. It is expected that writes will continue when the parameter synchronous_mode_strict set to false, is this expected?

[root@bastion-node pg-db]# cat values.yaml | grep sync
synchronous_mode: “on”
[root@bastion-node pg-db]# oc get pg pg-cluster-ha-pg-db -o yaml | grep sync
synchronous_mode: “on”
synchronous_mode_strict: false
synchronous_node_count: 2
syncPeriodSeconds: 10
[root@bastion-node pg-db]#

While we are testing sync and async replication between the primary and replica pods, the following is the insert status irrespective of the parameter value “synchronous_mode_strict”.

  • w/ synchronous_mode_strict:true

    • if replica pod is deleted gracefully (oc delete pod), patroni updates synchronous_standby_names to * allowing writes to continue on Primary

    • if replica pod is deleted with force option (oc delete pod <> --force to simulate real life failure), patroni does not update primary and writes are stalled

  • w/ synchronous_mode_strict:false

    • if replica pod is deleted gracefully (oc delete pod), patroni updates synchronous_standby_names to * allowing writes to continue on Primary

    • if replica pod is deleted with force option (oc delete pod <> --force to simulate real life failure), patroni does not update primary and writes are stalled.

2

@Srinivas

synchronous_mode_strict maintains strict consistency and requires at least one synchronous replica to allow commits or write request.

This parameter prevents Patroni from switching off the synchronous replication on the primary when no synchronous standby candidates are available. As a downside, the primary is not be available for writes (unless the Postgres transaction explicitly turns off synchronous_commit), blocking all client write requests until at least one synchronous replica comes up

I tried deleting one of the sync pods [cluster1-instance1-gmx5-0] with --force and observed a few seconds stall on leader write.

sh-5.1$ patronictl list
+ Cluster: cluster1-ha (7654234399595749491) -------------------------+--------------+-----------+----+-------------+-----+------------+-----+
|           Member          |                   Host                  |     Role     |   State   | TL | Receive LSN | Lag | Replay LSN | Lag |
+---------------------------+-----------------------------------------+--------------+-----------+----+-------------+-----+------------+-----+
| cluster1-instance1-bv44-0 | cluster1-instance1-bv44-0.cluster1-pods | Replica      | streaming |  2 |  0/18000000 |   0 | 0/18000000 |   0 |
**| cluster1-instance1-gmx5-0 | cluster1-instance1-gmx5-0.cluster1-pods | Sync Standby | streaming |  2 |  0/18000000 |   0 | 0/18000000 |   0 |**
| cluster1-instance1-mwsw-0 | cluster1-instance1-mwsw-0.cluster1-pods | Leader       | running   |  2 |             |     |            |     |
+---------------------------

kubectl delete pod cluster1-instance1-gmx5-0 --force -n postgres-operator
Warning: Immediate deletion does not wait for confirmation that the running resource has been terminated. The resource may continue to run on the cluster indefinitely.
pod "cluster1-instance1-gmx5-0" force deleted

That stall happens because sync replica[cluster1-instance1-gmx5-0] was stopped for that movement.

sh-5.1$ patronictl list
+ Cluster: cluster1-ha (7654234399595749491) -------------------------+--------------+-----------+----+-------------+-----+------------+-----+
|           Member          |                   Host                  |     Role     |   State   | TL | Receive LSN | Lag | Replay LSN | Lag |
+---------------------------+-----------------------------------------+--------------+-----------+----+-------------+-----+------------+-----+
| cluster1-instance1-bv44-0 | cluster1-instance1-bv44-0.cluster1-pods | Replica      | streaming |  2 |  0/1A0000D0 |   0 | 0/1A0000D0 |   0 |
**| cluster1-instance1-gmx5-0 | cluster1-instance1-gmx5-0.cluster1-pods | Sync Standby | stopped   |    |     unknown |     |    unknown |     |**
| cluster1-instance1-mwsw-0 | cluster1-instance1-mwsw-0.cluster1-pods | Leader       | running   |  2 |             |     |            |     |
+---------------------------+-----------------------------------------+--------------+-----------+----+-------------+-----+------------+-----+

Here, the leader continues waiting for acknowledgements from the sync standby, which was down.

Later once it available the writes works fine.

sh-5.1$ patronictl list
+ Cluster: cluster1-ha (7654234399595749491) -------------------------+--------------+-----------+----+-------------+-----+------------+-----+
|           Member          |                   Host                  |     Role     |   State   | TL | Receive LSN | Lag | Replay LSN | Lag |
+---------------------------+-----------------------------------------+--------------+-----------+----+-------------+-----+------------+-----+
| cluster1-instance1-bv44-0 | cluster1-instance1-bv44-0.cluster1-pods | Replica      | streaming |  2 |  0/1B000000 |   0 | 0/1B000000 |   0 |
| cluster1-instance1-gmx5-0 | cluster1-instance1-gmx5-0.cluster1-pods | Sync Standby | streaming |  2 |  0/1B000000 |   0 | 0/1B000000 |   0 |
| cluster1-instance1-mwsw-0 | cluster1-instance1-mwsw-0.cluster1-pods | Leader       | running   |  2 |             |     |            |     |
+---------------------------+-----------------------------------------+--------------+-----------+----+-------------+-----+------------+-----+
sh-5.1$

The fact is, with that instant delete [–force], it immediately removes resources from the API and bypasses graceful deletion. This most likely doesn’t allow time to switch sync mode on other available Replica [cluster1-instance1-bv44-0] or still the writes consider old Sync node for some time frame.

–force
If true, immediately remove resources from API and bypass graceful deletion. Note that immediate deletion of some resources may result in inconsistency or data loss and requires confirmation.
kubectl delete | Kubernetes

Your observation is correct. With graceful deletion, it works fine, and we didn’t observe this issue.

Can you please share the patronictl output below to take a quick look at the member roles sync staus?

kubectl exec -it <cluster> -n postgres-operator -- sh 
patronictl list