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

[Tatsuo Ishii]

> 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