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
inpgpool.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.