Re: Support for N synchronous standby servers - take 2

On Mon, Feb 1, 2016 at 5:36 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> On Sun, Jan 31, 2016 at 8:58 PM, Michael Paquier
> <michael(dot)paquier(at)gmail(dot)com> wrote:
>> On Sun, Jan 31, 2016 at 5:28 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>>> On Sun, Jan 31, 2016 at 5:18 PM, Michael Paquier
>>> <michael(dot)paquier(at)gmail(dot)com> wrote:
>>>> On Sun, Jan 31, 2016 at 5:08 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>>>>> On Sun, Jan 31, 2016 at 1:17 PM, Michael Paquier
>>>>> <michael(dot)paquier(at)gmail(dot)com> wrote:
>>>>>> On Thu, Jan 28, 2016 at 10:10 PM, Masahiko Sawada wrote:
>>>>>>> By the discussions so far, I'm planning to have several replication
>>>>>>> methods such as 'quorum', 'complex' in the feature, and the each
>>>>>>> replication method specifies the syntax of s_s_names.
>>>>>>> It means that s_s_names could have the number of sync standbys like
>>>>>>> what current patch does.
>>>>>>
>>>>>> What if the application_name of a standby node has the format of an integer?
>>>>>
>>>>> Even if the standby has an integer as application_name, we can set
>>>>> s_s_names like '2,1,2,3'.
>>>>> The leading '2' is always handled as the number of sync standbys when
>>>>> s_r_method = 'priority'.
>>>>
>>>> Hm. I agree with Fujii-san here, having the number of sync standbys
>>>> defined in a parameter that should have a list of names is a bit
>>>> confusing. I'd rather have a separate GUC, which brings us back to one
>>>> of the first patches that I came up with, and a couple of people,
>>>> including Josh were not happy with that because this did not support
>>>> real quorum. Perhaps the final answer would be really to get a set of
>>>> hooks, and a contrib module making use of that.
>>>
>>> Yeah, I agree with having set of hooks, and postgres core has simple
>>> multi sync replication mechanism like you suggested at first version.
>>
>> If there are hooks, I don't think that we should really bother about
>> having in core anything more complicated than what we have now. The
>> trick will be to come up with a hook design modular enough to support
>> the kind of configurations mentioned on this thread. Roughly perhaps a
>> refactoring of the syncrep code so as it is possible to wait for
>> multiple targets some of them being optional,, one modular way in
>> pg_stat_get_wal_senders to represent the status of a node to user, and
>> another hook to return to decide which are the nodes to wait for. Some
>> of the nodes being waited for may be based on conditions for quorum
>> support. That's a hard problem to do that in a flexible enough way.
>
> Hm, I think not-nested quorum and priority are not complicated, and we
> should support at least both or either simple method in core of
> postgres.
> More complicated method like using json-style, or dedicated language
> would be supported by external module.

So what about the following plan?

[first version]
Add only synchronous_standby_num which specifies the number of standbys
that the master must wait for before marking sync replication as completed.
This version supports simple use cases like "I want to have two synchronous
standbys".

[second version]
Add synchronous_replication_method: 'prioriry' and 'quorum'. This version
additionally supports simple quorum commit case like "I want to ensure
that WAL is replicated synchronously to at least two standbys from five
ones listed in s_s_names".

Or

Add something like quorum_replication_num and quorum_standby_names, i.e.,
the master must wait for at least q_r_num standbys from ones listed in
q_s_names before marking sync replication as completed. Also the master
must wait for sync replication according to s_s_num and s_s_num.
That is, this approach separates 'priority' and 'quorum' to each parameters.
This increases the number of GUC parameters, but ISTM less confusing, and
it supports a bit complicated case like "there is one local standby and three
remote standbys, then I want to ensure that WAL is replicated synchronously
to the local standby and at least two remote one", e.g.,

s_s_num = 1, s_s_names = 'local'
q_s_num = 2, q_s_names = 'remote1, remote2, remote3'

[third version]
Add the hooks for more complicated sync replication cases.

I'm thinking that the realistic target for 9.6 might be the first one.

Regards,

--
Fujii Masao