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