Re: Sync Rep Design

On 1/2/11 12:35 AM, Heikki Linnakangas wrote:
> 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.

That's not "very likely". Shops with enough DBAs to actually have a
heirarchy of database access are rare indeed; the average business has
trouble employing *one* fulltime DBA. Also, you're mistaken if you
think that DBA's wouldn't restrict the ability of development users to
set up asynch connections; those have a significant impact on the
production server as well.

However, the possibility of *accidental* DOS is a bit more likely, per
accidentally using the wrong config file. I don't think you can guard
against that with permissions, though; if a DBA is copying the config
file from a synchronous server accidentally, presumably he's copying the
.pgpass file as well, and then the new server is identical to the legit
synch rep servers as far as the master is concerned.

So, again, I don't agree that a separate synchrep permission is useful,
or warranted.

However, your arguments *do* make me backpedal on the issue of having a
list of synch rep roles on the master. I can easily imagine a DBA
needing to rapidly disable synch rep if replication is failing, without
taking the time to log in to several separate standbys, one or more of
which might be improperly configured and inaccessible. I can't think of
a simpler way to do that than having a synch_rep_roles configuration on
the master. That would also handle control issues for the senior DBA,
since you'd need superuser access to the master to modify it.

--Josh Berkus