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

[Tatsuo Ishii]

I have pushed the patch to master branch along with English/Japanese
documents.

https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=1ae1f159b89f4d18a8f7b737929e9a6448ad63ab

> 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