[Pgpool-hackers] [Pgpool-general] pgpool limitations

[Tatsuo Ishii]

>     I have configured pgpool 3.0.3 in master/slave mode and setup streaming
Note that pgpool-II 3.0.4 is the latest stable release.

> replication between the master and the slave systems. I want to be able to
> add more slaves to the setup whenever read-only load increases beyond the
> current capacity, and remove slave databases when the load subsides.
> 
>     I have found the following limitations, and wanted to check with you
> guys if these are really true or is it just me that I misconfigured pgpool.

They are all expected behavior.

> .) Killing one slave Postgres instance (in immediate mode) kills all
> connections to the pool, even those connections whose queries are currently
> running on a different Postgres instance which is still available.
> 
>     In essence a connection loss from a slave backend causes pgpool to
> reinitialize itself, hence breaking all existing connections.
> 
> .) The commands pcp_attach_node and pcp_detach node cause the pool to
> disconnect all connections and reinitialize the pool.
> 
> .) Adding a new entries backend_hostname<n> and backend_port<n> to the end
> of the current node list in pgpool.conf and reloading the config does add
> the new node to pgpool (as documented)l, but the pcp_node_info command shows
> that this new node is in 'down' state (code 3) and the docs don't say how to
> bring a node from 'down' state to 'up' state (code 1 or 2). Doing
> pcp_node_attach does bring it in 'up' mode but as noted above, this command
> causes all connections to be disconnected.

Because newly added node is likely to have out of sync databases. Thus
bringing it online immediately will allow user to use obsolete
databases. You should do online recovery first. 

>     How can I add/remove slave databases to pgpool cleanly without affecting
> the connections that are working on other databases.

It's impossible with pgpool-II 3.0.x.

Upcoming pgpool-II 3.1 will not disconnect connections after online
recovery.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp