[pgpool-hackers: 4376] Re: Load balancing after failover not working in 3-node HA PostgreSQL cluster

[Tatsuo Ishii]

You need to set backend_weight0 to non 0.
e.g.

backend_weight0 = 0.5

Note that you don't need to change backend_weight[0-2] before/after failover.

> Just make my last message clearer,
> 
> When I first setup the cluster these were the properties I was using
> 
> backend_weight0 = 0
> 
> backend_weight1 = 0.5
> 
> backend_weight2 = 0.5
> 
> load_balance_mode = on database_redirect_preference_list = '.*:standby'
> Load balancing after failover did not work. After failover, I manually set
> the backend_weight1 = 0 and backend_weight2 = 1, and load balancing started
> to work.
> 
> 
> 
> On Wed, Aug 16, 2023 at 5:29 PM Salman Ahmed <salman.ahmed at stormatics.tech>
> wrote:
> 
>> I did as you suggested, I set the  database_redirect_preference_list value
>> in pgpool.conf to
>>
>> database_redirect_preference_list = '.*:standby'
>>
>> but load balancing did not work after failover, however when I manually
>> set the backend_weight1 = 0 and backend_weight2 = 1 after failover, the
>> load balancing started to work.
>>
>> On Wed, Aug 16, 2023 at 1:02 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>>
>>> > Dear pgpool-hackers,
>>> >
>>> > We’re currently working on pg_cirrus - a simple tool to setup 3-node HA
>>> > PostgreSQL clusters using pgpool. We’re facing an issue i.e. load
>>> balancing
>>> > after failover does not work.
>>> >
>>> > When pg_cirrus sets up 3-node HA cluster within the same subnet, load
>>> > balancing works seamlessly as expected i.e. write operations go to
>>> primary
>>> > node and read operations go to standby nodes, but after primary node
>>> goes
>>> > down the read and write queries both land on the same newly promoted
>>> > primary node.
>>> >
>>> > We’re using the following load balancing properties in pgpool.conf:
>>> >
>>> > backend_weight0 = 0
>>> >
>>> > backend_weight1 = 0.5
>>> >
>>> > backend_weight2 = 0.5
>>> >
>>> > load_balance_mode = on
>>> >
>>> > Is this the expected behavior or are we doing something wrong, kindly
>>> guide
>>> > us.
>>>
>>> Assuming the initial primary is node 0 and the new primary is node 1,
>>> it seems pgpool behaves as expected. After failover, node 0 becomes
>>> new standby with weight 0, which means no read query is routed to node
>>> 0. In the mean time node 1 becomes the new primary with weight 0.5,
>>> which means some of read queries are routed to node1, new primary.
>>>
>>> If you want to keep on routing no read query being sent to primary
>>> node, you can use database_redirect_preference_list. For example,
>>>
>>> database_redirect_preference_list = '.*:standby'
>>>
>>> will route read queries to one of standby servers (which standby is
>>> chosen is depending on the weight parameter), i.e. no read query is
>>> routed to primary. This behavior is kept regardless which node is
>>> assigned to the primary. See manual for more details.
>>>
>>> Best reagards,
>>> --
>>> Tatsuo Ishii
>>> SRA OSS LLC
>>> English: http://www.sraoss.co.jp/index_en/
>>> Japanese:http://www.sraoss.co.jp
>>>
>>