Re: Sync Rep Design - Mailing list pgsql-hackers

On 02.01.2011 00:40, Josh Berkus wrote:
> 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.

The bookkeeping will presumably consist of an XLogRecPtr in shared 
memory for each standby, tracking how far the standby has acknowledged. 
At commit, you scan the standby slots in shared memory and check that 
the required standbys have acknowledged your commit record. The 
bookkeeping required is the same whether or not we support a list of 
standbys that must ack or just one.

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

Yes, we are awfully late, but let's not panic.

BTW, there's a bunch of replication related stuff that we should work to 
close, that are IMHO more important than synchronous replication. Like 
making the standby follow timeline changes, to make failovers smoother, 
and the facility to stream a base-backup over the wire. I wish someone 
worked on those...

>> 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?

Very likely. A synchronous standby can bring the master to a halt, while 
an asynchronous one is rather harmless. If I were a DBA, and the data 
wasn't very sensitive, I would liberally hand out async privileges to my 
colleagues to set up reporting standbys, test servers etc. But I would 
*not* give them synchronous privileges, because sooner or later one 
would go "hmm, I wonder what happens if I make this synchronous", or 
haphazardly copy the config file from a synchronous standby. That would 
either bring down the master, or act as a "fake" standby, acknowledging 
commits before they're flushed to the real synchronous standby. Either 
one would be bad.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com