[Pgpool-general] Maintain connections if backend fails

[Tatsuo Ishii]

> 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? 

No. This is an expected behavior of pgpool-II.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp