Re: Configuring synchronous replication

Robert Haas <robertmhaas@gmail.com> writes:
>> So, here, we have two quite different things to be concerned
>> about. First is the configuration, and I say that managing a distributed
>> setup will be easier for the DBA.
>
> Yeah, I disagree with that, but I suppose it's a question of opinion.

I'd be willing to share your thoughts if it was only for the initial
setup. This one is hard enough to sketch on the paper that you prefer an
easy way to implement it afterwards, and in some cases a central setup
would be just that.

The problem is that I'm concerned with upgrading the setup once the
system is live. Not at the best time for that in the project, either,
but when you finally get the budget to expand the number of servers.

From experience with skytools, no manual registering works best. But…

> I think that without standby registration it will be tricky to display
> information like "the last time that standby foo was connected".
> Yeah, you could set a standby name on the standby server and just have
> the master remember details for every standby name it's ever seen, but
> then how do you prune the list?

… I now realize there are 2 parts under the registration bit. What I
don't see helping is manual registration. For some use cases you're
talking about maintaining a list of known servers sounds important, and
that's also what londiste is doing.

Pruning the list would be done with some admin function. You need one to
see the current state already, add some other one to unregister a known
standby.

In londiste, that's how it works, and events are kept in the queues for
all known subscribers. For the ones that won't ever connect again,
that's of course a problem, so you SELECT pgq.unregister_consumer(…);.

> Heikki mentioned another application for having a list of the current
> standbys only (rather than "every standby that has ever existed")
> upthread: you can compute the exact amount of WAL you need to keep
> around.

Well, either way, the system can not decide on its own whether a
currently not available standby is going to join the party again later
on.

>> Now it seems to me that all you need here is the master sending one more
>> information with each WAL "segment", the currently fsync'ed position,
>> which pre-9.1 is implied as being the current LSN from the stream,
>> right?
>
> I don't see how that would help you.

I think you want to refrain to apply any WAL segment you receive at the
standby and instead only advance as far as the master is known to have
reached. And you want this information to be safe against slave restart,
too: don't replay any WAL you have in pg_xlog or in the archive.

The other part of your proposal is another story (having slaves talk to
each-other at master crash).

> Well, if you need to talk to "all the other standbys" and see who has
> the furtherst-advanced xlog pointer, it seems like you have to have a
> list somewhere of who they all are.

Ah sorry I was thinking on the other part of the proposal only (sending
WAL segments that are not been fsync'ed yet on the master). So, yes.

But I thought you were saying that replicating a (shared?) catalog of
standbys is technically hard (or impossible), so how would you go about
it? As it's all about making things simpler for the users, you're not
saying that they should keep the main setup in sync manually on all the
standbys servers, right?

> Maybe there's some way to get
> this to work without standby registration, but I don't really
> understand the resistance to the idea

In fact I'm now realising what I don't like is having to manually do the
registration work: as I already have to setup the slaves, it only
appears like a useless burden on me, giving information the system
already has.

Automatic registration I'm fine with, I now realize.

Regards,
--
Dimitri Fontaine
PostgreSQL DBA, Architecte