[Pgpool-general] Maintain connections if backend fails

[Joaquin Llano]

Hi. 

I'm using Pgpool2 with Streaming Replication over 2 PostgreSQL nodes. I have one server with Pgpool 3.0.3 and two servers with PostgreSQL 9.1. All works with Debian Squeeze (6.0.3) and Backports Repositories. 

If one of the back-ends nodes fails, clients connected to Pgpool are disconnected with lost connection message. 

For example: 

I connect to Pgpool: 
user at client_machine$ psql -h www.xxx.yyy.zzz -p 5433 postgres postgres 

postgres=# \l 

LOG: sentencia: SELECT d.datname as "Name", 
pg_catalog.pg_get_userbyid(d.datdba) as "Owner", 
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", 
d.datcollate as "Collate", 
d.datctype as "Ctype", 
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" 
FROM pg_catalog.pg_database d 
ORDER BY 1; 
LOG: duración: 2.247 ms 
Listado de base de datos 
Nombre | Dueño | Codificación | Collate | Ctype | Privilegios 
-----------+----------+--------------+-------------+-------------+----------------------- 
postgres | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | 
prueba1 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | 
prueba2 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | 
template0 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | =c/postgres + 
| | | | | postgres=CTc/postgres 
template1 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | =c/postgres + 
| | | | | postgres=CTc/postgres 
(5 filas) 

So, if I turn off PostgreSQL daemon on one of the backends: 
root at postgresql2:~# /etc/init.d/postgresql stop 
Stopping PostgreSQL 9.1 database server: main. 

If I try show databases I get this lost connection error: 
postgres=# \l 
La conexión al servidor se ha perdido. Intentando reiniciar: con éxito. 

If I rerun the query its works: 
postgres=# \l 
LOG: sentencia: SELECT d.datname as "Name", 
pg_catalog.pg_get_userbyid(d.datdba) as "Owner", 
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", 
d.datcollate as "Collate", 
d.datctype as "Ctype", 
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" 
FROM pg_catalog.pg_database d 
ORDER BY 1; 
LOG: duración: 1.834 ms 
Listado de base de datos 
Nombre | Dueño | Codificación | Collate | Ctype | Privilegios 
-----------+----------+--------------+-------------+-------------+----------------------- 
postgres | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | 
prueba1 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | 
prueba2 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | 
template0 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | =c/postgres + 
| | | | | postgres=CTc/postgres 
template1 | postgres | UTF8 | es_ES.UTF-8 | es_ES.UTF-8 | =c/postgres + 
| | | | | postgres=CTc/postgres 
(5 filas) 


Is there any way for not loosing connections when one of the back-end nodes fails? 

Thanks for your help and sorry for my bad English. 
-- 

Joaquín Manuel Llano Montero 

________________________________________ 

C O MPAREX España S.A. 
C/ Severo Ochoa, 20 
06800 Mérida (Badajoz) España 
phone: +34 924 373 573 
fax: +34 924 374 518 
mobile: +34 615 848 019 
email : joaquin.llano at comparex.es 
web: www.comparex.es 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20111122/8c76d263/attachment.html>