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
