[pgpool-general: 8800] Re: Difference between pgpool 'primary' and Watchdog 'MASTER' versus the 'load_balance_node'
Gopikrishnan
nksgopikrishnan at gmail.com
Fri Jun 2 15:04:39 JST 2023
Thank you for the response, Ishii san.
*Thanks*
*Gopi*
On Fri, Jun 2, 2023 at 6:39 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> > Thank you for the clarifications.
> >
> > So my understanding currently is :
> > -----------
> > 1. database '*primary*' node failure is the event controlling the
> failover
> > process. (executing failover.sh)
>
> Not only primary, but failure of streaming replication standby
> PostgreSQL triggers failover (thus executes failover.sh).
>
> > 2. Watchdog `*MASTER*` failure only causes the VIP to move to another
> node.
> > That is unless of course watchdog master is also the
> > current pgpool primary node, in which case a failover will be triggered.
>
> In this case one of follower watchdog nodes takes over VIP, and
> one of streaming replicatoin standby PostgreSQL servers takes over the
> role of streaming replication primary node.
>
> > 3. *esc.sh* and *desc.sh *only concerns the watchdog MASTER/STANDBY
>
> Yes.
>
> > Am I correct?
> >
> > *Thanks*
> > *Gopi*
> >
> >
> > On Mon, May 29, 2023 at 2:10 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> >
> >> >> Hi pgpool experts,
> >> >>
> >> >> Can you please clarify the below?
> >> >> *During failover, Is the '*primary*' or '*MASTER*' or '
> >> *load_balance_node*
> >> >> failure taken into account?*
> >> >
> >> > It's hard to answer your question as the question is too vague.
> Anyway I
> >> try it...
> >> >
> >> > - primary failure is taken into account because it triggers failover.
> >> > See
> >> https://www.pgpool.net/docs/44/en/html/runtime-config-failover.html for
> >> more details.
> >> >
> >> > - MASTER and other watchdog nodes are taken into account in failover.
> >> > See
> >> >
> >>
> https://www.pgpool.net/docs/44/en/html/runtime-watchdog-config.html#CONFIG-WATCHDOG-FAILOVER-BEHAVIOR
> >> > for more details.
> >> >
> >> > - Load balance node is not taken into account in streaming replication
> >> mode.
> >> > See note #2 in
> >> https://www.pgpool.net/docs/44/en/html/runtime-config-failover.html for
> >> more details.
> >>
> >> Sorry, I meant "Load balance node is taken into account".
> >>
> >> >>
> >> >> *Thanks*
> >> >> *Gopi*
> >> >>
> >> >>
> >> >> 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/
> >> >>>>
> >> >>>
> >> > _______________________________________________
> >> > pgpool-general mailing list
> >> > pgpool-general at pgpool.net
> >> > http://www.pgpool.net/mailman/listinfo/pgpool-general
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20230602/451e8b2d/attachment.htm>
More information about the pgpool-general
mailing list