I'm trying to set up PostgreSQL 9.3 server in HA mode on 2 Windows servers. Final solution should be deployed to Microsoft azure network, so there are few limitations. Since Microsoft is doing maintenance on their machines without any warning, I find it difficult to setup such system. If you have any idea how to do it, I'll be glad to hear it.

I already tried to setup Windows Cluster with shared data disk (ISCSI), as suggested on several sites. I found out that, due to heavy NFS caching, failover works only if there are no transactions for few minutes. If there are transactions, after several failovers database became corrupted and service failed to start on both machines.

I can always try to setup this on linux machines using pgpool, but I prefer if this could be done on windows because application is written for IIS. I've done this with Microsoft SQL server on 3 machines without much difficulties, but I couldn't find similar solution for PostgreSQL.

Thanks in advance! Kind regards, Ivan

  • 2
    Shared disk is absolutely the wrong approach for PostgreSQL. You need to do it using replication, not shared storage. What happens if the storage goes down? No more HA! At this point most "serious" PostgreSQL users land up running on Linux, and there's been little constructive input and contribution from the Windows-using PostgreSQL community to help port & maintain tools like PgBouncer and PgPool for Windows. So lots of people who might use Windows then use Linux because that's where the stuff they need runs. Cycle. You willing to step up and help out with Windows ports & testing? Commented Jan 29, 2014 at 11:23
  • You can use streaming replication without problems on Windows. You just need a way to detect the outage of the primary and then switch over to the slave. To my knowledge pgBouncer runs on Windows (see here: winpg.jp/~saito/pgbouncer) and can help automate the switchover.
    – user1822
    Commented Jan 29, 2014 at 11:30
  • 1
    Thank you for your answers. I've set up pgBouncer and today I will try to do Synchronous Replication. I have one question about that. Master db goes offline, I promote slave to be master, and then master returns online again. How do I handle that situation? Should I leave new master alone and old master denote to slave (MSSQL does that)? Will replication continue to work "other way"? Commented Jan 30, 2014 at 7:24
  • @CraigRinger I did the same with MSSQL Enterprise edition and, in general, solution worked. So, I saw no reason why I shouldn't try the same with PostgreSQL. Like I said, I red some posts with that suggestion, and there was at least one like that on PostgreSQL support. Of course I would make sure that storage stays available. How could I plan HA without that? I come from Microsoft world, but I'm not such a noob :) Commented Jan 30, 2014 at 7:58
  • @IvanCagalj You'll need to re-seed the old master from the new, promoted one. There's work ongoing to make that faster, allowing you to more easily turn a failed master into a replica slave, but right now you need to start from a new pg_basebackup. The tool repmgr helps automate some of this, but does not support Windows. Replication, HA and failover in PostgreSQL is a bit of a "here are the pieces, now assemble your own" affair, somewhere between flat-pack furniture and just going to a hardware store :S Commented Jan 30, 2014 at 8:40


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.