Re: Synchronous Standalone Master Redoux

On 10.07.2012 17:31, Shaun Thomas wrote:
> On 07/09/2012 05:15 PM, Josh Berkus wrote:
>> So I'm unclear on why sync rep would be faster than async rep given
>> that they use exactly the same mechanism. Explain?
>
> Too many mental gymnastics. I get that async is "faster" than sync, but
> the inconsistent transactional state makes it *look* slower. If a
> customer makes an order, but just happens to check that order state on
> the secondary before it can catch up, that's a net loss. Like I said,
> that's fine for our DR system, or a reporting mirror, or any one of
> several use-case scenarios, but it's not good enough for a failover when
> better alternatives exist. In this case, better alternatives are
> anything that can guarantee transaction durability: DRBD / PG sync.
>
> PG sync mode does what I want in that regard, it just has no graceful
> failure state without relatively invasive intervention.

You are mistaken. PostgreSQL's synchronous replication does not
guarantee that the transaction is immediately replayed in the standby.
It only guarantees that it's been sync'd to disk in the standby, but if
there are open snapshots or the system is simply busy, it might takes
minutes or more until the effects of that transaction become visible.

I agree that such a mode would be highly useful, where a transaction is
not acknowledged to the client as committed until it's been replicated
*and* replayed in the standby. And in that mode, a timeout after which
the master just goes ahead without the standby would be useful. You
could then configure your middleware and/or standby to not use the
standby server for queries after that timeout.

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