[pgpool-general: 8770] Re: Difference between pgpool 'primary' and Watchdog 'MASTER' versus the 'load_balance_node'

[Bo Peng]

Hi,

> Regd. which I couldn't gather much from the docs at:
> https://www.pgpool.net/docs/pgpool-II-4.0.4/
> 
> 
>    1. primary
>    2. MASTER
>    3. load_balance_node

1. primary: 
   It is the role of *PostgreSQL* nodes.
   The role of PostgreSQL nodes can be primary or standby
   in streaming replication mode.

2. MASTER: 
   I think you are asking the status name shown 
   in the result of pcp_watchdog_info command.
   The status name MASTER/STANDBY is the role of *Pgpool-II* node.
   If you have multiple Pgpool-II nodes using watchdog feature,
   one of them will be elected as MASTER and others will be STANDBY.

3. load_balance_node
   Pgpool-II supports for load balancing of SELECT queries.
   When a session starts, pgpool will randomly elect one PostgreSQL node
   as the load balance node (load_balance_node) and routes SELECTs to
   the load_balance_node.

On Mon, 15 May 2023 15:54:53 +0530
Gopikrishnan <nksgopikrishnan at gmail.com> wrote:

> Hi folks,
> 
> Need your help in clearing a confusion regd. basic terminologies:
> 
> Regd. which I couldn't gather much from the docs at:
> https://www.pgpool.net/docs/pgpool-II-4.0.4/
> 
> 
>    1. primary
>    2. MASTER
>    3. load_balance_node
> 
> I have a pgpool with 3 nodes. I have a confusion between the pgpool primary and
> the watchdog *MASTER* and the `*load_balance_node*`
> 
> In my setup, the pgpool primary node (according to show pool_nodes output)
> is : *10.108.104.31, *and also the node designated *load_balance_node*.
> 
> While the MASTER node, according to pgpool watchdog, is: *10.108.104.32* And
> the *cluster virtual IP* is attached to this node.
> 
> I have the following questions in mind:
> 
> 
>    1. What do the terms '*primary*' and '*MASTER*' and *load_balance_node*
>    mean?
>    2. During failover, Is the '*primary*' or '*MASTER*' or '
>    *load_balance_node* failure taken into account?
>    3. What is the difference (or any relation) between the '*primary*' and '
>    *MASTER*' and *load_balance_node* nodes?
>    4. If the cluster virtual IP is on the non '*primary*' node, wouldn't it
>    affect the performance of the HA setup?
> 
> Below are the outputs for *show pool_nodes *and* watchdog *info:
> 
> psql -h delegateIP -p 9999 -U pgpool postgres -c "show pool_nodes"
> Password for user pgpool:
>  node_id |   hostname    | port | status | lb_weight |  role   |
> select_cnt | load_balance_node | replication_delay |
> last_status_change---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
>  0       | 10.108.104.31 | 5432 | up     | 0.333333  | primary | 0
>      | true              | 0                 | 2023-05-12 12:07:13
>  1       | 10.108.104.32 | 5432 | up     | 0.333333  | standby | 0
>      | false             | 0                 | 2023-05-12 12:07:13
>  2       | 10.108.104.33 | 5432 | down   | 0.333333  | standby | 0
>      | false             | 0                 | 2023-05-12 12:07:13
> (3 rows)
> 
> And the Watchdog info:
> 
> pcp_watchdog_info -h localhost -vw -U pgpool
> Watchdog Cluster Information
> Total Nodes          : 3
> Remote Nodes         : 2
> Quorum state         : QUORUM EXIST
> Alive Remote Nodes   : 2
> VIP up on local node : NO
> Master Node Name     : vm-104-32.eng.dev.com:9999 Linux vm-104-32.eng.dev.com
> Master Host Name     : 10.108.104.32
> 
> Watchdog Node Information
> Node Name      : vm-104-31.eng.dev.com:9999 Linux vm-104-31.eng.dev.com
> Host Name      : vm-104-31.eng.dev.com
> Delegate IP    : 10.108.104.34
> Pgpool port    : 9999
> Watchdog port  : 9000
> Node priority  : 1
> Status         : 7
> Status Name    : STANDBY
> 
> Node Name      : vm-104-32.eng.dev.com:9999 Linux vm-104-32.eng.dev.com
> Host Name      : 10.108.104.32
> Delegate IP    : 10.108.104.34
> Pgpool port    : 9999
> Watchdog port  : 9000
> Node priority  : 1
> Status         : 4
> Status Name    : MASTER
> 
> Node Name      : vm-104-33.eng.dev.com:9999 Linux vm-104-33.eng.dev.com
> Host Name      : 10.108.104.33
> Delegate IP    : 10.108.104.34
> Pgpool port    : 9999
> Watchdog port  : 9000
> Node priority  : 1
> Status         : 7
> Status Name    : STANDBY
> 
> 
> 
> *Thanks & Regards*
> *Gopi*


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
https://www.sraoss.co.jp/