Change SYNC to ASYNC percona patroni replication with callback or others

PostgreSQL Percona Distribution for PostgreSQL
1

hi, we have 2 patroni nodes and 3 etcd nodes, currently, we configured SYNC replication between replication node SYNCHRONOUS_COMMIT=on and synchornous_standby_name=‘node2’, we intended to change replication to ASYNC if replica goes down with callback or some other parameters, need help, how we can achieve this requirement

Percona 14.6
RHEL 8

2

Hello @MRizwanShaukat you can use the parameters “synchronous_mode”, “synchronous_commit” “synchronous_standby_names”, and patroni will take care of the standby names. For example:

bootstrap:
    dcs:
        ttl: 30
        maximum_lag_on_failover: 1048576
        synchronous_mode: "on"
        postgresql:
            use_pg_rewind: true
            use_slots: true
            parameters:
                wal_level: replica
                hot_standby: "on"
                synchronous_commit: "on"
                synchronous_standby_names: "*"

Note that in the below example I’m using synchronous_standby_names: “*” this way Patroni will take care of the standby name. In this example I have a setup with 3 nodes (node1, node2, node3) and node1 is the primary. If I check the param I get:

test=> show synchronous_standby_names;
-[ RECORD 1 ]-------------+------------
synchronous_standby_names | "node2"
(1 row)


root@lab-node2:~# patronictl -c /percona/config/patroni.yml list
+ Cluster: cluster_1 (7208383656948843317) --------+----+-----------+
| Member    | Host        | Role         | State   | TL | Lag in MB |
+-----------+-------------+--------------+---------+----+-----------+
| node1     | 10.0.100.13 | Leader       | running |  1 |           |
| node2     | 10.0.100.11 | Sync Standby | running |  1 |         0 |
| node3     | 10.0.100.6  | Replica      | running |  1 |         0 |
+-----------+-------------+--------------+---------+----+-----------+

If I cause node2 to crash then I get:

test=> show synchronous_standby_names;
 synchronous_standby_names 
---------------------------
 "node3"
(1 row)


root@lab-node2:~# patronictl -c /percona/config/patroni.yml list
+ Cluster: cluster_1 (7208383656948843317) --------+----+-----------+
| Member    | Host        | Role         | State   | TL | Lag in MB |
+-----------+-------------+--------------+---------+----+-----------+
| node1     | 10.0.100.13 | Leader       | running |  1 |           |
| node3     | 10.0.100.6  | Sync Standby | running |  1 |         0 |
+-----------+-------------+--------------+---------+----+-----------+

As we can see, Patroni took care of the Sync Standby.

Now, in the case I lose all my nodes (I’m using more ETCD servers than PG nodes in this example to be able to have only one PG node running as leader) Patroni will empty the config:

test=> show synchronous_standby_names;
 synchronous_standby_names 
---------------------------
 
(1 row)

test=> select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

test=> insert into t1 select from generate_series(1,1000);
INSERT 0 1000


root@lab-node2:~# patronictl -c /percona/config/patroni.yml list
+ Cluster: cluster_1 (7208383656948843317) --+----+-----------+
| Member    | Host        | Role   | State   | TL | Lag in MB |
+-----------+-------------+--------+---------+----+-----------+
| node1     | 10.0.100.13 | Leader | running |  1 |           |
+-----------+-------------+--------+---------+----+-----------+

My primary is still running and responsive as if it wasn’t using sync replicas.

Note that I am NOT using “synchronous_mode_strict” because this parameter prevents Patroni from switching off the synchronous replication on the primary when no synchronous standby candidates are available.

You may find more information in Patroni’s documentation here[1].

Hope this helps.

Charly

[1] Replication modes — Patroni 3.0.1 documentation

1 Like
3

Thanks for reply, @Charly_Batista it’s working as expected :slight_smile:

4

hi, we configured with “*” it’s not able to get synchronous_standby_names automatically, we need to specify standby name in this parameter, then only it showed SYCN Standby … on disaster, it’s not changing standby parameter automatically, any idea pls

5

Hi,

Can you please share your configuration? You can get it using the below command:

patronictl -c </path/to/patroni.yml> edit-config

6

We specify these parameters on all nodes, it’s not changing status to “Sync Standby” automatically, even synchronous_standby_names didn’t pick node name automatically e.g. node 2 or node 3, on node 2 failure also, it’s not changing value.

loop_wait: 10
master_start_timeout: 300
maximum_lag_on_failover: 1048576
postgresql:
parameters:
hot_standby: ‘on’
max_replication_slots: 10
max_wal_senders: 10
synchronous_commit: ‘on’
synchronous_standby_names: ‘*’
wal_keep_segments: 8
wal_level: replica
use_pg_rewind: true
use_slots: true
retry_timeout: 10
slots:
percona_3nodes_replication_slot:
type: physical
synchronous_mode: ‘on’
ttl: 30