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

[Tatsuo Ishii]

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