[pgpool-general: 7865] Re: Rejecting database mutations without a quorum

[Emond Papegaaij]

On Mon, Nov 8, 2021 at 12:52 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> >> One of the difficulties with this kind of approach is, how to decide
> >> that the primary should be killed in a reliable way.
> >>
> >> Suppose we have:
> >>
> >> node 1: pgpool/watchdog 1, primary PostgreSQL
> >> node 2: pgpool/watchdog 2, standby PostgreSQL
> >> node 3: pgpool/watchdog 3, standby PostgreSQL
> >>
> >> The scenario:
> >>
> >> 1) Watchdog 1 lost communication to watchdog 2 and watchdog 3. So
> >>    watchdog 1 lost quorum.
> >>
> >> 2) However communication between pgpool 1/2 and the primary is still
> >> healthy.
> >>
> >> 3) Watchdog 1 decides to kill the primary and the primary goes down.
> >>
> >> 4) pgpool 1/2 have to promote one of the standbys to create new primary.
> >
> >
> > Yes, this always is the issue with communication within a cluster, or
> more
> > specifically the loss of communication. That's why I wanted to discuss
> this
> > here. What happens to a VIP in your scenario if watchdog 1 started with
> the
> > VIP assigned? Will watchdog 1 drop the VIP when it losses the quorum?
>
> Yes.
>
> > Is
> > the pgpool 1 still supposed to receive queries when it can no longer be
> > sure it is sending them to the correct database?
>
> I am not sure what you mean here. It's user's responsibility to send
> queries to a pgpool which does not have quorum but Pgpool-II assumes
> that clients sends queries to VIP.
>

What I meant here was, if a Pgpool that is not part of a quorum (and
therefore should no longer hold the VIP), receives a query, should it
process this query? In a 'normal' setup, it should not get the query, but
if it does, what should it do with it? It could be that the VIP was not
correctly reassigned, or a system connecting to Pgpool is configured
incorrectly. I would say, it would be nice if Pgpool could be configured to
reject any queries when it cannot be sure it is sending them to the correct
database.

> In our setup, what I would ideally see is that I can configure pgpool to
> > not send writes (or completely disconnect all its backends) when the
> > instance is not part of a quorum. Also, it would be really great if I can
> > configure a command when the watchdog status changes. That would allow me
> > to implement custom behavior myself.
>
> Maybe you misunderstand Pgpool-II's architecture. In a Pgpool-II
> cluster, Pgpool-II and PostgreSQL processes are logically separated
> objects. Whether Pgpool-II and PostgreSQL processes are running on a
> separate host or not is irrevant to the functionality of the cluster.
>

Yes, I'm aware of this. In our situation, we currently have a database and
a Pgpool on every node, but that's purely our setup. It is very likely we
will allow this to be changed in the future, with support for nodes that
only run the database or special arbiter nodes that only run Pgpool.
However, we cannot use a VIP as all nodes are linked together using a
wireguard network on which we cannot have VIPs.

On the other hand I understand your motivation. Allowing to have a
> command which is called when watchdog quorum status changes might be a
> good idea.
>

This would be of tremendous help for us, because it will allow us to plug
in our own behavior driven directly by Pgpool. This would eliminate the
need to poll Pgpool, which I fear will be too unreliable. If a poll fails
due to a short glitch, I do not want to bring down a node right away. Also,
if the process doing the polling fails for whatever reason, the whole setup
will be at risk of corruption.

Best regards,
Emond
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20211108/ba7ff9b1/attachment.htm>