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

[Bo Peng]

Hi,
 
> Can you please clarify the below?
> *During failover, Is the '*primary*' or '*MASTER*' or ' *load_balance_node*
> failure taken into account?*

Sorry for the late reply.

Your question is whether the role of *primary* or *MASTER* or  *load_balance_node*
is switched over to other nodes during failover of PostgreSQL?

> On Tue, May 16, 2023 at 10:56 AM Gopikrishnan <nksgopikrishnan at gmail.com>
> wrote:
> 
> > Thank you for your response!
> >
> > Can you also kindly clarify this:
> >  During failover, Is the '*primary*' or '*MASTER*' or '
> > *load_balance_node* failure taken into account?
> >
> > *Thanks*
> > *Gopi*
> >
> >
> > On Tue, May 16, 2023 at 10:26 AM Bo Peng <pengbo at sraoss.co.jp> wrote:
> >
> >> 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/
> >>
> >


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