Re: Configuring synchronous replication - Mailing list pgsql-hackers

On Fri, 2010-09-17 at 13:41 +0300, Heikki Linnakangas wrote:
> On 17/09/10 12:49, Simon Riggs wrote:
> > This isn't just about UI, there are significant and important
> > differences between the proposals in terms of the capability and control
> > they offer.
> 
> Sure. The point of focusing on the UI is that the UI demonstrates what 
> capability and control a proposal offers.

My patch does not include server registration. It could be added later
on top of my patch without any issues.

The core parts of my patch are the fine grained transaction-level
control and the ability to mix them dynamically with good performance.

To me server registration is not a core issue. I'm not actively against
it, I just don't see the need for it at all. Certainly not committed
first, especially since its not actually needed by either of our
patches.

Standby registration doesn't provide *any* parameter that can't be
supplied from standby recovery.conf. 

The only thing standby registration allows you to do is know whether
there was supposed to be a standby there, but yet it isn't there now. I
don't see that point as being important because it seems strange to me
to want to wait for a standby that ought to be there, but isn't anymore.
What happens if it never comes back? Manual intervention required.

(We agree on how to handle a standby that *is* "connected", yet never
returns a reply or takes too long to do so).

> >> So what should the user interface be like? Given the 1st and 2nd
> >> requirement, we need standby registration. If some standbys are
> >> important and others are not, the master needs to distinguish between
> >> them to be able to determine that a transaction is safely delivered to
> >> the important standbys.
> >
> > My patch provides those two requirements without standby registration,
> > so we very clearly don't "need" standby registration.
> 
> It's still not clear to me how you would configure things like "wait for 
> ack from reporting slave, but not other slaves" or "wait until replayed 
> in the server on the west coast" in your proposal. Maybe it's possible, 
> but doesn't seem very intuitive, requiring careful configuration in both 
> the master and the slaves.

In the use cases we discussed we had simple 2 or 3 server configs.

master
standby1 - preferred sync target - set to recv, fsync or apply
standby2 - non-preferred sync target, maybe test server - set to async

So in the two cases you mention we might set

"wait for ack from reporting slave"
master: sync_replication = 'recv'   #as default, can be changed
reporting-slave: sync_replication_service = 'recv' #gives max level

"wait until replayed in the server on the west coast"
master: sync_replication = 'recv'   #as default, can be changed
west-coast: sync_replication_service = 'apply' #gives max level


The absence of registration in my patch makes some things easier and
some things harder. For example, you can add a new standby without
editing the config on the master.

If you had 2 standbys, both offering the same level of protection, my
proposal would *not* allow you to specify that you preferred one master
over another. But we could add a priority parameter as well if that's an
issue. 

> In your proposal, you also need to be careful not to connect e.g a test 
> slave with "synchronous_replication_service = apply" to the master, or 
> it will possible shadow a real production slave, acknowledging 
> transactions that are not yet received by the real slave. It's certainly 
> possible to screw up with standby registration too, but you have more 
> direct control of the master behavior in the master, instead of 
> distributing it across all slaves.
> 
> > The question is do we want standby registration on master and if so,
> > why?
> 
> Well, aside from how to configure synchronous replication, standby 
> registration would help with retaining the right amount of WAL in the 
> master. wal_keep_segments doesn't guarantee that enough is retained, and 
> OTOH when all standbys are connected you retain much more than might be 
> required.
> 
> Giving names to slaves also allows you to view their status in the 
> master in a more intuitive format. Something like:

We can give servers a name without registration. It actually makes more
sense to set the name in the standby and it can be passed through from
standby when we connect.

I very much like the idea of server names and think this next SRF looks
really cool.

> postgres=# SELECT * FROM pg_slave_status ;
>      name    | connected |  received  |   fsyncd   |  applied
> ------------+-----------+------------+------------+------------
>   reporting  | t         | 0/26000020 | 0/26000020 | 0/25550020
>   ha-standby | t         | 0/26000020 | 0/26000020 | 0/26000020
>   testserver | f         |            | 0/15000020 |
> (3 rows)

That could be added on top of my patch also.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services