[pgpool-general: 8528] Re: "delay_threshold_by_time" not detecting replication lag

[zam bak]

On Tue, Dec 27, 2022 at 6:17 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
> > I wrote a "capture" script that runs several commands/queries to
> > demonstrate this issue
> > Script uses the query you requested (except I added the entire
> > replay_lag columns also)
> > The script followed by the output is below and it shows:
> > * when replay lag was 56+ seconds (according to pg_stat_statements),
> > the output from SHOW POOL_NODES and from pcp_node_info showed no delay
> > * there was no entry in the log showing replication log threshold being exceeded
> >
> > SCRIPT:
> [snip]
> > OUTPUT:
> >
> > [postgres at localhost]$ ./check_lag.sh
> >
> >
> > 2022-12-27T15:18:55+00:00 | checking lag on primary directly via
> > pg_stat_replication...
> >  application_name |   state   | sync_state |   int4   |   replay_lag
> > ------------------+-----------+------------+----------+-----------------
> >  walreceiver      | streaming | async      | 56663201 | 00:00:56.663201
> > (1 row)
>
> It seems application_name is "walreceiver", which is different from
> your pgpool.conf:
>
> > backend_application_name1 = 'pg-2'
>
> Pgpool-II checks the application name when delay_threshold_by_time >
> 0. If the result of pg_stat_replication.application_name does not
> match, replay_lag will be ignored. Check your primary_conninfo in
> postgresql.conf of the standby node to see whether application_name is
> properly set.

OK, This seems to be working now.

I made changes to pgpool.conf
  backend_application_name0 = 'primary'
  backend_application_name1 = 'replica_1'
Then, on standby/replica server #1, in the PostgreSQL config, setting
for "primary_conninfo", I added "application_name=replica_1"
After that, I repeated my test as previously and I could see log entries.

I guess I missed this because the previous setting I used
(delay_threshold) did not seem to depend on application_name in
replicas' primary_conninfo parameter...
May I suggest adding some clarification in docs for
"delay_threshold_by_time" - something like:
"delay_threshold_by_time relies on backend_application_name  being
correctly set and matching application_name in your
PostgreSQLreplica's primary_conninfo"


Also, since "pimary_conninfo" is only relevant to PostgreSQL replicas
(setting has no effect if the server is not in standby mode),
is it safe to say that "backend_application_name0" does not matter in
the config?


Once again thanks for the help!
Z..






>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp