Configuring synchronous replication

(changed subject again.)

On 17/09/10 10:06, Simon Riggs wrote:
> I don't think we can determine how far to implement without considering
> both approaches in detail. With regard to your points below, I don't
> think any of those points could be committed first.

Yeah, I think we need to decide on the desired feature set first, before
we dig deeper into the the patches. The design and implementation will
fall out of that.

That said, there's a few small things that can be progressed regardless
of the details of synchronous replication. There's the changes to
trigger failover with a signal, and it seems that we'll need some libpq
changes to allow acknowledgments to be sent back to the master
regardless of the rest of the design. We can discuss those in separate
threads in parallel.

So the big question is what the user interface looks like. How does one
configure synchronous replication, and what options are available.
Here's a list of features that have been discussed. We don't necessarily
need all of them in the first phase, but let's avoid painting ourselves
in the corner.

* Support multiple standbys with various synchronization levels.

* What happens if a synchronous standby isn't connected at the moment?
Return immediately vs. wait forever.

* Per-transaction control. Some transactions are important, others are not.

* Quorum commit. Wait until n standbys acknowledge. n=1 and n=all
servers can be seen as important special cases of this.

* async, recv, fsync and replay levels of synchronization.

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.

For per-transaction control, ISTM it would be enough to have a simple
user-settable GUC like synchronous_commit. Let's call it
"synchronous_replication_commit" for now. For non-critical transactions,
you can turn it off. That's very simple for developers to understand and
use. I don't think we need more fine-grained control than that at
transaction level, in all the use cases I can think of you have a stream
of important transactions, mixed with non-important ones like log
messages that you want to finish fast in a best-effort fashion. I'm
actually tempted to tie that to the existing synchronous_commit GUC, the
use case seems exactly the same.

OTOH, if we do want fine-grained per-transaction control, a simple
boolean or even an enum GUC doesn't really cut it. For truly
fine-grained control you want to be able to specify exceptions like
"wait until this is replayed in slave named 'reporting'" or 'don't wait
for acknowledgment from slave named 'uk-server'". With standby
registration, we can invent a syntax for specifying overriding rules in
the transaction. Something like SET replication_exceptions =
'reporting=replay, uk-server=async'.

For the control between async/recv/fsync/replay, I like to think in
terms of
a) asynchronous vs synchronous
b) if it's synchronous, how synchronous is it? recv, fsync or replay?

I think it makes most sense to set sync vs. async in the master, and the
level of synchronicity in the slave. Although I have sympathy for the
argument that it's simpler if you configure it all from the master side
as well.

Putting all of that together. I think Fujii-san's standby.conf is pretty
close. What it needs is the additional GUC for transaction-level control.

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