[Pgpool-general] Problems configuring pgpool-II 3.0 to failover gracefully and load balance queries

[Stephen Sugden]

Hello everybody,

I am trying to configure pgpool-II to load balance queries and provide
failover for 2 (or more) identical backend servers. The application that
will access pgpool only does SELECT queries. The back end servers are
written to directly by a custom ETL process (e.g. no slony or pg9
replication).

In my tests I have observed the following behaviour:

- Load Balancing appears to load balance incoming connections, not queries.
If I open a single connection and execute a series of select queries, they
all get routed to the same backend. If I open up multiple connections, both
backends are used.
- When a node shuts down the pool drops *all* connections. (but subsequent
connections are correctly only sent to the backend that is still up)
- re-adding a node to the pool using pcp_attach_node causes the pool to
reset and all current connections will be closed

Is this expected behaviour? I would hope that at least re-attaching a node
could happen without interrupting existing connections. It's possible I've
misunderstood the documentation, so any feedback on my configuration would
be most appreciated.

In my config file I have the following:

load_balance_mode = true
white_function_list = ''
black_function_list = 'nextval,setval'
print_timestamp = true
master_slave_mode = true
master_slave_sub_mode = 'slony'
delay_threshold = 10
log_standby_delay = 'none'
connection_cache = true
health_check_timeout = 20
health_check_period = 5
health_check_user = 'postgres'
#
failover_command = ''
#
failback_command = ''
fail_over_on_backend_error = true
insert_lock = true
ignore_leading_white_space = true
log_statement = false
log_per_node_statement = false
log_connections = false
log_hostname = false
pgpool2_hostname = '10.100.20.120'
system_db_hostname = 'localhost'
system_db_port = 5433
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = ''
backend_hostname0 = '10.100.20.121'
backend_port0 = 5432
backend_weight = 0
backend_hostname1 = '10.100.20.122'
backend_port1 = 5432
backend_weight1 = 1
enable_pool_hba = false
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
lobj_lock_table = ''
ssl = false
debug_level = 0
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20110221/61ed3f5a/attachment.html>