Re: Sync Rep Design

On 1/1/11 5:59 AM, Stefan Kaltenbrunner wrote:
> well you keep saying that but to be honest I cannot really even see a
> usecase for me - what is "only a random one of a set of servers is sync
> at any time and I don't really know which one".
> My usecases would al involved 2 sync standbys and 1 or more async ones.
> but the second sync one would be in a different datacenter and I NEED to
> protect against a datacenter failure which your proposals says I cannot
> do :(

As far as I know, *nobody* has written the bookkeeping code to actually
track which standbys have ack'd. We need to get single-ack synch
standby merged, tested and working before we add anything as complicated
as "each standby on this list must ack". That means that it's extremely
unlikely for 9.1 at this point.

Frankly, if Simon hadn't already submitted code, I'd be pushing for
single-standby-only for 9.1, instead of "any one".

Standby in general deals with the A,D,R triangle (Availability,
Durability, Response time). "Any one" configuration is the A,R
configuration, and the only reason to go out with it for 9.1 is because
it's simpler to implement than the D,R configuration (all standbys must
ack).

> Hmm, access control... We haven't yet discussed what privileges a
> standby needs to become synchronous. Perhaps it needs to be a separate
> privilege that can be granted, in addition to the replication privilege?

No, I don't think so. An additional priv would just complicate life for
DBAs without providing any real benefit. You'd be guarding against the
very narrow hypothetical case where there's a server admin with limited
privs on the master, and authorization to create async standbies, but
not the authorization to create s synch standby. How likely is that to
*ever* happen?

> Robert's suggestion of using the roles instead of server names would
> also solve that. With that you would list the roles in
> synchronous_standbys, and no-one else could become a synchronous
> standby. The downside is that if you want to have two standbys in the
> mode that it's enough that either one acknowledges a commit, they would
> have to use the same user account.

I really don't think that Robert was suggesting that we have
predetermined Roles with "magic names" like synchronous_standbys (were
you, Robert?). That would defeat eventually having the feature which
people like Stefan want: the ability to define pools of servers with
custom names to represent various data centers. It also overloads and
"perverts" the concept of Roles.

While I quite like the idea of having Roles for replication, synch/async
should be a property (CREATE ROLE seattle_dac NOLOGIN REPLICATION
SYNCHRONOUS*), not a special role. Also, I'll repeat: I see this as 9.2
work, not 9.1 work. There's going to be far too much bookkeeping code
to write to make it happen without severely delaying 9.1.

BTW, I don't see *any* of this requiring us to have a
synchronous_standbys = "list" GUC if we embrace the Roles concept.

--Josh

( here's how I can see Roles working:

1) Create a group role for the synch standbyes (NOLOGIN REPLICATION)
2) Create one or more replication roles which are members of that group
role.
3) File-synch those standbys and get them replicating (asynchronously)
4) ALTER ROLE synch_standbys SYNCHRONOUS
5) The postmaster scans the list of Roles for synchronous roles. For
each synchronous role, the must be one replication ack in order commit.

Since the above means that it would be possible to have a replication
connection which was a member of more than one synch group, you can see
that the bookkeeping involved will be substantial. So, 9.2.
)