Re: standby registration (was: is sync rep stalled?)

On Thu, Oct 7, 2010 at 1:39 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On 10/7/10, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> On 06.10.2010 19:26, Greg Smith wrote:
>>> Now, the more relevant question, what I actually need in order for a
>>> Sync Rep feature in 9.1 to be useful to the people who want it most I
>>> talk to. That would be a simple to configure setup where I list a subset
>>> of "important" nodes, and the appropriate acknowledgement level I want
>>> to hear from one of them. And when one of those nodes gives that
>>> acknowledgement, commit on the master happens too. That's it. For use
>>> cases like the commonly discussed "two local/two remote" situation, the
>>> two remote ones would be listed as the important ones.
>>
>> This feels like the best way forward to me. It gives some flexibility,
>> and doesn't need a new config file.
>>
>> Let me check that I got this right, and add some details to make it more
>> concrete: Each standby is given a name. It can be something like
>> "boston1" or "testserver". It does *not* have to be unique across all
>> standby servers. In the master, you have a list of important,
>> synchronous, nodes that must acknowledge each commit before it is
>> acknowledged to the client.
>>
>> The standby name is a GUC in the standby's configuration file:
>>
>> standby_name='bostonserver'
>>
>> The list of important nodes is also a GUC, in the master's configuration
>> file:
>>
>> synchronous_standbys='bostonserver, oxfordserver'
>>
>> To configure for a simple setup with a master and one synchronous
>> standby (which is not a very good setup from availability point of view,
>> as discussed to death), you give the standby a name, and put the same
>> name in synchronous_standbys in the master.
>>
>> To configure a setup with a master and two standbys, so that a commit is
>> acknowledged to client as soon as either one of the standbys acknowledge
>> it, you give both standbys the same name, and the same name in
>> synchronous_standbys list in the master. This is the configuration that
>> gives zero data loss in case one server fails, but also caters for
>> availability because you don't need to halt the master if one standby fails.
>>
>> To configure a setup with a master and two standbys, so that a commit is
>> acknowledged to client after *both* standbys acknowledge it, you give
>> both standbys a different name, and list both names in
>> synchronous_standbys_list in the master.
>>
>> I believe this will bend to most real life scenarios people have.
>
> +1. I think this would have met any needs of mine in my past life as a
> sysadmin/dba.

Before we get too far down the garden path here, this is actually
substantially more complicated than what Greg proposed. Greg was
proposing, as have some other folks I think, to focus only on the k=1
case - in other words, only one acknowledgment would ever be required
for any given commit. I think he's right to focus on that case,
because the multiple-ACKs-required solutions are quite a bit hairier.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company