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

[Salman Ahmed]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20230816/366b9547/attachment-0001.htm>