1

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
3

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.

  • There are two sample files of pgpool.conf for master-slave mode viz. pgpool.conf.sample-master-slave and pgpool.conf.sample-stream. First one has to be used when replication software is slony and the latter is for streaming replication. I was wrongly using the first one. When I used the second file as a reference, everything has started working smooth. We don't need to worry about sr_check_period explicitly as it comes with non-zero default value. – ramanadh Nov 6 '15 at 8:53
0

backend_hostname1 = 'master-postgres-ip' - > This should point to your slave, not the master again.

  • I edited my question now. Sorry for that. In my actual setup, I am rightly pointing to slave IP. – ramanadh Nov 4 '15 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.