Synchronization levels in SR - Mailing list pgsql-hackers

Hi,

I'm now designing the "synchronous" replication feature based on
SR for 9.1, while discussing that at another thread.
http://archives.postgresql.org/pgsql-hackers/2010-04/msg01516.php

At the first design phase, I'd like to clarify which synch levels
should be supported 9.1 and how it should be specified by users.

The log-shipping replication has some synch levels as follows.
  The transaction commit on the master  #1 doesn't wait for replication (already suppored in 9.0)  #2 waits for WAL to
bereceived by the standby  #3 waits for WAL to be received and flushed by the standby  #4 waits for WAL to be received,
flushedand replayed by     the standby  ..etc?
 

Which should we include in 9.1? I'd like to add #2 and #3.
They are enough for high-availability use case (i.e., to
prevent failover from losing any transactions committed).
AFAIR, MySQL semi-synchronous replication supports #2 level.

#4 is useful for some cases, but might often make the
transaction commit on the master get stuck since read-only
query can easily block recovery by the lock conflict. So
#4 seems not to be worth working on until that HS problem
has been addressed. Thought?

Second, we need to discuss about how to specify the synch
level. There are three approaches:

* Per standby Since the purpose, location and H/W resource often differ from one standby to another, specifying level
perstandby (i.e., we set the level in recovery.conf) is a straightforward approach, I think. For example, we can choose
#3for high-availability standby near the master, and choose #1 (async) for the disaster recovery standby remote.
 

* Per transaction Define the PGC_USERSET option specifying the level and specify it on the master in response to the
purposeof transaction. In this approach, for example, we can choose #4 for the transaction which should be visible on
thestandby as soon as a "success" of the commit has been returned to a client. We can also choose #1 for time-critical
butnot mission-critical transaction.
 

* Mix Allow users to specify the level per standby and transaction at the same time, and then calculate the real level
fromthem by using some algorithm.
 

Which should we adopt for 9.1? I'd like to implement the
"per-standby" approach at first since it's simple and seems
to cover more use cases. Thought?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center