On Thu, Aug 24, 2017 at 12:38 PM, Masahiko Sawada <sawada.m...@gmail.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 -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers