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? – Craig Ringer Jan 29 '14 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. – a_horse_with_no_name Jan 29 '14 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"? – Ivan Cagalj Jan 30 '14 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 :) – Ivan Cagalj Jan 30 '14 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 – Craig Ringer Jan 30 '14 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.