[pgpool-general: 8797] Re: Difference between pgpool 'primary' and Watchdog 'MASTER' versus the 'load_balance_node'
Tatsuo Ishii
ishii at sraoss.co.jp
Fri Jun 2 10:09:16 JST 2023
> 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
>>
More information about the pgpool-general
mailing list