[pgpool-general: 7742] Re: Recovery after loosing connection to all backends

[Bo Peng]

Hello,

> > > During testing of some of the more intense failure scenario's we
> > sometimes
> > > end up with a cluster where all backends are down according to pgpool. We
> > > run in a cluster with 3 nodes, all running a pgpool and a postgresql
> > > instance. In some situations when the pgpool instances lose connection to
> > > the postgresql backends, they are all marked as down and the cluster
> > never
> > > recovers. To make things worse, this state is shared between the pgpool
> > > nodes, so even restarting the pgpool instances one by one does not help.
> > > Once you enter this state, the only way out is to stop all pgpool
> > instances
> > > and then start them again, to clear the cluster and have pgpool start
> > with
> > > a fresh state. Is there any way to trigger pgpool to re-evaluate the
> > status
> > > of a backend, even when the cluster think it's down?
> >
> > If pgpool detected the failure of PostgreSQL, pgpool will detach the
> > PostgreSQL from cluster
> > and mark the status as "DOWN".
> > After you recover the PostgreSQL, you need to use "pcp_attach_node"
> > command to
> > attach the PostgreSQL to cluster.
> >
> 
> This requires a manual action, which we are trying to prevent.
> 
> 
> > Alternatively, you can turn on "auto_failback" parameter to
> > automaticly reattach a "healthy" PostgreSQL.
> >
> 
>  We've got auto_failback enabled. However, this only works if pgpool has an
> active connection to at least one database. We've also configured a
> periodic health check.

Yes. auto_failback is used to automatically detect a healthy standby node.

> Why can't pgpool use this health check to detect a
> database is up and automatically reattach it?

Because there is no safe way to automatically recover a primary node.
You need to restart pgpool or attach the nodes manually. 

> Best regards,
> Emond


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan
http://www.sraoss.co.jp/