Re: PostgreSQL Synchronous Replication in production

On 06/06/13 11:20, Colin Sloss wrote:
>
> I have been testing the differences between asynchronous and synchronous
> hot standby streaming replication on PostgreSQL 9.2.4. There is some
> push towards synchronous replication, but I am finding some serious
> problems, and wonder how other people deal with them.
[snip]
> The whole idea of my solution was to have no single point of failure.
> This seems to create two exclusive points of failure, each needing a
> completely separate reaction.

Synchronous replication provides a higher level of guarantee for an
individual transaction (it's safely[1] on at least two boxes now) at the
cost of making the system as a whole more brittle.

Your uptime as a "service" will inevitably be reduced since in the event
of problems talking to the slave the master will *have* to delay/cancel
new transactions.

I have seen people suggest some sort of mode where the server drops back
to asynch mode in the event of problems. I can't quite understand the
use-case for that though - either you want synchronous replication or
you don't. Mostly-synchronous is just asynchronous.

Here's a few questions. How you answer them will decide whether you
really want synchronous replication or not:
1. The link between servers encounters network congestion
   a. The whole system should slow down.
      Committed transactions should ALWAYS be on
      two geographically separate machines.
   b. An alert should be sent.
      If it's not sorted in 5 mins we'll get someone to look at it.
2. Adding more servers[2] to my replication should:
   a. Make the system as a whole slower[3] and reduce uptime
      but increase the safety of committed transactions
   b. Make the system as a whole faster and increase uptime

There are cases where you want (a), but lots where you want (b) and
monitor the replication lag.


[1] For various values of "safely" of course
[2] In the same mode - adding async slaves doesn't count
[3] Assuming a reasonable write load of course. Read-only databases
won't care.

--
   Richard Huxton
   Archonet Ltd