[Pgpool-general] Problem with load balancing + automatic failover

[Michael SALOMON]

Hi everybody and thank you for your wonderful work with PGPool.

I'm trying to use latest version of PgPool-II 2.1-beta2 to build a
replication system between 3 database nodes (1 master + 2 slaves).

The replication part is actually handled by Slony.

Here are some revelant informations from my MASTER pgpool.conf file :

replication_mode = false
load_balance_mode = true
master_slave_mode = true
failover_command = ''
failback_command = ''

ON MASTER :
pgpool2_hostname = 'master'

backend_hostname0 = 'master'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/some/path'

backend_hostname1 = 'slave1'
backend_port1 = 5432
backend_weight1 = 0
backend_data_directory1 = '/some/path'

backend_hostname2 = 'slave2'
backend_port2 = 5432
backend_weight2 = 0
backend_data_directory2 = '/some/path'

ON SLAVE1 :
pgpool2_hostname = 'slave1'

backend_hostname0 = 'master'
backend_port0 = 5432
backend_weight0 = 0
backend_data_directory0 = '/some/path'

backend_hostname1 = 'slave1'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/some/path'

backend_hostname2 = 'slave2'
backend_port2 = 5432
backend_weight2 = 0
backend_data_directory2 = '/some/path'

ON SLAVE2 :
pgpool2_hostname = 'slave2'

backend_hostname0 = 'master'
backend_port0 = 5432
backend_weight0 = 0
backend_data_directory0 = '/some/path'

backend_hostname1 = 'slave1'
backend_port1 = 5432
backend_weight1 = 0
backend_data_directory1 = '/some/path'

backend_hostname2 = 'slave2'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/some/path'


I assume here that all three Postgres servers are hosted on three different
computers. I setup the backend's weight like this
to make sure that all SELECT statements are handled by the local database
(master if statement is run on master, slave1 if run on slave1, etc)

Moreover, if MASTER goes down, i want SLAVE1 to be granted master, and so
on.

Finally, slony is not running in this scenario...

PROBLEM :

When I stop master database, the health checker correctly detects it and
switch over the slave1 db correctly. Indeed, if I try to insert some data on
master pgpool
 server, I can see that this data is actually inserted on slave1 database.
Fair enough..

Fact is, as backend_weight0 = 1 on MASTER server, there is no way I can run
a SELECT statement on MASTER pgpool server, because it is 100% load balanced
to
 the actually failed database, and then I end up with :

- master server -> pgsql -h master -p 9999 -d rep_db -c 'SELECT * FROM
my_table'
psql:/usr/local/pgsql/sql/select.sql:2: server closed the connection
unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
psql:/usr/local/pgsql/sql/select.sql:2: connection to server was lost

I would like pgpool-II to load balance the query to the failover server,
that is to say slave1

When I used to play with pgpool-I , I did not have this kind of problem, and
the automatic failover between two nodes worked perfectly fine, so I wonder
if somehow i
 did something wrong in my configuration.

Whatever kind of help would be really appreciated, I would really like to
use PGPOOL on our production !

Thanks a lot in advance and keep up the good work !

Kind regards

Michael SALOMON
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://pgfoundry.org/pipermail/pgpool-general/attachments/20080429/5aeff16a/attachment.html