Re: PostgreSQL Synchronous Replication in production

On Fri, Jun 7, 2013 at 3:22 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
> 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.
>>
>> Action:
>> The Slave is unreachable (postgres is stopped, or machine is turned off)
>>
>> Result:
>> Transactions stay open, and add up until connection is allowed between Master and Slave again.
>>
>> My guess:
>> Create a script that detects when transactions are being opened, but not committed on slave. Alter
>> postgresql.conf to comment out synchronous_standby_names and pg_ctl reload. This should boot the
>> server back to asynchronous, more or less.
>>
>> Caveats:
>> I haven't tested this yet, and I'm not sure what would happen to the transactions that started while
>> synchronous replication was active. Any guesses?
>>
>> 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. My original proposal was
>> asynchronous replication, with xlogs being stored on shared storage, and DRBD replication to prevent
>> it from being a single point of failure. I have never seen it go over 15kbs behind in my setup, which
>> still results in a very speedy failover. Is it just me, or is that seeming better than just
>> synchronous replication?
>>
>> Another caveat I found is that setting up slaves becomes more complicated. You have to set up the
>> Master in asynchronous style, and then switch it to synchronous only when the timing is right.
>> Otherwise the transactions will sit there until everything is ready.
>>
>> Sorry for the onslaught of questions, I don't expect all of them satisfied. Please share any
>> resolutions to these issues which you guys have discovered.
>
> One simple guideline first:
> If you use synchronous replication, you'll have to have at least two standby
> servers or the overall availability of your system will suffer.
>
> The differences between synchronous and asynchronous replication are mostly:
> a) With synchronous replication you cannot lose a committed transaction
> during failover.
> b) Synchronous replication will slow down your system; the higher the
> network latency between the servers, the slower it will get.
>
> You should work on getting the requirements defined:
> - In the case of failover, can you afford to lose a few committed transactions?
> - Are you ready to pay the price for synchronous replication
> (second standby if you don't want availability to suffer, slower
> database system, more complicated setup and failover procedures)?
>
> For synchronous replication, keep the servers close together with
> a strong network inbetween.
> To protect against catastrophes (if that's a requirement), you should
> use another asynchronous standby in a distant location.

yeah -- well put. Synchronous replication exists because in some
cases even the loss of a single transaction is unacceptable. So it's
not really fair to compare vs asynchronous which is more of a 'best
effort' system; it in no way guarantees that every transaction is
recoverable. That small fudge allows for big optimizations in terms
of process simplicity and performance.

merlin