[pgpool-general: 7521] Re: Recovery after failure on a single backend

[Emond Papegaaij]

On Fri, Apr 23, 2021 at 3:19 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> > In our application we support both a clustered setup and a setup with
> > a single node. In the situation with a single node, we still put
> > pgpool between the application and the database for consistency. This
> > means pgpool runs as a standalone node, with no watchdog and only a
> > single backend. This works fine most of the time, however, if for some
> > reason the database (or the connection to it) fails, pgpool marks the
> > backend as invalid and never recovers. This will make a small hickup
> > in the network to cause permanent outage of the service until pgpool
> > is restarted. Is there any way to make pgpool reconnect to the
> > database in this setup to recover from this situation?
>
> There are two ways:
>
> 1) Enable disallow failover flag.
>
> backend_flag0 = 'DISALLOW_TO_FAILOVER'
>
> Upon failure to the backend, you will be getting following error:
> psql: error: FATAL:  failed to create a backend connection
> DETAIL:  executing failover on backend
>
> Once the backend (or connection to the backend) comes up again, you
> can connect to pgpool again. In this case failover never completes.

Thanks for your reply. This indeed seems to work. I think it also
makes sense in our single node setup. There is only one database in
that case and there is nothing to failover to.

> 2) Enable auto_failback. Unlike #1, failover completes. But after some
> period, the backend automatically becomes online again if connection
> to the backend becomes healthy again.

The documentation states that this option relies on streaming
replication. We use this option in our cluster setup to automatically
reattach backends when streaming is in sync again. I don't think it
will work with a single backend because pgpool has no way of detecting
that the connection is healthy again. The health checks don't seem to
run on detached nodes.

PS. Sorry for the double post. Gmail by default replies to the author
not to the list. Very annoying.

Best regards,
Emond