PostgreSQL – Fixing pgpool Load Balancing Sending All Queries to Master

load balancingpgpoolpostgresql

My two postgresql servers are configured for streaming replication, which is working fine.

Pgpool is configured for Master Slave mode / Load Balance Mode.

pgpool.conf:

listen_addresses = '*'
port = 9999
backend_hostname0 = 'master-postgres-ip'
backend_port0 = port-no
backend_weight0 = 1
backend_data_directory0 = 'data-dir'
backend_hostname1 = 'slave-postgres-ip'
backend_port1 = port-no
backend_weight1 = 1
backend_data_directory1 = 'data-dir'
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode='stream'

I expected all write queries will go to primary and read queries will be distributed between two. But, all the queries are going only to master. However, if I stop master, queries are going to slave.

Can somebody tell me what might be going wrong?

pgpool gives below log on startup:

2015-11-03 17:25:56: pid 21284: LOG:  find_primary_node: checking backend no 0
2015-11-03 17:25:56: pid 21284: LOG:  find_primary_node: checking backend no 1
2015-11-03 17:25:56: pid 21284: DEBUG:  SSL is requested but SSL support is not available
2015-11-03 17:25:56: pid 21284: DEBUG:  authenticate kind = 3
2015-11-03 17:25:56: pid 21284: ERROR:  failed to authenticate
2015-11-03 17:25:56: pid 21284: DETAIL:  invalid authentication message response type, Expecting 'R' and received 'E'
2015-11-03 17:25:56: pid 21284: DEBUG:  find_primary_node: no primary node found

Best Answer

Initially, I have set sr_check_period in pgpool.conf to 0 (zero) to disable SR checking. I thought of avoiding this check and invariably balance the load. But this has caused the problem.

After I have given a non-zero value to this attribute, load balancing started working.