Hello,
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.
Regards,
Colin
