[pgpool-hackers: 3319] Re: Adding new columns to show pool_status

[Tatsuo Ishii]

>>> Hi,
>>> 
>>> I would like to add new columns to existing "show pool_status" command
>>> toward 4.1. New columns will be brought by calling pg_stat_replication
>>> view. From the PostgreSQL docs of pg_stat_replication:
>>> 
>>> state: Current WAL sender state. Possible values are:
>>> 
>>>     startup: This WAL sender is starting up.
>>> 
>>>     catchup: This WAL sender's connected standby is catching up with the primary.
>>> 
>>>     streaming: This WAL sender is streaming changes after its
>>>     	       connected standby server has caught up with the primary.
>>> 
>>>     backup: This WAL sender is sending a backup.
>>> 
>>>     stopping: This WAL sender is stopping.
>>> 
>>> sync_state: Synchronous state of this standby server. Possible values are:
>>> 
>>>     async: This standby server is asynchronous.
>>> 
>>>     potential: This standby server is now asynchronous, but can
>>>     	       potentially become synchronous if one of current synchronous ones fails.
>>> 
>>>     sync: This standby server is synchronous.
>>> 
>>>     quorum: This standby server is considered as a candidate for quorum standbys. 
>>> 
>>> These should be useful information for streaming replication users. Of
>>> course users could call pg_stat_replication as usual but I think
>>> showing them in "show pool_status" is handy for users so that they
>>> could grasp overview status of the cluster managed by Pgpool-II.
>>> 
>>> To implement this, I will add new members to BackendInfo struct
>>> sitting on the shared memory. Existing replication delay collecting
>>> process will be in charge of calling the view.
>> 
>> Please note that those two columns are only available in PostgreSQL
>> 9.2 or later. So we need to check PostgreSQL version.
> 
> Also we need to set application_name in recovery.conf (or in
> postgresql.conf if PostgreSQL 12 or later) so that we can distingusih
> which row of pg_stat_replication corresponds to which standby
> server. Unfortunately the view does not provide enough information for
> that. Again from the doc:
> 
> ---------------------------------------------------------------------
> client_addr:
> IP address of the client connected to this WAL sender. If this field
> is null, it indicates that the client is connected via a Unix socket
> on the server machine.
> 
> client_hostname:
> host name of the connected client, as reported by a reverse DNS lookup
> of client_addr. This field will only be non-null for IP connections,
> and only when log_hostname is enabled.
> 
> client_port:
> TCP port number that the client is using for communication with this
> WAL sender, or -1 if a Unix socket is used
> ---------------------------------------------------------------------
> 
> Thus, if we use UNIX domain socket, then client_add = NULL,
> client_hostname = NULL and client_port = -1, which means we cannot
> distinguish two standby nodes that use UNIX domain socket.  The only
> way to solve this, unique application_name must be assigned to each
> standby server's connection string to walsender.
> 
> So I would like to propose followings:
> 
> 1) add new "backend_application_name" parameter. User has to set
> unique application_name for each backends. Probably we should set
> default appropriate backend_application_name something like "server0".
> 
> Also users must set the application_name in standby server's
> connection string to walsender.
> 
> 2) if backend_application_name is not set or application_name is
> different from what is set in the connection string, the new columns
> will be shown as NULL.
> 
> Best regards,

Done. Initial commit has been made.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp