[Pgpool-general] TCP connections are *not* closed when a backend timeout

[Tatsuo Ishii]

> Hi,
> 
> It works just perfectly :) The connections are closed and the recovery
> is done just well !

Thanks for the report!

> Thanks for that.

You are welcome.

> I have a few other questions about pgpool :
> 
> - Why pgpool doesn't monitor when a backend comes back ? Must we have an
>   external monitoring software which call pcp_recoevr_node ?

This is a design decision when pgpool was born. In my understanding
many HA(High Availability) software systems do like this. The reason
for this is if we allow this, some flakey hardwares (for example
flakey network cables) might cause repeating fail over/fail back
actions.

> - I would set some redudancy on my pgpool servers. ie: I would have 2
>   pgpool servers which uses 2 postgresql backend. It seems to work
>   pretty well with the test I made. My question is : if a backend server
>   fails, which pgpool server should/must perform the recovery ? The schema
>   should be :
> 
>   pgpool1 ==> master: backend1 , slave : backend2
>   pgpool2 ==> master: backend2 , slave : backend1

Besides recovery, having different master/slave settings on those two
pgpool servers might cause deadlock situation. Consider following
scenario: pgpool1 sends "LOCK t1" to backend1 first since backend1 is
the master for pgpool1 then sends the same SQL to backend2. At the
same time pgpool2 sends "LOCK t1" to backend2 first since backend2 is
the master for pgpool2 then sends the same SQL to backend1. As a
result pgpool1 waits for completion of LOCK command on backend2, while
pgpool2 waits for completion of LOCK command on backend1, which is a
deadlock.

I recommend you to have same settings for both pgpool1 and pgpool2:

   pgpool1 ==> master: backend1 , slave : backend2
   pgpool2 ==> master: backend1 , slave : backend2

Now for the recovery part. The answer is either pgpool1 or ppool2 can
perform the recovery. However while pgpool1(2) performs the recovery
pgpool2(1) *must not* access backend1 and backend2.

> - A more general question, my archive logs take lot of place. How much
>   archive_log files must we keep ? only the last ? Is there any
>   postgresql configuration capacities to set this rotation, or must I
>   use smthg like logrotate?

Actually once you finish the recovery, you do not need to keep take
archive logs. There are two ways for this. Change archive_mode to
off(require PostgreSQL restarting). The other is set ''(null string)
to archive_command. This will not require PostgreSQL restarting. Just
pg_ctl reload is enough. I'm not sure if this works for all PostgreSQL
versions though.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> Thanks for all you help
> 
> Maxence
> 
> Le Sun, Jul 13, 2008 at 09:41:51AM +0900, Tatsuo Ishii à écrit :
> > > Hi,
> > > 
> > > I'm working on recovery  with pgpool.
> > > When a backend failed (ie, for exemple, when the postgresql server shuts
> > > down), all seems OK, connections are closed and backend is set as down
> > > (status 3).
> > > 
> > > My problem is in case of network problem. If I remove the network link
> > > with the backend, pgpool correctly detects it as down when healthcheck
> > > timeout but it does *not* close tcp connections to remote backend.
> > > 
> > > The problem is that when the link comes back and when I start
> > > pcp_recovery_node, the second stage can't process because there are
> > > existing connections to backend ...
> > > 
> > > Is this a normal thing? Or is this a bug ?
> > > 
> > > I'm trying to find how I can close the connections when healthcheck
> > > timeout...
> > 
> > Thanks for the report.
> > 
> > If in case of network problem, the underlying TCP/IP stack is keeping
> > retrying, and the only safe way to shutdown the connection is
> > restarting the process. Even if we safely close the limbo connection,
> > we need to pass the limbo connection info from parent to child process
> > (remember that health checking is done in parent, and child is keeping
> > the connection). 
> > 
> > This is not problem if master goes does down, since all children will
> > restart anyway. I guess you remove the netork cable for nodes other
> > than master. The difference here is pgpool tries to minimize
> > restarting children. If master does not fail, pgpool will not do the
> > restarting.
> > 
> > From your report I think this logic si wrong and we need to restart
> > children in *any* case. Included is the patch for this. Could you
> > please try it out?
> > --
> > Tatsuo Ishii
> > SRA OSS, Inc. Japan
> 
> > Index: main.c
> > ===================================================================
> > RCS file: /cvsroot/pgpool/pgpool-II/main.c,v
> > retrieving revision 1.37
> > diff -c -r1.37 main.c
> > *** main.c	22 May 2008 14:36:38 -0000	1.37
> > --- main.c	13 Jul 2008 00:29:59 -0000
> > ***************
> > *** 1224,1229 ****
> > --- 1224,1230 ----
> >   	{
> >   		pool_error("failover_handler: no valid DB node found");
> >   	}
> > + #ifdef NOT_USED
> >   	else
> >   	{
> >   		if (Req_info->master_node_id == new_master && *InRecovery == 0)
> > ***************
> > *** 1256,1262 ****
> >   			return;
> >   		}
> >   	}
> > ! 
> >   	/* kill all children */
> >   	for (i = 0; i < pool_config->num_init_children; i++)
> >   	{
> > --- 1257,1263 ----
> >   			return;
> >   		}
> >   	}
> > ! #endif
> >   	/* kill all children */
> >   	for (i = 0; i < pool_config->num_init_children; i++)
> >   	{
> 
> 
> -- 
> Maxence DUNNEWIND
> Contact : maxence at dunnewind.net
> Site : http://www.dunnewind.net
> 02 23 20 35 36
> 06 32 39 39 93
> 
> S'il vous plaît, évitez de m'envoyer des pièces jointes au format Word ou PowerPoint.
> Voir http://www.gnu.org/philosophy/no-word-attachments.fr.html