Re: Synchronization levels in SR

On 24/05/10 16:20, Fujii Masao wrote:
> 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 be received by the standby
>     #3 waits for WAL to be received and flushed by the standby
>     #4 waits for WAL to be received, flushed and 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?

I see a lot of value in #4; it makes it possible to distribute read-only 
load to the standby using something like pgbouncer, completely 
transparently to the application. In the lesser modes, the application 
can see slightly stale results.

But whatever we can easily implement, really. Pick one that you think is 
the easiest and start with that, but keep the other modes in mind in the 
design and in the user interface so that you don't paint yourself in the 
corner.

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