[pgpool-hackers: 3317] 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,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp