[pgpool-general: 6779] Re: Delay in synchronous replication, is it possible ?!

[Bo Peng]

Hi,

On Wed, 13 Nov 2019 13:01:07 -0300
Franklin Anderson de Oliveira Souza <franklinbr at gmail.com> wrote:

> I use pgpool with 5 slave on mode synchronous.
> But sometimes i see values on column replication_delay when execute command
> show pool_nodes.
> Sometimes, when a node goes beyond the value of delay_threshold, the node
> does not receive balancing query.
> 
> But
> 1- Synchronous replication should not be delayed is not it?
> 2- How is this possible ?
> 3- Is the replication_delay parameter of pgpool wrong?
> 
> show pool_nodes
>  node_id |  hostname   | port | status | lb_weight |  role   | select_cnt |
> load_balance_node | replication_delay | last_status_change
> ---------+-------------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
>  0       | IP          | 5432 | up     | 0.100000  | primary | 230616548  |
> false             | 0                 | 2019-10-24 19:17:27
>  1       | IP          | 5432 | up     | 0.100000  | standby | 172466610  |
> true              | 0                 | 2019-10-24 19:17:27
>  2       | IP          | 5432 | up     | 0.100000  | standby | 180179200  |
> false             | 234324            | 2019-10-24 19:17:27
>  3       | IP          | 5432 | up     | 0.100000  | standby | 178475037  |
> false             | 20777216          | 2019-10-24 19:17:27
>  4       | IP          | 5432 | up     | 0.100000  | standby | 182174042  |
> false             | 0                 | 2019-10-24 19:17:27
>  5       | IP          | 5432 | up     | 0.100000  | standby | 181169375  |
> false             | 0                 | 2019-10-24 19:17:27
> 
> 
> 
> pg_stat_replication
>   application_name  | client_addr |  write_lsn   |  flush_lsn   |
> write_lag    | sync_priority | sync_state
> --------------------+-------------+--------------+--------------+-----------------+---------------+------------
>  pje1_slave1        | IP          | 8FA/42E88B60 | 8FA/42E88B60 |
> 00:00:00.000355 |             1 | sync
>  pje1_slave2        | IP          | 8FA/42E88B60 | 8FA/42E88B60 |
> 00:00:00.000312 |             2 | potential
>  pje1_slave3        | IP          | 8FA/42E88B60 | 8FA/42E88B60 |
> 00:00:00.000366 |             3 | potential
>  pje1_slave4        | IP          | 8FA/42E88B60 | 8FA/42E88B60 |
> 00:00:00.000277 |             4 | potential
>  pje1_slave5        | IP          | 8FA/42E88B60 | 8FA/42E88B60 |
> 00:00:00.000265 |             5 | potential

It's the feature of PostgreSQL synchronous replication, not pgpool's issue.

As you can see from pg_stat_replication.sync_state, 
only "pje1_slave1" is a synchronous standby.
pje1_slave2-5 are "potential" which means asynchronous standby.

See more details:
https://www.postgresql.org/docs/12/monitoring-stats.html#PG-STAT-REPLICATION-VIEW


> -- 
> foobar


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan