pgpool and slony

"Josh Harrison" <joshques@gmail.com> writes:

This is a newbie pgpool question.
...
sorry if my question is silly

It doesn't sound silly, but this isn't the best place to ask it.
The slony project has its own mailing list(s) and you'll get
better-informed answers there. Not sure if pgpool has its own list.

regards, tom lane

Hi,
This is a newbie pgpool question. I have 2 servers (master-slave) and Slony
is doing the replication. I had installed pgpool for load balance where i
had disabled the replication_mode=false and load_balance_mode=true in
pgpool.conf

pcp_node_info gives this output.

-sh-3.00$pcp_node_info 10 localhost 9898 josh postgres 1
172.31.0.67 5432 2 16383.500000

-sh-3.00$ pcp_node_info 10 localhost 9898 josh postgres 0
172.31.0.68 5432 2 16383.500000

So does this mean that pgpool is working properly with slony-1.

You need to trun on master_slave_mode. This will ensure that write
queries are sent to only master (remember that Slony's slave does not
accept write queries).

Also how do
I test to see if the load balancing with pgpool is working properly. What
kind of query will help me confirm that it is load balanced ?

Turn on query logging on PostgreSQL and look into the log file.

Also will pgpool automate failover? How can I test that?

For Slony slave, it's easy. Stop the slave's postmaster using pg_ctl
command. I recommend to use -m f option.

For Slony master, it's a little complicated. In this case Slony
requires user to manualy promote one of slaves to new master (correct
me if I am wrong). While pgpool automaticaly detach the broken master
and chose the second PostgreSQL ("second" means written in the second
place in pgpool.conf") and regard it as a new master. So there needs
some communication between pgpool and Slony. As of pgpool-II 2.0，a
new directive called "failover_command" is added. User can specify
shell command to be executed when a node is detached by pgpool.

You can specify following in the directive:
# special values: %d = node id
# %h = host name
# %p = port number
# %D = database cluster path
# %% = '%' character

So you could invoke some Slony management command by using above to
sync pgpool and Slony(I'm not familiar with Slony, and hope someone
help us in this regard).

sorry if my question is silly

Not at all. Thanks for testing pgpool.

On Wed, 19 Dec 2007, Tom Lane wrote:

Not sure if pgpool has its own list.

There's a moderately active general user list as part of the set at
http://pgfoundry.org/mail/?group_id=1000055

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD