Re: Combination with priority-based and quorum-based synchronous replications

On Thu, Aug 24, 2017 at 12:38 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> PostgreSQL 9.6 introduced the priority-based multiple synchronous
> replication and PostgreSQL 10 introduced the quorum-based one.
> Initially I was thinking to use both synchronous replication ways in
> combination but it's not supported yet for now. It's useful for some
> use cases where for example we have three replicas: nodeA, nodeB and
> nodeC. Two of them (nodeA and nodeC) are for read replica and another
> one (nodeB) is for the backup. In this case we would want to replicate
> the data synchronously to nodeB while replicating data to the nodeA
> and nodeC using quorum-based synchronous replication. To cover such a
> use case we need a feature allowing us to use both in combination.
> IIUC other use cases are also mentioned on earlier discussion.

Yes, I would imagine cases where users are two sites with a set of
standbys in each to be a common use-case for this feature, though
complicated (are there that many people with such complicated
scenarios anyway?), it is possible to failover to one site or the
other, and be able to have at hand a set of read-only standbys at hand
just after the promotion of the new primary on one of the two sites.

> To implement there are two ideas.
> 1. Use two synchronous replication ways in combination. For above
> example, we can set s_s_names = 'First 2 (nodeB, Any1(nodeA, nodeC))'.
> This approach allows us to set a nested set of nodes in s_s_names. We
> can consider supporting the more nested solution but one nested level
> would be enough for most cases. Also, it would be useful if one
> synchronization method can have multiple another synchronization
> method. For example, I can imagine a use case where each two backup
> data centers have two replicas and we want to send the data
> synchronously either one replica on each data center. We can set
> s_s_name = 'First 2( Any 1(nodeA, nodeB), Any 1(nodeC, nodeD))'. It
> might be over engineering but it would be worth to consider it.

That would cover the use-case written above. After a failover, it may
count to have ready-to-use standbys at the same site as the new
primary. Cascading replication could be used, but you really don't
want a F with a single standby entry connecting to the primary on each
backup sites either.

> 2. Extend quorum-based synchronous replication so that we can specify
> the node that we want to replicate the data synchronously. For above
> example, if we can set s_s_names = 'Any 2 (nodeA, *nodeB, nodeC)' then
> the master server wait for nodeB and either nodeA or nodeC. That is, a
> special mark '*' means that the marked nodes is preferentially
> selected as synchronous server. This approach is more simpler than #1
> approach but we cannot nest more than one and a synchronization method
> cannot have an another method..

You cannot either select multiple groups, which sounds limited to me.
It's hard to imagine people using more than one level of nesting as
well.

> Feedback and comment are very welcome.

Approach 1. has more promises, no need to have more than 1 level of
nesting. I would be curious to know if there are many people
interested in such more complex availability strategies as well or if
that's a niche case. For example, I would not need such complexity for
what develop lately as there are already enough complexities to
consider with one level of standbys.
--
Michael