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

[Tatsuo Ishii]

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: test.diff
Type: text/x-patch
Size: 4309 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20210210/e41c8716/attachment-0001.bin>