Re: Sync Rep Design - Mailing list pgsql-hackers

On Fri, 2010-12-31 at 14:40 +0200, Heikki Linnakangas wrote:
> On 31.12.2010 13:48, Simon Riggs wrote:
> >
> > I see significant real-world issues with configuring replication using
> > multiple named servers, as described in the link above:
> 
> All of these points only apply to specifying *multiple* named servers in 
> the synchronous_standbys='...' list.

Unfortunately, some of the points apply to using named servers ever,
even if there is only one.

>  That's certainly a more complicated 
> scenario, and the configuration is more complicated as a result. 
> With your proposal, it's not possible in the first place.
> 
> Multiple synchronous standbys probably isn't needed by most people, so 
> I'm fine with leaving that out for now, keeping the design the same 
> otherwise. I included it in the proposal because it easily falls out of 
> the design. So, if you're worried about the complexities of multiple 
> synchronous standbys, let's keep the UI exactly the same as what I 
> described in the link above, but only allow one name in the 
> synchronous_standbys setting, instead of a list.

The best usage recommendation is still to have 2+ standbys, *any* of
which can be used to provide sync rep. That is the best performance,
best availability and easiest to configure that I know of. That best
usage is not achievable with uniquely named servers; using non-unique
names defeats the point of having names in the first place.

I accept that the "best usage" is a general case and there may be
circumstances that make the difficulties of named servers worth the
trouble.

So replicating to multiple synchronous standbys is definitely needed in
this release. *Confirming* replication to multiple named sync standbys
is the thing we don't need in this release.

> > I suppose we might regard the feature set I am proposing as being the
> > same as making synchronous_standbys a USERSET parameter, and allowing
> > just two options:
> > "none" - allowing the user to specify async if they wish it
> > "*" - allowing people to specify that syncing to *any* standby is
> > acceptable
> >
> > We can blend the two approaches together, if we wish, by having two
> > parameters (plus server naming)
> >    synchronous_replication = on | off (USERSET)
> >    synchronous_standbys = '...'
> > If synchronous_standbys is not set and synchronous_replication = on then
> > we sync to any standby. If  synchronous_replication = off then we use
> > async replication, whatever synchronous_standbys is set to.
> > If synchronous_standbys is set, then we use sync rep to all listed
> > servers.
> 
> Sounds good.

> I still don't like the synchronous_standbys='' and 
> synchronous_replication=on combination, though. 
> IMHO that still amounts 
> to letting the standby control the behavior on master, and it makes it 
> impossible to temporarily add an asynchronous standby to the mix. I 
> could live with it, you wouldn't be forced to use it that way after all, 
> but I would still prefer to throw an error on that combination. Or at 
> least document the pitfalls and recommend always naming the standbys.

We need a parameter set that makes the best practice easy/easiest to
specify, and yet more complicated configurations possible. So I'm happy
to add "synchronous_standbys" parameter, as long as it is possible to
specify "any" (for which I would use "*"), which would be the default.
Initially that would be restricted to just one name.

Will pass the server name as an option after IDENTIFY SYSTEM <name>.

Anyway, lets continue the discussion next year.

-- Simon Riggs           http://www.2ndQuadrant.com/books/PostgreSQL Development, 24x7 Support, Training and Services