[pgpool-hackers: 3871] Re: Proposal: add new fields to show pool_nodes

[Tatsuo Ishii]

Patch including pcp_node_info and pgpool_adm added. Still need to work
on documents.

> And patch for regression test.
> 
>> Attached is the PoC patch.
>> 
>>> For upcoming Pgpool-II 4.3 I would like to propose to add new fields
>>> to "show pool_nodes" command. Comments are welcome.
>>> 
>>> There are "status" and "role" fields: "status" represents the DB node
>>> status stored in shared memory ("up", "down" etc), "role" represents
>>> "primary" or "standby" (in streaming replication mode), "main" or
>>> "replica" (in native replication or snapshot isolation mode).
>>> 
>>> The actual DB node status does not necessary match the "status"
>>> field. For example, pcp_detach_node can set the status to "down" while
>>> the DB node is still up and running.  Similar thing can be said to
>>> "role" field. If there are two primaries (or non standbys) nodes
>>> exist, Pgpool-II only shows one of them as "primary" and shows others
>>> as "standby".  As a result, admins periodically check the actual
>>> status/role directly on DB nodes.
>>> 
>>> So my proposal is to add following new 2 fields:
>>> 
>>> pg_status: actual DB node status. Either "up" or "down". The status is
>>> fetched by using pg_isready command while executing show
>>> pool_nodes. Arguments for pg_isready are obtained from health check
>>> parameters. The reason for this choice is, pgpool already checks the
>>> availability of PostgreSQL by using health check parameters. If health
>>> check is disabled (health_check_period = 0), the status will be
>>> "unknown".
>>> 
>>> pg_role: actual DB node status. Only meaningful in streaming
>>> replication mode. Either "primary" or "standby". If
>>> pg_is_in_recovery() returns true, it will be represented as "standby",
>>> otherwise "primary". In other clustering modes, same as "role" field.
>>> Arguments for querying to backend are obtained from
>>> sr_check_parameters. The reason for this choice is, pgpool already
>>> checks the role of PostgreSQL by using sr_check parameters. If
>>> sr_check is disabled (sr_check_period = 0), the status will be
>>> "unknown". For other clustering mode, value of "role" field is copied
>>> to pg_role field.
>>> 
>>> Here is the image of new fields. On node 0 and 1, new fields
>>> "pg_status" and "pg_role" are match with "status" and "role". On node
>>> 2 pg_status is unknown since health check is disabled. pg_role shows
>>> "primary" although "role" is "standby". Probably our admin should
>>> check node 2 to find out what's going on.
>>> 
>>> test=# show pool_nodes;
>>> -[ RECORD 1 ]----------+--------------------
>>> node_id                | 0
>>> hostname               | /tmp
>>> port                   | 11002
>>> status                 | up
>>> pg_status              | up
>>> lb_weight              | 0.333333
>>> role                   | primary
>>> pg_role                | primary
>>> select_cnt             | 0
>>> load_balance_node      | false
>>> replication_delay      | 0
>>> replication_state      | 
>>> replication_sync_state | 
>>> last_status_change     | 2021-02-09 22:47:03
>>> -[ RECORD 2 ]----------+--------------------
>>> node_id                | 1
>>> hostname               | /tmp
>>> port                   | 11003
>>> status                 | up
>>> pg_status              | up
>>> lb_weight              | 0.333333
>>> role                   | standby
>>> pg_role                | standby
>>> select_cnt             | 0
>>> load_balance_node      | true
>>> replication_delay      | 0
>>> replication_state      | streaming
>>> replication_sync_state | async
>>> last_status_change     | 2021-02-09 22:47:03
>>> -[ RECORD 3 ]----------+--------------------
>>> node_id                | 2
>>> hostname               | /tmp
>>> port                   | 11004
>>> status                 | up
>>> pg_status              | unknown
>>> lb_weight              | 0.333333
>>> role                   | standby
>>> pg_role                | primary
>>> select_cnt             | 0
>>> load_balance_node      | false
>>> replication_delay      | 0
>>> replication_state      | streaming
>>> replication_sync_state | async
>>> last_status_change     | 2021-02-09 22:47:03
>>> _______________________________________________
>>> pgpool-hackers mailing list
>>> pgpool-hackers at pgpool.net
>>> http://www.pgpool.net/mailman/listinfo/pgpool-hackers
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pool_nodes.diff
Type: text/x-patch
Size: 38475 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20210213/8c40b6d2/attachment-0001.bin>