Geographic web+pgsql load balancing

Question

My goal is (don't know if this is possible, that's why I'm asking):

• have two separate geographic locations. At each location, a web server and a postgres database run. The application is the same at both sites. Ideally, I'd like both sites to be usable at the same time, which means the databases should be kept in sync. Is this possible? I've read that pgpool-II may be able to do this, but I'm not sure.

If a site goes down, failover will be manual at this stage, but any suggestion about methods to automate it will be appreciated.

Answer 1

The simplest solution is to use a CDN DNS service - see this SO post for a discussion of the technology. But I suppose a simpler home-grown solution would be to use distinct weighted, round-robin DNS for the two sites and set up some sort of gateway which picks a location, e.g.

east.example.com.  A   10.1.1.1
east.example.com.  A   10.1.1.1
east.example.com.  A   10.1.1.1
east.example.com.  A   10.1.1.1
east.example.com.  A   10.2.2.2

west.example.com.  A   10.1.1.1
west.example.com.  A   10.2.2.2
west.example.com.  A   10.2.2.2
west.example.com.  A   10.2.2.2
west.example.com.  A   10.2.2.2
west.example.com.  A   10.2.2.2

realeast.example.com.   A 10.1.1.1
realwest.example.com.  A 10.2.2.2

www.example.com.   A   10.1.1.1
www.example.com.   A   10.2.2.2

Then on www.example.com vhost serve up a javascript file which measures response times for realeast and realwest then redirects to east or west.example.com (which could also be triggered from east.example.com or west.example.com before a session is created.

(NB this only addresses the stack as a whole - allowing a webserver at east to failover to a database at west is a different can of worms)

Answer 2

Bucardo is a multimaster replication system for PostgreSQL that could help you.