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.
Yours,
Laurenz Albe
