[pgpool-general: 7411] Re: pcp_node_info does not show replication info in MASTER-SLAVE mode

[Bo Peng]

Hi,

On Wed, 3 Feb 2021 10:33:55 +0000
Láznička Vladimír <Vladimir.Laznicka at cca.cz> wrote:

> Hello,
> 
> We've recently switched our pgPool/PostgreSQL cluster from the native replication to MASTER-SLAVE replication (streaming replication via replication slots). Everything seems to be working just fine (like failover, online recovery or load balancing), but if I query the node status with pcp_node_info command, the information about replication state does not get displayed:

Please make sure you have specified correct application name 
in "backend_application_name0" and "backend_application_name1".

  backend_application_name0 = 'DBD1'
  backend_application_name1 = 'DBD2'

Are "DBD1" and "DBD2" the application name specified in "primary_conninfo"?

> [pgpool at AISGPGP01 ~]$ pcp_node_info -h /home/pgpool/pcp_socket_dir --no-password -v 0
> Hostname               : aisgdbd01.cca.cz
> Port                   : 5432
> Status                 : 2
> Weight                 : 0.500000
> Status Name            : up
> Role                   : primary
> Replication Delay      : 0
> Replication State      :
> Replication Sync State :
> Last Status Change     : 2021-02-01 16:07:01
> 
> The same can be observed, when I try to query the node status with SQL command:
> 
> aisgdvyv=# SHOW POOL_NODES;
> node_id |     hostname     | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
> ---------+------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
> 0       | aisgdbd01.cca.cz | 5432 | up     | 0.500000  | primary | 1507331    | true              | 0                 |                   |                        | 2021-02-01 16:07:01
> 1       | aisgdbd02.cca.cz | 5432 | up     | 0.500000  | standby | 806751     | false             | 0                 |                   |                        | 2021-02-01 16:08:00
> 
> If I try to directly query the pg_stat_replication view on the PRIMARY, I get this result:
> 
> aisgdvyv=# SELECT * FROM pg_stat_replication;
> -[ RECORD 1 ]----+------------------------------
> pid              | 4429
> usesysid         | 16393
> usename          | replication
> application_name | walreceiver
> client_addr      | 172.20.15.67
> client_hostname  |
> client_port      | 32944
> backend_start    | 2021-02-01 16:07:55.678388+01
> backend_xmin     |
> state            | streaming
> sent_lsn         | E5/10170BD8
> write_lsn        | E5/10170BD8
> flush_lsn        | E5/10170BD8
> replay_lsn       | E5/10170BD8
> write_lag        | 00:00:00.000208
> flush_lag        | 00:00:00.000907
> replay_lag       | 00:00:00.000972
> sync_priority    | 0
> sync_state       | async
> 
> It seems like pgPool cannot access the information. I have created a user "pgpool_streamcheck" as a member of the group "pg_monitor" to be used for the replication delay check:
> 
> aisgdvyv=# \dg
>                                               List of roles
>       Role name      |                         Attributes                         |       Member of
> ---------------------+------------------------------------------------------------+-----------------------
> ...
> pgpool_healthcheck  |                                                            | {}
> pgpool_streamcheck  |                                                            | {pg_monitor}
> postgres            | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
> replication         | Replication                                                | {}
> ...
> 
> >From the logs of pgPool it seems to be able to query the delay since I can occasionally see it exceeds the set threshold:
> 
> Feb  2 22:39:08 AISGPGP01 pgpool[28300]: [2205-1] 2021-02-02 22:39:08 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG:  Replication of node:1 is behind 8224 bytes from the primary server (node:0)
> Feb  2 22:39:08 AISGPGP01 pgpool[28300]: [2205-2] 2021-02-02 22:39:08 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |CONTEXT:  while checking replication time lag
> 
> I am sending you the pgpool.conf file as an attachment. Could you please look it over and tell me, if I have set something wrong? We are using the pgPool version 4.1.5 and the PostgreSQL version 11.10.
> 
> Thank you for your time.
> 
> With best regards,
> Vladimír Láznička


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