Re: Sync Rep Design

On 12/31/2010 11:06 AM, Heikki Linnakangas wrote:
> On 31.12.2010 09:50, Hannu Krosing wrote:
>> On 30.12.2010 22:27, Robert Haas wrote:
>>> On Thu, Dec 30, 2010 at 2:04 PM, Simon Riggs<simon@2ndquadrant.com>
>>> wrote:
>>>> synchronous_replication (boolean)
>>>> Specifies whether transaction commit will wait for WAL records
>>>> to be replicated before the command returns a "success"
>>>> indication to the client.
>>> The word "replicated" here could be taken to mean different things,
>>> most obviously:
>>>
>>> - slave has received the WAL
>>> - slave has fsync'd the WAL
>>> - slave has applied the WAL
>> Perhaps the level of "replication guarantee" should be decided on the
>> slave side, by
>> having a configuration parameter there
>>
>> report_as_replicated = received|written_to_disk|fsynced|applied
>>
>> for different types of hosts may have wildly different guarantees and
>> performance
>> parameters for these. One could envision a WAL-archive type "standby"
>> which is
>> there for data persistence only will and never "apply" WAL.
>
> Agreed, it feels natural to specify when a piece of WAL is acknowledged
> in the standby.
>
> Regarding the rest of the proposal, I would still prefer the UI
> discussed here:
>
> http://archives.postgresql.org/message-id/4CAE030A.2060701@enterprisedb.com
>
> It ought to be the same amount of work to implement, and provides the
> same feature set, but makes administration a bit easier by being able to
> name the standbys. Also, I dislike the idea of having the standby
> specify that it's a synchronous standby that the master has to wait for.
> Behavior on the master should be configured on the master.

well that proposal is much closer to what I want as an admin - except 
that it would be nice to configure that through actual DDL.
My wish would be more like:

* standby provides a unique name identifier
* standby has a flag to say the maximum(or minimum?) 
replication_reported support it can do
* standby connects to the master async by default and the master 
registers the standby automatically
* on the master I can do the following with every standby that is 
visible to the master or has been in the past:* enable/disable and add/remove permanently(if not added permanently 
the registration is transient) - enabled if not set explicitly* sync_rep_enabled (boolean) it (so you can still do per
transactionor 
 
per database or whatever sync rep) - disabled if not set explicitly* sync_reply_required (booleant), (per sync standby
flagto require a 
 
reply before the master returns - if there is only one sync standby this 
is default behaviour if there are more the admin can choose)* wait_forever or timeout per standby* maybe a way to set
thereport_as_replicated from the master (if 
 
feasible) up to the max of what the standby can do

so you would get the proposed "semi sync rep" mode by simply setting 
more than one standby as "sync_rep_enabled" and sync_reply_required is 
false for all of them (ie any one of them can reply and the master 
returns) - if you want better than that just require a reply from a 
specific one or more than one.

this would also help in us providing a simple view with a nice and handy 
status report on the slaves (which ones are there, which ones should be 
there, how far are they in terms of applying wal, what status do they have).

Imho an interface like this would be:

a) convinient because it would not require any additional setup 
requirements for async rep except providing a "name" on the standby by 
default
b) it would enable the master to specify the business rules clearly
c) would still support the simple "one sync reply is enough" semisync 
replication case people like to have
d) would also enable the admin to get more than ONE sync standby that is 
really sync - not maybe sync
e) hot_standby_feedback (if enabled) would look at only the permanently 
enabled slaves so only an "DBA approved" standby would be able to affect 
the master for table bloat
f) provide the necessary meta information for providing the handy "quick 
& nice" replication status overview reporting feature people want and need
g) for all the permanently enabled async nodes we could keep track of 
the required oldest required WAL and keep that (optionally) so we could 
get rid of the hard to size max_keep_segements and maintain that 
automatically.

the only disadvantage I can see would be that you would have to manually 
remove a non-functional slave from the master(and only one that you set 
some explicit configuration for!) if you decide you don't need it any more.



Stefan