Re: Support for N synchronous standby servers - take 2 - Mailing list pgsql-hackers

On Wed, Oct 14, 2015 at 3:02 AM, Masahiko Sawada wrote:
> On Sat, Oct 10, 2015 at 4:35 AM, Robert Haas wrote:
>> On Fri, Oct 9, 2015 at 12:00 AM, Amit Kapila wrote:
>>> Sounds like both the approaches have some pros and cons, also there are
>>> some people who prefer mini-language and others who prefer JSON.  I think
>>> one thing that might help, is to check how other databases support this
>>> feature or somewhat similar to this feature (mainly with respect to User
>>> Interface), as that can help us in knowing what users are already familiar
>>> with.
>>
>> +1!

Thanks for having a look at that!

> For example, MySQL 5.7 has similar feature, but it doesn't support
> quorum commit, and is simpler than postgresql attempting feature.
> There is one configuration parameter in MySQL 5.7 which indicates the
> number of sync replication node.
> The primary server commit when the primary server receives the
> specified number of ACK from standby server regardless name of standby
> server.

Hm. This is not much helpful in the case we especially mentioned
upthread at some point with 2 data centers, first one has the master
and a sync standby, and second one has a set of standbys. We need to
be sure that the standby in DC1 acknowledges all the time, and we
would only need to wait for one or more of them in DC2. I still
believe that this is the main use case for this feature to ensure a
proper failover without data loss if one data center blows away with a
meteorite.

> And IIRC, Oracle database also doesn't support the quorum commit as well.
> The settings standby server sync or async is specified per standby
> server in configuration parameter in primary node.

And I guess that they manage standby nodes using a system catalog
then, being able to change the state of a node from async to sync with
something at SQL level? Is that right?

> I thought that this feature for postgresql should be simple at first
> implementation.

And extensible.

> It would be good even if there are some restriction such as the
> nesting level, the group setting.
> The another new approach that I came up with is,
> * Add new parameter synchronous_replication_method (say s_r_method)
> which can have two names: 'priority', 'quorum'
> * If s_r_method = 'priority', the value of s_s_names (e.g. 'n1,n2,n3')
> is handled using priority. It's same as '[n1,n2,n3]' in dedicated
> language.
> * If s_r_method = 'quorum', the value of s_s_names is handled using
> quorum commit, It's same as '(n1,n2,n3)' in dedicated language.
> * Setting of synchronous_standby_names is same as today. That is, the
> storing the nesting value is not supported.
> * If we want to support more complex syntax like what we are
> discussing, we can add the new value to s_r_method, for example
> 'complex', 'json'.

If we go that path, I think that we still would need an extra
parameter to control the number of nodes that need to be taken from
the set defined in s_s_names whichever of quorum or priority is used.
Let's not forget that in the current configuration the first node
listed in s_s_names and *connected* to the master will be used to
acknowledge the commit.
-- 
Michael