Re: Understanding streaming replication

Pawel Veselov wrote:
>>> QUESTION: If multiple pgpools are running, and if there are no network problems,
>>> and configuration
>>> files are identical, is there any guarantee that the same stand-by node will be selected for
>>> promotion? Concern here is that with configuration of (M-SB0-SB1) one pgpool decides
>>> to promote SB0
>>> and another - SB1, causing both of them to enter master mode, and splitting the cluster.
>>> It does look
>>> that pgpool will always select next "alive" node for promotion, but I couldn't find
>>> a definitive statement on that.
>>
>> I don't know about pgpool and its abilities to handle
>> cluster failover, but I wouldn't go this way at all.
>> Even if the answer were that in the circumstances you
>> describe things would work, you can depend on it that
>> things will go wrong in ways different from what you
>> expect, e.g. a broken network card.
>> The consequences would be worse than I'd like to imagine.
>
> I would imagine this situation will happen in any case, I don't logically see how it's avoidable. If
> you only have one agent that has power to promote a node to be a new master, you have SPF. If you have
> multiple agents that can do the promotion, there is always a risk that they fall out of sync.

Cluster software usually has the cluster nodes communicate
regularly, and if anything fails, the nodes try to form
groups where everybody can reach everybody else.
The group that is bigger than half of the original
nodes wins, turns off the others and takes over their
services.

>> If you want reliable automatic failover, consider cluster
>> software.
>
> Anything you could please recommend?

The only thing I have seen is RedHat's Cluster Suite, which
is commercial. I would recommend to have at least three nodes
though, because the two node cluster we had was subject to
spurious failovers on short quorum disk hiccups.

Yours,
Laurenz Albe