From 1ae1f159b89f4d18a8f7b737929e9a6448ad63ab Mon Sep 17 00:00:00 2001 From: Tatsuo Ishii Date: Sat, 27 Feb 2021 16:34:49 +0900 Subject: [PATCH] Add new fields to show pool_nodes command and friends. 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. Modified commands/functions are: show poool_nodes, pcp_node_info and pgpool_adm_pcp_node_info. --- doc.ja/src/sgml/ref/pcp_node_info.sgml | 78 ++++++---- .../sgml/ref/pgpool_adm_pcp_node_info.sgml | 28 +++- doc.ja/src/sgml/ref/show_pool_nodes.sgml | 23 ++- doc/src/sgml/ref/pcp_node_info.sgml | 70 ++++++--- .../sgml/ref/pgpool_adm_pcp_node_info.sgml | 27 +++- doc/src/sgml/ref/show_pool_nodes.sgml | 24 ++- src/include/pcp/libpcp_ext.h | 4 + src/libs/pcp/pcp.c | 13 +- src/pcp_con/pcp_child.c | 8 +- src/pcp_con/pcp_worker.c | 11 +- src/sql/pgpool_adm/Makefile | 3 +- src/sql/pgpool_adm/pgpool_adm--1.3--1.4.sql | 98 ++++++++++++ src/sql/pgpool_adm/pgpool_adm--1.4.sql | 101 ++++++++++++ src/sql/pgpool_adm/pgpool_adm.c | 84 ++++++---- src/sql/pgpool_adm/pgpool_adm.control | 2 +- .../tests/003.failover/create_expected.sql | 12 +- .../003.failover/create_expected_node0.sql | 12 +- .../regression/tests/003.failover/test.sh | 4 +- src/tools/pcp/pcp_frontend_client.c | 10 +- src/utils/pool_process_reporting.c | 147 +++++++++++++++++- 20 files changed, 626 insertions(+), 133 deletions(-) create mode 100644 src/sql/pgpool_adm/pgpool_adm--1.3--1.4.sql create mode 100644 src/sql/pgpool_adm/pgpool_adm--1.4.sql diff --git a/doc.ja/src/sgml/ref/pcp_node_info.sgml b/doc.ja/src/sgml/ref/pcp_node_info.sgml index 9a9eedf03..18c9a66e1 100644 --- a/doc.ja/src/sgml/ref/pcp_node_info.sgml +++ b/doc.ja/src/sgml/ref/pcp_node_info.sgml @@ -95,8 +95,8 @@ Pgpool-II documentation --> ここでは例を示します。 - $ pcp_node_info -h localhost -U postgres 1 - /tmp 11003 2 0.500000 up standby 0 streaming async 2019-04-23 13:58:40 + $ pcp_node_info -w -p 11001 1 + /tmp 11003 1 0.500000 waiting up standby standby 0 streaming async 2021-02-27 14:51:30 @@ -109,32 +109,36 @@ Pgpool-II documentation 1. hostname 2. port number 3. status - 4. load balance weight - 5. status name - 6. backend role - 7. replication delay - 8. replication state (PostgreSQL 9.1以降。pg_stat_replicationから取得) - 9. sync replication state (PostgreSQL 9.2以降。pg_stat_replicationから取得) - 10. last status change time + 4. backend status + 5. load balance weight + 6. status name + 7. backend role + 8. actual backend role + 9. replication delay + 10. replication state (PostgreSQL 9.1以降。pg_stat_replicationから取得) + 11. sync replication state (PostgreSQL 9.2以降。pg_stat_replicationから取得) + 12. last status change time --> 1. ノードのホスト名 2. ノードのポート番号 3. ステータス 4. ロードバランスウェイト 5. バックエンド状態名 - 6. バックエンドの役割 - 7. レプリケーションの遅延 - 8. レプリケーション状態 (PostgreSQL 9.1以降。pg_stat_replicationから取得) - 9. 同期レプリケーション状態 (PostgreSQL 9.2以降。pg_stat_replicationから取得) - 10. 最終ステータス変更時刻 + 6. 実際のバックエンドの状態 (Pgpool-II 4.3以降。pg_isreadyで取得) + 7. バックエンドの役割 + 8. 実際のバックエンドの役割(Pgpool-II 4.3以降。pg_is_in_recoveryで取得) + 9. レプリケーションの遅延 + 10. レプリケーション状態 (Pgpool-II 4.1以降。pg_stat_replicationから取得) + 11. 同期レプリケーション状態 (Pgpool-II 4.1以降。pg_stat_replicationから取得) + 12. 最終ステータス変更時刻 - 7, 8, 9は、が0以外でなければ正しく表示されません。 - また、8, 9は、がPostgreSQLのスーパーユーザーであるか、"pg_monitor"グループに所属していなけれ正しく表示されません。 + 9, 10, 11は、が0以外でなければ正しく表示されません。 + また、10, 11は、がPostgreSQLのスーパーユーザーであるか、"pg_monitor"グループに所属していなけれ正しく表示されません。 をpg_monitorグループに所属させるには、以下のSQLコマンドをPostgreSQLスーパーユーザーで実行してください（"sr_check_user"をの設定値で置き換えてください） @@ -160,30 +164,46 @@ Pgpool-II documentation 3 - ノードダウン + + 6は実際のバックエンドの状態をリアルタイムで取得して表示します。 + 情報の取得はpg_isreadyを呼び出すことによって行われます。 + でバックエンドノードを切り離すと、Pgpool-IIが管理する状態は"Down"になりますが、バックエンドの実際の状態は"up"なので、5と6が一致しないことはあり得ます。 + 逆に、5が"up"なのに、6が"down"というのは起こらないはずです。 + + + 8は実際のバックエンドの状態をリアルタイムで取得して表示します。 + その結果はストリーミングレプリケーションモードでは、"primary"か"standby"ですが、情報が取得できなければ"unknown"と表示します。 + Pgpool-IIはノードID順にバックエンドノードを調べ、最後に見つけたスタンバイ以外のノードをプライマリとするので、誤ってスタンバイ以外のノードが複数できたときなどには、7と8が一致しないことはありえます。 + （このコマンドはそのような状態を見つけるのに役立ちます。) + それ以外のモードでは、"main"あるいは"replica"と表示されます。 + ストリーミングレプリケーションモードと違って、pg_is_in_recoveryは呼び出されず、7と8が異なる値になることはありません。 + - ロードバランスウェイトは Normalize されたフォーマットで出力されます。 + ロードバランスウェイトは0から1に正規化されたフォーマットで出力されます。 - オプションは出力内容を理解するのに役に立ちます。例： + オプションは出力内容を理解するのに役に立ちます。例を示します： - $ pcp_node_info --verbose -h localhost -U postgres 1 - Hostname : /tmp - Port : 11003 - Status : 2 - Weight : 0.500000 - Status Name : up - Role : standby - Replication Delay : 0 - Replication State : streaming - Replication Sync State : async - Last Status Change : 2019-04-23 13:58:40 +$ pcp_node_info -w -p 11001 --verbose 1 +Hostname : /tmp +Port : 11003 +Status : 1 +Weight : 0.500000 +Status Name : waiting +Backend Status Name : up +Role : standby +Backend Role : standby +Replication Delay : 0 +Replication State : streaming +Replication Sync State : async +Last Status Change : 2021-02-27 14:51:30 diff --git a/doc.ja/src/sgml/ref/pgpool_adm_pcp_node_info.sgml b/doc.ja/src/sgml/ref/pgpool_adm_pcp_node_info.sgml index fd034dacc..301607db6 100644 --- a/doc.ja/src/sgml/ref/pgpool_adm_pcp_node_info.sgml +++ b/doc.ja/src/sgml/ref/pgpool_adm_pcp_node_info.sgml @@ -35,8 +35,10 @@ Pgpool-II documentation text username text password out status text + out pg_status text out weight float4 out role text + out pg_role text out replication_delay bigint out replication_state text out replication_sync_state text @@ -48,8 +50,10 @@ Pgpool-II documentation integer node_id text pcp_server out status text + out pg_status text out weight float4 out role text + out pg_role text out replication_delay bigint out replication_state text out replication_sync_state text @@ -134,14 +138,18 @@ Pgpool-II documentation --> 使用例です。 - test=# SELECT * FROM pcp_node_info(node_id => 1, host => '', port => 11001, username => 't-ishii', password => 't-ishii'); - host | port | status | weight | role | replication_delay | replication_state | replication_sync_state | last_status_change - ------+-------+-------------------+--------+---------+-------------------+-------------------+------------------------+--------------------- - /tmp | 11003 | Connection in use | 0.5 | Standby | 0 | streaming | async | 2019-04-23 15:02:46 - (1 row) +test=# SELECT * FROM pcp_node_info(node_id => 1, host => '', port => 11001, username => 't-ishii', password => 't-ishii'); + host | port | status | pg_status | weight | role | pg_role | replication_delay | replication_state | replication_sync_state | last_status_change +------+-------+-------------------+-----------+--------+---------+---------+-------------------+-------------------+------------------------+--------------------- + /tmp | 11003 | Connection in use | up | 0.5 | Standby | standby | 0 | streaming | async | 2021-02-27 15:10:19 +(1 row) + + 表示データの意味については、をご覧ください。 + + ここでは例を示します。 - test=# show pool_nodes; - node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change - ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------------------- - 0 | /tmp | 11002 | up | 0.500000 | primary | 0 | false | 0 | | | 2019-04-22 16:13:46 - 1 | /tmp | 11003 | up | 0.500000 | standby | 0 | true | 0 | streaming | async | 2019-04-22 16:13:46 - (2 rows) +test=# show pool_nodes; + node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change +---------+----------+-------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------------------- + 0 | /tmp | 11002 | up | up | 0.500000 | primary | primary | 0 | false | 0 | | | 2021-02-27 15:10:19 + 1 | /tmp | 11003 | up | up | 0.500000 | standby | standby | 0 | true | 0 | streaming | async | 2021-02-27 15:10:19 +(2 rows) diff --git a/doc/src/sgml/ref/pcp_node_info.sgml b/doc/src/sgml/ref/pcp_node_info.sgml index d9d3b1354..a29f411bd 100644 --- a/doc/src/sgml/ref/pcp_node_info.sgml +++ b/doc/src/sgml/ref/pcp_node_info.sgml @@ -68,8 +68,8 @@ Pgpool-II documentation Here is an example output: - $ pcp_node_info -h localhost -U postgres 1 - /tmp 11003 2 0.500000 up standby 0 streaming async 2019-04-23 13:58:40 + $ pcp_node_info -w -p 11001 1 + /tmp 11003 1 0.500000 waiting up standby standby 0 streaming async 2021-02-27 14:51:30 @@ -80,17 +80,19 @@ Pgpool-II documentation 3. status 4. load balance weight 5. status name - 6. backend role - 7. replication delay - 8. replication state (taken from pg_stat_replication, if PostgreSQL is 9.1 or later) - 9. sync replication state (taken from pg_stat_replication, if PostgreSQL is 9.2 or later) - 10. last status change time + 6. actual backend status (taken from pg_isready. Pgpool-II 4.3 or later) + 7. backend role + 8. actual backend role (taken from pg_is_in_recovery. Pgpool-II 4.3 or later) + 9. replication delay + 10. replication state (taken from pg_stat_replication. Pgpool-II 4.1 or later) + 11. sync replication state (taken from pg_stat_replication. Pgpool-II 4.1 or later) + 12. last status change time - Status is represented by a digit from [0 to 3]. To correctly 7, - 8, 9 are displayed, must not - be 0. 8, 9 will not be displayed + Status is represented by a digit from [0 to 3]. To correctly 9, + 10, 11 are displayed, must not + be 0. 10, 11 will not be displayed if is not PostgreSQL super user nor it's not in "pg_monitor" group. @@ -118,23 +120,47 @@ Pgpool-II documentation - The load balance weight is displayed in normalized format. + 6 shows the backend status in real time. The info is taken by + calling pg_isready. When a backend node is + detached by , the status managed by + Pgpool-II will be "Down", while the + actual backend status is "up". Thus it is possible that 5 does not + match with 6. However it should not happen that 5 is "up" while 6 + is "down". + + + 8 shows the backend status in real time. The result will be either + "primary" or "standby", and possibly "unknown" if information + retrieval failed. Since Pgpool-II + searches backend nodes in the node id order and assumes the last + found node is primary, it is possible that 7 does not match 8 when + there are multiple nodes that are not standby by erroneous + operations (this command is useful to find such that situation). + In other than streaming replication mode, the status will be either + "main" or "replica". Unlike streaming replication mode + pg_is_in_recovery is not called and value for + 7 and 8 will be always the same. + + + The load balance weight is displayed in normalized format (0 - 1). The option can help understand the output. For example: - $ pcp_node_info --verbose -h localhost -U postgres 1 - Hostname : /tmp - Port : 11003 - Status : 2 - Weight : 0.500000 - Status Name : up - Role : standby - Replication Delay : 0 - Replication State : streaming - Replication Sync State : async - Last Status Change : 2019-04-23 13:58:40 +$ pcp_node_info -w -p 11001 --verbose 1 +Hostname : /tmp +Port : 11003 +Status : 1 +Weight : 0.500000 +Status Name : waiting +Backend Status Name : up +Role : standby +Backend Role : standby +Replication Delay : 0 +Replication State : streaming +Replication Sync State : async +Last Status Change : 2021-02-27 14:51:30 diff --git a/doc/src/sgml/ref/pgpool_adm_pcp_node_info.sgml b/doc/src/sgml/ref/pgpool_adm_pcp_node_info.sgml index 41a6532ff..e4e85cda0 100644 --- a/doc/src/sgml/ref/pgpool_adm_pcp_node_info.sgml +++ b/doc/src/sgml/ref/pgpool_adm_pcp_node_info.sgml @@ -31,8 +31,10 @@ Pgpool-II documentation text username text password out status text + out pg_status text out weight float4 out role text + out pg_role text out replication_delay bigint out replication_state text out replication_sync_state text @@ -44,8 +46,10 @@ Pgpool-II documentation integer node_id text pcp_server out status text + out pg_status text out weight float4 out role text + out pg_role text out replication_delay bigint out replication_state text out replication_sync_state text @@ -103,11 +107,11 @@ Pgpool-II documentation Here is an example output: - test=# SELECT * FROM pcp_node_info(node_id => 1, host => '', port => 11001, username => 't-ishii', password => 't-ishii'); - host | port | status | weight | role | replication_delay | replication_state | replication_sync_state | last_status_change - ------+-------+-------------------+--------+---------+-------------------+-------------------+------------------------+--------------------- - /tmp | 11003 | Connection in use | 0.5 | Standby | 0 | streaming | async | 2019-04-23 15:02:46 - (1 row) +test=# SELECT * FROM pcp_node_info(node_id => 1, host => '', port => 11001, username => 't-ishii', password => 't-ishii'); + host | port | status | pg_status | weight | role | pg_role | replication_delay | replication_state | replication_sync_state | last_status_change +------+-------+-------------------+-----------+--------+---------+---------+-------------------+-------------------+------------------------+--------------------- + /tmp | 11003 | Connection in use | up | 0.5 | Standby | standby | 0 | streaming | async | 2021-02-27 15:10:19 +(1 row) @@ -135,6 +139,19 @@ Pgpool-II documentation + + + pg_status, pg_role + out parameters are new from Pgpool-II + 4.3. If you have already installed pre-4.2 pgpool_adm extension, + you can upgrade to the new one by using ALTER EXTENSION SQL + command. + + ALTER EXTENSION pgpool_adm UPDATE; + + + + diff --git a/doc/src/sgml/ref/show_pool_nodes.sgml b/doc/src/sgml/ref/show_pool_nodes.sgml index 5898fd42d..ba701e327 100644 --- a/doc/src/sgml/ref/show_pool_nodes.sgml +++ b/doc/src/sgml/ref/show_pool_nodes.sgml @@ -38,9 +38,17 @@ balance node or not, the replication delay (only if in streaming replication mode) and last status change time. In addition to this replication state and sync state are shown for standby nodes - in Pgpool-II 4.1 or after. The + in Pgpool-II 4.1 or after. + Also actual node status and node role are shown in + Pgpool-II 4.3 or after. + + + The possible values in the status column are explained in - the reference. If the hostname is + the reference. + + + If the hostname is something like "/tmp", that means Pgpool-II is connecting to backend by using UNIX domain sockets. The SELECT count does not include @@ -55,12 +63,12 @@ Here is an example session: - test=# show pool_nodes; - node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change - ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------------------- - 0 | /tmp | 11002 | up | 0.500000 | primary | 0 | false | 0 | | | 2019-04-22 16:13:46 - 1 | /tmp | 11003 | up | 0.500000 | standby | 0 | true | 0 | streaming | async | 2019-04-22 16:13:46 - (2 rows) +test=# show pool_nodes; + node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change +---------+----------+-------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------------------- + 0 | /tmp | 11002 | up | up | 0.500000 | primary | primary | 0 | false | 0 | | | 2021-02-27 15:10:19 + 1 | /tmp | 11003 | up | up | 0.500000 | standby | standby | 0 | true | 0 | streaming | async | 2021-02-27 15:10:19 +(2 rows) diff --git a/src/include/pcp/libpcp_ext.h b/src/include/pcp/libpcp_ext.h index 6f000a215..7f8e9d960 100644 --- a/src/include/pcp/libpcp_ext.h +++ b/src/include/pcp/libpcp_ext.h @@ -83,6 +83,7 @@ typedef struct char backend_hostname[MAX_DB_HOST_NAMELEN]; /* backend host name */ int backend_port; /* backend port numbers */ BACKEND_STATUS backend_status; /* backend status */ + char pg_backend_status[NAMEDATALEN]; /* backend status examined by show pool_nodes and pcp_node_info*/ time_t status_changed_time; /* backend status changed time */ double backend_weight; /* normalized backend load balance ratio */ double unnormalized_weight; /* described parameter */ @@ -95,6 +96,7 @@ typedef struct SERVER_ROLE role; /* Role of server. used by pcp_node_info and * failover() to keep track of quarantined * primary node */ + char pg_role[NAMEDATALEN]; /* backend role examined by show pool_nodes and pcp_node_info*/ char replication_state [NAMEDATALEN]; /* "state" from pg_stat_replication */ char replication_sync_state [NAMEDATALEN]; /* "sync_state" from pg_stat_replication */ } BackendInfo; @@ -188,8 +190,10 @@ typedef struct char hostname[MAX_DB_HOST_NAMELEN + 1]; char port[POOLCONFIG_MAXPORTLEN + 1]; char status[POOLCONFIG_MAXSTATLEN + 1]; + char pg_status[POOLCONFIG_MAXSTATLEN + 1]; char lb_weight[POOLCONFIG_MAXWEIGHTLEN + 1]; char role[POOLCONFIG_MAXWEIGHTLEN + 1]; + char pg_role[POOLCONFIG_MAXWEIGHTLEN + 1]; char select[POOLCONFIG_MAXWEIGHTLEN + 1]; char load_balance_node[POOLCONFIG_MAXWEIGHTLEN + 1]; char delay[POOLCONFIG_MAXWEIGHTLEN + 1]; diff --git a/src/libs/pcp/pcp.c b/src/libs/pcp/pcp.c index 709e2761c..052ff1e41 100644 --- a/src/libs/pcp/pcp.c +++ b/src/libs/pcp/pcp.c @@ -728,15 +728,26 @@ process_node_info_response(PCPConnInfo * pcpConn, char *buf, int len) if (index == NULL) goto INVALID_RESPONSE; index += 1; - backend_info->backend_weight = atof(index); + strlcpy(backend_info->pg_backend_status, index, sizeof(backend_info->pg_backend_status)); index = (char *) memchr(index, '\0', len); if (index == NULL) goto INVALID_RESPONSE; + index += 1; + backend_info->backend_weight = atof(index); + index = (char *) memchr(index, '\0', len); + if (index == NULL) + goto INVALID_RESPONSE; index++; backend_info->role = atoi(index); + index = (char *) memchr(index, '\0', len); + if (index == NULL) + goto INVALID_RESPONSE; + index++; + strlcpy(backend_info->pg_role, index, sizeof(backend_info->pg_role)); + index = (char *) memchr(index, '\0', len); if (index == NULL) goto INVALID_RESPONSE; diff --git a/src/pcp_con/pcp_child.c b/src/pcp_con/pcp_child.c index ee6b1bef2..0103360fc 100644 --- a/src/pcp_con/pcp_child.c +++ b/src/pcp_con/pcp_child.c @@ -5,7 +5,7 @@ * pgpool: a language independent connection pool server for PostgreSQL * written by Tatsuo Ishii * - * Copyright (c) 2003-2020 PgPool Global Development Group + * Copyright (c) 2003-2021 PgPool Global Development Group * * Permission to use, copy, modify, and distribute this software and * its documentation for any purpose and without fee is hereby @@ -29,6 +29,9 @@ #include "utils/pool_ipc.h" #include "utils/ps_status.h" +#include "context/pool_process_context.h" +#include "context/pool_session_context.h" + #include "pcp/pcp_worker.h" #include @@ -137,6 +140,9 @@ pcp_main(int unix_fd, int inet_fd) MemoryContextSwitchTo(TopMemoryContext); + /* Initialize per process context */ + pool_init_process_context(); + if (sigsetjmp(local_sigjmp_buf, 1) != 0) { error_context_stack = NULL; diff --git a/src/pcp_con/pcp_worker.c b/src/pcp_con/pcp_worker.c index 47211dd07..445dbe730 100644 --- a/src/pcp_con/pcp_worker.c +++ b/src/pcp_con/pcp_worker.c @@ -4,7 +4,7 @@ * pgpool: a language independent connection pool server for PostgreSQL * written by Tatsuo Ishii * - * Copyright (c) 2003-2020 PgPool Global Development Group + * Copyright (c) 2003-2021 PgPool Global Development Group * * Permission to use, copy, modify, and distribute this software and * its documentation for any purpose and without fee is hereby @@ -48,6 +48,7 @@ #include "auth/md5.h" #include "auth/pool_auth.h" #include "context/pool_process_context.h" +#include "context/pool_session_context.h" #include "utils/pool_process_reporting.h" #include "utils/palloc.h" #include "utils/memutils.h" @@ -834,6 +835,8 @@ inform_watchdog_info(PCP_CONNECTION * frontend, char *buf) static void inform_node_info(PCP_CONNECTION * frontend, char *buf) { + POOL_REPORT_NODES *nodes; + int nrows; int node_id; int wsize; char port_str[6]; @@ -883,13 +886,17 @@ inform_node_info(PCP_CONNECTION * frontend, char *buf) snprintf(status_changed_time_str, sizeof(status_changed_time_str), UINT64_FORMAT, bi->status_changed_time); + nodes = get_nodes(&nrows); + pcp_write(frontend, "i", 1); wsize = htonl(sizeof(code) + strlen(bi->backend_hostname) + 1 + strlen(port_str) + 1 + strlen(status) + 1 + + strlen(nodes[node_id].pg_status) + 1 + strlen(weight_str) + 1 + strlen(role_str) + 1 + + strlen(nodes[node_id].pg_role) + 1 + strlen(standby_delay_str) + 1 + strlen(bi->replication_state) + 1 + strlen(bi->replication_sync_state) + 1 + @@ -900,8 +907,10 @@ inform_node_info(PCP_CONNECTION * frontend, char *buf) pcp_write(frontend, bi->backend_hostname, strlen(bi->backend_hostname) + 1); pcp_write(frontend, port_str, strlen(port_str) + 1); pcp_write(frontend, status, strlen(status) + 1); + pcp_write(frontend, nodes[node_id].pg_status, strlen(nodes[node_id].pg_status) + 1); pcp_write(frontend, weight_str, strlen(weight_str) + 1); pcp_write(frontend, role_str, strlen(role_str) + 1); + pcp_write(frontend, nodes[node_id].pg_role, strlen(nodes[node_id].pg_role) + 1); pcp_write(frontend, standby_delay_str, strlen(standby_delay_str) + 1); pcp_write(frontend, bi->replication_state, strlen(bi->replication_state) + 1); pcp_write(frontend, bi->replication_sync_state, strlen(bi->replication_sync_state) + 1); diff --git a/src/sql/pgpool_adm/Makefile b/src/sql/pgpool_adm/Makefile index abd457fc8..5a46d80f4 100644 --- a/src/sql/pgpool_adm/Makefile +++ b/src/sql/pgpool_adm/Makefile @@ -6,7 +6,8 @@ PG_CPPFLAGS = -I$(libpq_srcdir) -I../../include/pcp EXTENSION = pgpool_adm DATA = pgpool_adm--1.0.sql pgpool_adm--1.1.sql pgpool_adm--1.2.sql pgpool_adm--1.3.sql \ -pgpool_adm--1.0--1.1.sql pgpool_adm--1.1--1.2.sql pgpool_adm--1.2--1.3.sql +pgpool_adm--1.0--1.1.sql pgpool_adm--1.1--1.2.sql pgpool_adm--1.2--1.3.sql \ +pgpool_adm--1.4.sql pgpool_adm--1.3--1.4.sql SHLIB_LINK = -L../../libs/pcp/.libs -lpcp -Wl,--as-needed -Wl,-rpath,'${prefix}/lib',--enable-new-dtags # if you are using PostgreSQL 8.0 or later, # using pg_config is recommended. diff --git a/src/sql/pgpool_adm/pgpool_adm--1.3--1.4.sql b/src/sql/pgpool_adm/pgpool_adm--1.3--1.4.sql new file mode 100644 index 000000000..c8d51aa4e --- /dev/null +++ b/src/sql/pgpool_adm/pgpool_adm--1.3--1.4.sql @@ -0,0 +1,98 @@ +/* contrib/pgpool_adm/pgpool_adm--1.3--1.4.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION pgpool_adm UPDATE TO '1.4'" to load this file. \quit + +CREATE FUNCTION pcp_health_check_stats(IN node_id integer, IN host text, IN port integer, IN username text, IN password text, OUT node_id integer, OUT host text, OUT port integer, OUT status text, OUT role text, OUT last_status_change timestamp, OUT total_count bigint, OUT success_count bigint, OUT fail_count bigint, OUT skip_count bigint, OUT retry_count bigint, OUT average_retry_count float4, OUT max_retry_count bigint, OUT max_health_check_duration bigint, OUT min_health_check_duration bigint, OUT average_health_check_duration float4, OUT last_health_check timestamp, OUT last_successful_health_check timestamp, OUT last_skip_health_check timestamp, OUT last_failed_health_check timestamp) +RETURNS record +AS 'MODULE_PATHNAME', '_pcp_health_check_stats' +LANGUAGE C VOLATILE STRICT; + +CREATE FUNCTION pcp_health_check_stats(IN node_id integer, IN pcp_server text, OUT host text, OUT port integer, OUT status text, OUT weight float4) +RETURNS record +AS 'MODULE_PATHNAME', '_pcp_health_check_stats' +LANGUAGE C VOLATILE STRICT; + +ALTER EXTENSION pgpool_adm DROP FUNCTION pcp_node_info(IN node_id integer, IN host text, IN port integer, IN username text, IN password text, OUT host text, OUT port integer, OUT status text, OUT pg_status text, OUT weight float4, OUT role text, OUT pg_role text, OUT replication_delay bigint, OUT replication_state text, OUT replication_sync_state text, OUT last_status_change timestamp) + +ALTER EXTENSION pgpool_adm CREATE FUNCTION pcp_node_info(IN node_id integer, IN host text, IN port integer, IN username text, IN password text, OUT host text, OUT port integer, OUT status text, OUT pg_status text, OUT weight float4, OUT role text, OUT pg_role text, OUT replication_delay bigint, OUT replication_state text, OUT replication_sync_state text, OUT last_status_change timestamp) + +ALTER EXTENSION pgpool_adm DROP FUNCTION pcp_node_info(integer, text, integer, text, text, OUT host text, OUT port integer, OUT status text, OUT weight float4, OUT role text, OUT replication_delay bigint, OUT replication_state text, OUT replication_sync_state text, OUT last_status_change timestamp); +DROP FUNCTION pcp_node_info(integer, text, integer, text, text, OUT host text, OUT port integer, OUT status text, OUT weight float4, OUT role text, OUT replication_delay bigint, OUT replication_state text, OUT replication_sync_state text, OUT last_status_change timestamp); + +CREATE FUNCTION pcp_node_info(IN node_id integer, IN host text, IN port integer, IN username text, IN password text, OUT host text, OUT port integer, OUT status text, OUT weight float4, OUT role text, OUT replication_delay bigint, OUT replication_state text, OUT replication_sync_state text, OUT last_status_change timestamp) +RETURNS record +AS 'MODULE_PATHNAME', '_pcp_node_info' +LANGUAGE C VOLATILE STRICT; + +ALTER EXTENSION pgpool_adm DROP FUNCTION pcp_node_info(integer, text, OUT host text, OUT port integer, OUT status text, OUT weight float4); +DROP FUNCTION pcp_node_info(integer, text, OUT host text, OUT port integer, OUT status text, OUT weight float4); + +CREATE FUNCTION pcp_node_info(IN node_id integer, IN pcp_server text, OUT host text, OUT port integer, OUT status text, OUT weight float4) +RETURNS record +AS 'MODULE_PATHNAME', '_pcp_node_info' +LANGUAGE C VOLATILE STRICT; + +ALTER EXTENSION pgpool_adm DROP FUNCTION pcp_pool_status(text, integer, text, text, OUT item text, OUT value text, OUT description text); +DROP FUNCTION pcp_pool_status(text, integer, text, text, OUT item text, OUT value text, OUT description text); + +CREATE FUNCTION pcp_pool_status(IN host text, IN port integer, IN username text, IN password text, OUT item text, OUT value text, OUT description text) +RETURNS record +AS 'MODULE_PATHNAME', '_pcp_pool_status' +LANGUAGE C VOLATILE STRICT; + +ALTER EXTENSION pgpool_adm DROP FUNCTION pcp_pool_status(text, OUT item text, OUT value text, OUT description text); +DROP FUNCTION pcp_pool_status(text, OUT item text, OUT value text, OUT description text); + +CREATE FUNCTION pcp_pool_status(IN pcp_server text, OUT item text, OUT value text, OUT description text) +RETURNS record +AS 'MODULE_PATHNAME', '_pcp_pool_status' +LANGUAGE C VOLATILE STRICT; + +ALTER EXTENSION pgpool_adm DROP FUNCTION pcp_node_count(text, integer, text, text, OUT node_count integer); +DROP FUNCTION pcp_node_count(text, integer, text, text, OUT node_count integer); + +CREATE FUNCTION pcp_node_count(IN host text, IN port integer, IN username text, IN password text, OUT node_count integer) +RETURNS integer +AS 'MODULE_PATHNAME', '_pcp_node_count' +LANGUAGE C VOLATILE STRICT; + +ALTER EXTENSION pgpool_adm DROP FUNCTION pcp_node_count(text, OUT node_count integer); +DROP FUNCTION pcp_node_count(text, OUT node_count integer); + +CREATE FUNCTION pcp_node_count(IN pcp_server text, OUT node_count integer) +RETURNS integer +AS 'MODULE_PATHNAME', '_pcp_node_count' +LANGUAGE C VOLATILE STRICT; + +ALTER EXTENSION pgpool_adm DROP FUNCTION pcp_attach_node(integer, text, integer, text, text, OUT node_attached boolean); +DROP FUNCTION pcp_attach_node(integer, text, integer, text, text, OUT node_attached boolean); + +CREATE FUNCTION pcp_attach_node(IN node_id integer, IN host text, IN port integer, IN username text, IN password text, OUT node_attached boolean) +RETURNS boolean +AS 'MODULE_PATHNAME', '_pcp_attach_node' +LANGUAGE C VOLATILE STRICT; + +ALTER EXTENSION pgpool_adm DROP FUNCTION pcp_attach_node(integer, text, OUT node_attached boolean); +DROP FUNCTION pcp_attach_node(integer, text, OUT node_attached boolean); + +CREATE FUNCTION pcp_attach_node(IN node_id integer, IN pcp_server text, OUT node_attached boolean) +RETURNS boolean +AS 'MODULE_PATHNAME', '_pcp_attach_node' +LANGUAGE C VOLATILE STRICT; + +ALTER EXTENSION pgpool_adm DROP FUNCTION pcp_detach_node(integer, boolean, text, integer, text, text, OUT node_detached boolean); +DROP FUNCTION pcp_detach_node(integer, boolean, text, integer, text, text, OUT node_detached boolean); + +CREATE FUNCTION pcp_detach_node(IN node_id integer, IN gracefully boolean, IN host text, IN port integer, IN username text, IN password text, OUT node_detached boolean) +RETURNS boolean +AS 'MODULE_PATHNAME', '_pcp_detach_node' +LANGUAGE C VOLATILE STRICT; + +ALTER EXTENSION pgpool_adm DROP FUNCTION pcp_detach_node(integer, boolean, text, OUT node_detached boolean); +DROP FUNCTION pcp_detach_node(integer, boolean, text, OUT node_detached boolean); + +CREATE FUNCTION pcp_detach_node(IN node_id integer, IN gracefully boolean, IN pcp_server text, OUT node_detached boolean) +RETURNS boolean +AS 'MODULE_PATHNAME', '_pcp_detach_node' +LANGUAGE C VOLATILE STRICT; diff --git a/src/sql/pgpool_adm/pgpool_adm--1.4.sql b/src/sql/pgpool_adm/pgpool_adm--1.4.sql new file mode 100644 index 000000000..b3cf74c88 --- /dev/null +++ b/src/sql/pgpool_adm/pgpool_adm--1.4.sql @@ -0,0 +1,101 @@ +/* contrib/pgpool_adm/pgpool_adm--1.3.sql */ + +/* *********************************************** + * Administrative functions for pgPool + * *********************************************** */ + +/** + * input parameters: node_id, host, port, username, password + */ +CREATE FUNCTION pcp_node_info(IN node_id integer, IN host text, IN port integer, IN username text, IN password text, OUT host text, OUT port integer, OUT status text, OUT pg_status text, OUT weight float4, OUT role text, OUT pg_role text, OUT replication_delay bigint, OUT replication_state text, OUT replication_sync_state text, OUT last_status_change timestamp) +RETURNS record +AS 'MODULE_PATHNAME', '_pcp_node_info' +LANGUAGE C VOLATILE STRICT; + +/** + * input parameters: node_id, pcp_server + */ +CREATE FUNCTION pcp_node_info(IN node_id integer, IN pcp_server text, OUT host text, OUT port integer, OUT status text, OUT weight float4) +RETURNS record +AS 'MODULE_PATHNAME', '_pcp_node_info' +LANGUAGE C VOLATILE STRICT; + +/** + * input parameters: node_id, host, port, username, password + */ +CREATE FUNCTION pcp_health_check_stats(IN node_id integer, IN host text, IN port integer, IN username text, IN password text, OUT node_id integer, OUT host text, OUT port integer, OUT status text, OUT role text, OUT last_status_change timestamp, OUT total_count bigint, OUT success_count bigint, OUT fail_count bigint, OUT skip_count bigint, OUT retry_count bigint, OUT average_retry_count float4, OUT max_retry_count bigint, OUT max_health_check_duration bigint, OUT min_health_check_duration bigint, OUT average_health_check_duration float4, OUT last_health_check timestamp, OUT last_successful_health_check timestamp, OUT last_skip_health_check timestamp, OUT last_failed_health_check timestamp) +RETURNS record +AS 'MODULE_PATHNAME', '_pcp_health_check_stats' +LANGUAGE C VOLATILE STRICT; + +/** + * input parameters: node_id, pcp_server + */ +CREATE FUNCTION pcp_health_check_stats(IN node_id integer, IN pcp_server text, OUT host text, OUT port integer, OUT status text, OUT weight float4) +RETURNS record +AS 'MODULE_PATHNAME', '_pcp_health_check_stats' +LANGUAGE C VOLATILE STRICT; + +/** + * input parameters: host, port, username, password + */ +CREATE FUNCTION pcp_pool_status(IN host text, IN port integer, IN username text, IN password text, OUT item text, OUT value text, OUT description text) +RETURNS record +AS 'MODULE_PATHNAME', '_pcp_pool_status' +LANGUAGE C VOLATILE STRICT; + +/** + * input parameters: pcp_server + */ +CREATE FUNCTION pcp_pool_status(IN pcp_server text, OUT item text, OUT value text, OUT description text) +RETURNS record +AS 'MODULE_PATHNAME', '_pcp_pool_status' +LANGUAGE C VOLATILE STRICT; + +/** + * input parameters: host, port, username, password + */ +CREATE FUNCTION pcp_node_count(IN host text, IN port integer, IN username text, IN password text, OUT node_count integer) +RETURNS integer +AS 'MODULE_PATHNAME', '_pcp_node_count' +LANGUAGE C VOLATILE STRICT; + +/** + * input parameters: pcp_server + */ +CREATE FUNCTION pcp_node_count(IN pcp_server text, OUT node_count integer) +RETURNS integer +AS 'MODULE_PATHNAME', '_pcp_node_count' +LANGUAGE C VOLATILE STRICT; + +/** + * input parameters: node_id, host, port, username, password + */ +CREATE FUNCTION pcp_attach_node(IN node_id integer, IN host text, IN port integer, IN username text, IN password text, OUT node_attached boolean) +RETURNS boolean +AS 'MODULE_PATHNAME', '_pcp_attach_node' +LANGUAGE C VOLATILE STRICT; + +/** + * input parameters: node_id, pcp_server + */ +CREATE FUNCTION pcp_attach_node(IN node_id integer, IN pcp_server text, OUT node_attached boolean) +RETURNS boolean +AS 'MODULE_PATHNAME', '_pcp_attach_node' +LANGUAGE C VOLATILE STRICT; + +/** + * input parameters: node_id, gracefully, host, port, username, password + */ +CREATE FUNCTION pcp_detach_node(IN node_id integer, IN gracefully boolean, IN host text, IN port integer, IN username text, IN password text, OUT node_detached boolean) +RETURNS boolean +AS 'MODULE_PATHNAME', '_pcp_detach_node' +LANGUAGE C VOLATILE STRICT; + +/** + * input parameters: node_id, gracefully, pcp_server + */ +CREATE FUNCTION pcp_detach_node(IN node_id integer, IN gracefully boolean, IN pcp_server text, OUT node_detached boolean) +RETURNS boolean +AS 'MODULE_PATHNAME', '_pcp_detach_node' +LANGUAGE C VOLATILE STRICT; diff --git a/src/sql/pgpool_adm/pgpool_adm.c b/src/sql/pgpool_adm/pgpool_adm.c index 4da51a00f..9a8cb9648 100644 --- a/src/sql/pgpool_adm/pgpool_adm.c +++ b/src/sql/pgpool_adm/pgpool_adm.c @@ -130,12 +130,13 @@ _pcp_node_info(PG_FUNCTION_ARGS) PCPResultInfo *pcpResInfo; BackendInfo *backend_info = NULL; - Datum values[9]; /* values to build the returned tuple from */ - bool nulls[] = {false, false, false, false, false, false, false, false, false}; + Datum values[11]; /* values to build the returned tuple from */ + bool nulls[] = {false, false, false, false, false, false, false, false, false, false, false}; TupleDesc tupledesc; HeapTuple tuple; struct tm tm; char datebuf[20]; + int i; if (nodeID < 0 || nodeID >= MAX_NUM_BACKENDS) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("NodeID is out of range."))); @@ -176,63 +177,84 @@ _pcp_node_info(PG_FUNCTION_ARGS) * Construct a tuple descriptor for the result rows. **/ #if defined(PG_VERSION_NUM) && (PG_VERSION_NUM >= 120000) - tupledesc = CreateTemplateTupleDesc(9); + tupledesc = CreateTemplateTupleDesc(11); #else - tupledesc = CreateTemplateTupleDesc(9, false); + tupledesc = CreateTemplateTupleDesc(11, false); #endif TupleDescInitEntry(tupledesc, (AttrNumber) 1, "hostname", TEXTOID, -1, 0); TupleDescInitEntry(tupledesc, (AttrNumber) 2, "port", INT4OID, -1, 0); TupleDescInitEntry(tupledesc, (AttrNumber) 3, "status", TEXTOID, -1, 0); - TupleDescInitEntry(tupledesc, (AttrNumber) 4, "weight", FLOAT4OID, -1, 0); - TupleDescInitEntry(tupledesc, (AttrNumber) 5, "role", TEXTOID, -1, 0); - TupleDescInitEntry(tupledesc, (AttrNumber) 6, "replication_delay", INT8OID, -1, 0); - TupleDescInitEntry(tupledesc, (AttrNumber) 7, "replication_state", TEXTOID, -1, 0); - TupleDescInitEntry(tupledesc, (AttrNumber) 8, "replication_sync_state", TEXTOID, -1, 0); - TupleDescInitEntry(tupledesc, (AttrNumber) 9, "last_status_change", TIMESTAMPOID, -1, 0); + TupleDescInitEntry(tupledesc, (AttrNumber) 4, "pg_status", TEXTOID, -1, 0); + TupleDescInitEntry(tupledesc, (AttrNumber) 5, "weight", FLOAT4OID, -1, 0); + TupleDescInitEntry(tupledesc, (AttrNumber) 6, "role", TEXTOID, -1, 0); + TupleDescInitEntry(tupledesc, (AttrNumber) 7, "pg_role", TEXTOID, -1, 0); + TupleDescInitEntry(tupledesc, (AttrNumber) 8, "replication_delay", INT8OID, -1, 0); + TupleDescInitEntry(tupledesc, (AttrNumber) 9, "replication_state", TEXTOID, -1, 0); + TupleDescInitEntry(tupledesc, (AttrNumber) 10, "replication_sync_state", TEXTOID, -1, 0); + TupleDescInitEntry(tupledesc, (AttrNumber) 11, "last_status_change", TIMESTAMPOID, -1, 0); tupledesc = BlessTupleDesc(tupledesc); backend_info = (BackendInfo *) pcp_get_binary_data(pcpResInfo, 0); /* set values */ - values[0] = CStringGetTextDatum(backend_info->backend_hostname); - nulls[0] = false; - values[1] = Int16GetDatum(backend_info->backend_port); - nulls[1] = false; + i = 0; + values[i] = CStringGetTextDatum(backend_info->backend_hostname); + nulls[i] = false; + i++; + values[i] = Int16GetDatum(backend_info->backend_port); + nulls[i] = false; + + i++; switch (backend_info->backend_status) { case CON_UNUSED: - values[2] = CStringGetTextDatum("Connection unused"); + values[i] = CStringGetTextDatum("Connection unused"); break; case CON_CONNECT_WAIT: - values[2] = CStringGetTextDatum("Waiting for connection to start"); + values[i] = CStringGetTextDatum("Waiting for connection to start"); break; case CON_UP: - values[2] = CStringGetTextDatum("Connection in use"); + values[i] = CStringGetTextDatum("Connection in use"); break; case CON_DOWN: - values[2] = CStringGetTextDatum("Disconnected"); + values[i] = CStringGetTextDatum("Disconnected"); break; } - nulls[2] = false; - values[3] = Float4GetDatum(backend_info->backend_weight / RAND_MAX); - nulls[3] = false; + nulls[i] = false; + + i++; + nulls[i] = false; + values[i] = CStringGetTextDatum(backend_info->pg_backend_status); + + i++; + values[i] = Float4GetDatum(backend_info->backend_weight / RAND_MAX); + nulls[i] = false; + + i++; + nulls[i] = false; + values[i] = backend_info->role == ROLE_PRIMARY ? CStringGetTextDatum("Primary") : CStringGetTextDatum("Standby"); - nulls[4] = false; - values[4] = backend_info->role == ROLE_PRIMARY ? CStringGetTextDatum("Primary") : CStringGetTextDatum("Standby"); + i++; + nulls[i] = false; + values[i] = CStringGetTextDatum(backend_info->pg_role); - nulls[5] = false; - values[5] = Int64GetDatum(backend_info->standby_delay); + i++; + nulls[i] = false; + values[i] = Int64GetDatum(backend_info->standby_delay); - nulls[6] = false; - values[6] = CStringGetTextDatum(backend_info->replication_state); + i++; + nulls[i] = false; + values[i] = CStringGetTextDatum(backend_info->replication_state); - nulls[7] = false; - values[7] = CStringGetTextDatum(backend_info->replication_sync_state); + i++; + nulls[i] = false; + values[i] = CStringGetTextDatum(backend_info->replication_sync_state); - nulls[8] = false; + i++; + nulls[i] = false; localtime_r(&backend_info->status_changed_time, &tm); strftime(datebuf, sizeof(datebuf), "%F %T", &tm); - values[8] = DatumGetTimestamp(DirectFunctionCall3(timestamp_in, + values[i] = DatumGetTimestamp(DirectFunctionCall3(timestamp_in, CStringGetDatum(datebuf), ObjectIdGetDatum(InvalidOid), Int32GetDatum(-1))); diff --git a/src/sql/pgpool_adm/pgpool_adm.control b/src/sql/pgpool_adm/pgpool_adm.control index 9310ca0ad..a94f5e2d6 100644 --- a/src/sql/pgpool_adm/pgpool_adm.control +++ b/src/sql/pgpool_adm/pgpool_adm.control @@ -1,5 +1,5 @@ # pcp extension comment = 'Administrative functions for pgPool' -default_version = '1.3' +default_version = '1.4' module_pathname = '$libdir/pgpool_adm' relocatable = true diff --git a/src/test/regression/tests/003.failover/create_expected.sql b/src/test/regression/tests/003.failover/create_expected.sql index ea39f39a8..1164cd73d 100644 --- a/src/test/regression/tests/003.failover/create_expected.sql +++ b/src/test/regression/tests/003.failover/create_expected.sql @@ -3,8 +3,10 @@ CREATE TEMP TABLE tmp ( hostname text, port text, status text, + pg_status text, lb_weight text, role text, + pg_role text, select_cnt text, load_balance_node text, replication_delay text, @@ -14,11 +16,11 @@ CREATE TEMP TABLE tmp ( mode text); INSERT INTO tmp VALUES -('0',:dir,'11002','up','0.500000','primary','0','false','0','','','XXXX-XX-XX XX:XX:XX','s'), -('1',:dir,'11003','down','0.500000','standby','0','false','0','','','XXXX-XX-XX XX:XX:XX','s'), -('0',:dir,'11002','up','0.500000','main','0','false','0','','','XXXX-XX-XX XX:XX:XX','r'), -('1',:dir,'11003','down','0.500000','replica','0','false','0','','','XXXX-XX-XX XX:XX:XX','r'); +('0',:dir,'11002','up','up','0.500000','primary','unknown','0','false','0','','','XXXX-XX-XX XX:XX:XX','s'), +('1',:dir,'11003','down','down','0.500000','standby','unknown','0','false','0','','','XXXX-XX-XX XX:XX:XX','s'), +('0',:dir,'11002','up','up','0.500000','main','main','0','false','0','','','XXXX-XX-XX XX:XX:XX','r'), +('1',:dir,'11003','down','down','0.500000','replica','replica','0','false','0','','','XXXX-XX-XX XX:XX:XX','r'); -SELECT node_id,hostname,port,status,lb_weight,role,select_cnt,load_balance_node,replication_delay,replication_state, replication_sync_state, last_status_change +SELECT node_id,hostname,port,status,pg_status,lb_weight,role,pg_role,select_cnt,load_balance_node,replication_delay,replication_state, replication_sync_state, last_status_change FROM tmp WHERE mode = :mode diff --git a/src/test/regression/tests/003.failover/create_expected_node0.sql b/src/test/regression/tests/003.failover/create_expected_node0.sql index 5a12e5784..3532f6637 100644 --- a/src/test/regression/tests/003.failover/create_expected_node0.sql +++ b/src/test/regression/tests/003.failover/create_expected_node0.sql @@ -3,8 +3,10 @@ CREATE TEMP TABLE tmp ( hostname text, port text, status text, + pg_status text, lb_weight text, role text, + pg_role text, select_cnt text, load_balance_node text, replication_delay text, @@ -14,11 +16,11 @@ CREATE TEMP TABLE tmp ( mode text); INSERT INTO tmp VALUES -('0',:dir,'11002','down','0.500000','standby','0','false','0','','','XXXX-XX-XX XX:XX:XX','s'), -('1',:dir,'11003','up','0.500000','primary','0','false','0','','','XXXX-XX-XX XX:XX:XX','s'), -('0',:dir,'11002','down','0.500000','replica','0','false','0','','','XXXX-XX-XX XX:XX:XX','r'), -('1',:dir,'11003','up','0.500000','main','0','false','0','','','XXXX-XX-XX XX:XX:XX','r'); +('0',:dir,'11002','down','down','0.500000','standby','unknown','0','false','0','','','XXXX-XX-XX XX:XX:XX','s'), +('1',:dir,'11003','up','up','0.500000','primary','unknown','0','false','0','','','XXXX-XX-XX XX:XX:XX','s'), +('0',:dir,'11002','down','down','0.500000','replica','replica','0','false','0','','','XXXX-XX-XX XX:XX:XX','r'), +('1',:dir,'11003','up','up','0.500000','main','main','0','false','0','','','XXXX-XX-XX XX:XX:XX','r'); -SELECT node_id,hostname,port,status,lb_weight,role,select_cnt,load_balance_node,replication_delay,replication_state, replication_sync_state, last_status_change +SELECT node_id,hostname,port,status,pg_status,lb_weight,role,pg_role,select_cnt,load_balance_node,replication_delay,replication_state, replication_sync_state, last_status_change FROM tmp WHERE mode = :mode diff --git a/src/test/regression/tests/003.failover/test.sh b/src/test/regression/tests/003.failover/test.sh index 64e782f57..3d1870972 100755 --- a/src/test/regression/tests/003.failover/test.sh +++ b/src/test/regression/tests/003.failover/test.sh @@ -15,12 +15,14 @@ do # create test environment echo -n "creating test environment..." - $PGPOOL_SETUP -m $mode -n 2 --no-stop|| exit 1 + $PGPOOL_SETUP -m $mode -n 2|| exit 1 echo "done." # disable replication delay check so that comparison between # expected and actual result is not confused. echo "sr_check_period = 0" >> etc/pgpool.conf + ./startall + wait_for_pgpool_startup source ./bashrc.ports diff --git a/src/tools/pcp/pcp_frontend_client.c b/src/tools/pcp/pcp_frontend_client.c index 0a0fff9e2..8ace0a90b 100644 --- a/src/tools/pcp/pcp_frontend_client.c +++ b/src/tools/pcp/pcp_frontend_client.c @@ -516,8 +516,8 @@ output_nodeinfo_result(PCPResultInfo * pcpResInfo, bool verbose) if (verbose) { - const char *titles[] = {"Hostname", "Port", "Status", "Weight", "Status Name", "Role", "Replication Delay", "Replication State", "Replication Sync State", "Last Status Change"}; - const char *types[] = {"s", "d", "d", "f", "s", "s", "lu", "s", "s", "s"}; + const char *titles[] = {"Hostname", "Port", "Status", "Weight", "Status Name", "Backend Status Name", "Role", "Backend Role", "Replication Delay", "Replication State", "Replication Sync State", "Last Status Change"}; + const char *types[] = {"s", "d", "d", "f", "s", "s", "s", "s", "lu", "s", "s", "s"}; char *format_string; format_string = format_titles(titles, types, sizeof(titles)/sizeof(char *)); @@ -527,7 +527,9 @@ output_nodeinfo_result(PCPResultInfo * pcpResInfo, bool verbose) backend_info->backend_status, backend_info->backend_weight / RAND_MAX, backend_status_to_string(backend_info), + backend_info->pg_backend_status, role_to_str(backend_info->role), + backend_info->pg_role, backend_info->standby_delay, backend_info->replication_state, backend_info->replication_sync_state, @@ -535,13 +537,15 @@ output_nodeinfo_result(PCPResultInfo * pcpResInfo, bool verbose) } else { - printf("%s %d %d %f %s %s %lu %s %s %s\n", + printf("%s %d %d %f %s %s %s %s %lu %s %s %s\n", backend_info->backend_hostname, backend_info->backend_port, backend_info->backend_status, backend_info->backend_weight / RAND_MAX, backend_status_to_string(backend_info), + backend_info->pg_backend_status, role_to_str(backend_info->role), + backend_info->pg_role, backend_info->standby_delay, backend_info->replication_state, backend_info->replication_sync_state, diff --git a/src/utils/pool_process_reporting.c b/src/utils/pool_process_reporting.c index 18cdf64e2..23e2e73a5 100644 --- a/src/utils/pool_process_reporting.c +++ b/src/utils/pool_process_reporting.c @@ -5,7 +5,7 @@ * pgpool: a language independent connection pool server for PostgreSQL * written by Tatsuo Ishii * - * Copyright (c) 2003-2020 PgPool Global Development Group + * Copyright (c) 2003-2021 PgPool Global Development Group * * Permission to use, copy, modify, and distribute this software and * its documentation for any purpose and without fee is hereby @@ -23,12 +23,14 @@ #include "pool.h" #include "main/health_check.h" #include "protocol/pool_proto_modules.h" +#include "protocol/pool_process_query.h" #include "utils/elog.h" #include "utils/pool_stream.h" #include "utils/statistics.h" #include "pool_config.h" #include "query_cache/pool_memqcache.h" #include "version.h" +#include "protocol/pool_pg_utils.h" #include #include @@ -40,6 +42,8 @@ static void send_row_description_and_data_rows(POOL_CONNECTION * frontend, POOL_ char *data, int row_size, int nrows); static void write_one_field(POOL_CONNECTION * frontend, char *field); static void write_one_field_v2(POOL_CONNECTION * frontend, char *field); +static char *db_node_status(int node); +static char *db_node_role(int node); void send_row_description(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backend, @@ -1284,7 +1288,7 @@ get_nodes(int *nrows) int i; POOL_REPORT_NODES *nodes = palloc(NUM_BACKENDS * sizeof(POOL_REPORT_NODES)); BackendInfo *bi = NULL; - POOL_SESSION_CONTEXT *session_context = pool_get_session_context(false); + POOL_SESSION_CONTEXT *session_context = pool_get_session_context(true); struct tm tm; for (i = 0; i < NUM_BACKENDS; i++) @@ -1297,8 +1301,11 @@ get_nodes(int *nrows) snprintf(nodes[i].status, POOLCONFIG_MAXSTATLEN, "%s", backend_status_to_str(bi)); snprintf(nodes[i].lb_weight, POOLCONFIG_MAXWEIGHTLEN, "%f", bi->backend_weight / RAND_MAX); snprintf(nodes[i].select, POOLCONFIG_MAXWEIGHTLEN, UINT64_FORMAT, stat_get_select_count(i)); - snprintf(nodes[i].load_balance_node, POOLCONFIG_MAXWEIGHTLEN, "%s", - (session_context->load_balance_node_id == i) ? "true" : "false"); + if (session_context) + snprintf(nodes[i].load_balance_node, POOLCONFIG_MAXWEIGHTLEN, "%s", + (session_context->load_balance_node_id == i) ? "true" : "false"); + else + snprintf(nodes[i].load_balance_node, POOLCONFIG_MAXWEIGHTLEN, "%s", "false"); snprintf(nodes[i].delay, POOLCONFIG_MAXWEIGHTLEN, "%d", 0); @@ -1313,6 +1320,7 @@ get_nodes(int *nrows) snprintf(nodes[i].role, POOLCONFIG_MAXWEIGHTLEN, "%s", "standby"); snprintf(nodes[i].delay, POOLCONFIG_MAXWEIGHTLEN, UINT64_FORMAT, bi->standby_delay); } + snprintf(nodes[i].pg_role, POOLCONFIG_MAXWEIGHTLEN, "%s", db_node_role(i)); } else { @@ -1320,8 +1328,12 @@ get_nodes(int *nrows) snprintf(nodes[i].role, POOLCONFIG_MAXWEIGHTLEN, "%s", "main"); else snprintf(nodes[i].role, POOLCONFIG_MAXWEIGHTLEN, "%s", "replica"); + + snprintf(nodes[i].pg_role, POOLCONFIG_MAXWEIGHTLEN, "%s", nodes[i].role); } + snprintf(nodes[i].pg_status, POOLCONFIG_MAXSTATLEN, "%s", db_node_status(i)); + /* status last changed */ localtime_r(&bi->status_changed_time, &tm); strftime(nodes[i].last_status_change, POOLCONFIG_MAXDATELEN, "%F %T", &tm); @@ -1342,8 +1354,8 @@ get_nodes(int *nrows) void nodes_reporting(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backend) { - static char *field_names[] = {"node_id", "hostname", "port", "status", "lb_weight", "role", - "select_cnt", "load_balance_node", "replication_delay", + static char *field_names[] = {"node_id", "hostname", "port", "status", "pg_status", "lb_weight", "role", + "pg_role", "select_cnt", "load_balance_node", "replication_delay", "replication_state", "replication_sync_state", "last_status_change"}; static int offsettbl[] = { @@ -1351,8 +1363,10 @@ nodes_reporting(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backend) offsetof(POOL_REPORT_NODES, hostname), offsetof(POOL_REPORT_NODES, port), offsetof(POOL_REPORT_NODES, status), + offsetof(POOL_REPORT_NODES, pg_status), offsetof(POOL_REPORT_NODES, lb_weight), offsetof(POOL_REPORT_NODES, role), + offsetof(POOL_REPORT_NODES, pg_role), offsetof(POOL_REPORT_NODES, select), offsetof(POOL_REPORT_NODES, load_balance_node), offsetof(POOL_REPORT_NODES, delay), @@ -2148,3 +2162,124 @@ static void write_one_field_v2(POOL_CONNECTION * frontend, char *field) pool_write(frontend, &hsize, sizeof(hsize)); pool_write(frontend, field, size); } + +/* + * Get DB node status. Return values are "up", "down" or "unknown" (in case + * when health check is not enabled). + */ +static +char *db_node_status(int node) +{ + BackendInfo *bkinfo; + char *user; + char *dbname; + char *host; + int port; + char command[4096]; + int wstatus; + + /* + * If health check is not enabled, return "unknown". + */ + if (pool_config->health_check_params[node].health_check_period == 0) + { + return "unknown"; + } + + user = pool_config->health_check_params[node].health_check_user; + + /* + * If health check database is not defined, use "postgres" database. + */ + if (*pool_config->health_check_params[node].health_check_database == '\0') + dbname = "postgres"; + else + dbname = pool_config->health_check_params[node].health_check_database; + + bkinfo = pool_get_node_info(node); + host = bkinfo->backend_hostname; + port = bkinfo->backend_port; + snprintf(command, sizeof(command), "pg_isready --dbname=%s --host=%s --port=%d --username=%s --quiet", + dbname, host, port, user); + /* + * Use pg_isready command to know if the backend is alive or not. + */ + wstatus = system(command); + + if (WEXITSTATUS(wstatus) == 0) + { + return "up"; + } + return "down"; +} + +/* + * Get DB node role. Return values are "primary", "standby" or "unknown" (in case + * when sr check is not enabled). + */ +static +char *db_node_role(int node) +{ + BackendInfo *bkinfo; + POOL_CONNECTION_POOL_SLOT *slots[MAX_NUM_BACKENDS]; + POOL_SELECT_RESULT *res; + char *user; + char *password; + char *dbname; + char *host; + int port; + char *sts; + + if (pool_config->sr_check_period == 0) + { + /* sr check is disabled */ + return "unknown"; + } + + bkinfo = pool_get_node_info(node); + host = bkinfo->backend_hostname; + port = bkinfo->backend_port; + user = pool_config->sr_check_user; + password = get_pgpool_config_user_password(user, pool_config->sr_check_password); + dbname = pool_config->sr_check_database; + if (*dbname == '\0') + dbname = "postgres"; + + /* + * Establish connection to backend. + */ + slots[node] = make_persistent_db_connection_noerror(node, host, port, dbname, user, + password ? password : "", true); + + if (slots[node] == NULL) + return "unknown"; + + /* + * Query whether the node is in recovery. + */ + if (get_query_result(slots, node, "SELECT pg_is_in_recovery()", &res)) + { + return "unknown"; + } + + if (res->data[0] && !strcmp(res->data[0], "t")) + { + sts = "standby"; + } + else if (res->data[0] && !strcmp(res->data[0], "f")) + { + sts = "primary"; + } + else + { + sts = "unknown"; + } + free_select_result(res); + + /* + * Discard connection to backend. + */ + discard_persistent_db_connection(slots[node]); + + return sts; +} -- 2.39.5