up vote 1 down vote favorite
2
share [g+] share [fb]

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.

link|improve this question
feedback

3 Answers

up vote 0 down vote

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)

link|improve this answer
Thanks. It's not the "how to send which users to which location" part I have doubts on, but rather the database part. Assuming I'm able to successfully distribute users to both sites (via DNS or otherwise), how do I keep the database consistent? Users will be querying or updating the DB from both locations. – user56857 Oct 20 '10 at 14:20
If it were mysql I'd suggest master-master replication. If it were a free choice of database then I'd go with a non-relational system such as cassandra or mongodb. But for Postgresql....no idea. – symcbean Oct 20 '10 at 16:29
feedback
up vote 0 down vote

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

If you want the failover to be fully automated based on server probe results and you want geographical distribution to the nearest healthy server you can just get specialty dns services from:

http://edgedirector.com

As for the database sync problem, MSSQL can handle it using one of three replication techniques. Presumably pgsql has something similar. Sorry to be vague, but better to be vague than lead you down the garden path.

link|improve this answer
Eh, it seems that pgsql has no native master-master replication, that's why I asked in the first place...there are a number of addons or modules that seem to look as if they can do it, but I have no experience and so was asking for some advice if somebody had used them already. – user56857 Oct 21 '10 at 8:12
Here is what I came up with after reading the docs (still unsure though): set up a pgpool instance at each site; this instance will have the local postgres and the remote pgpool as backends, with reads only performed from the local postgres, and writes going to the local postgres and replicated to the remote pgpool. Would this work at all? I can't see why not, but then I'm not a databse expert. – user56857 Oct 22 '10 at 10:51
feedback
up vote 0 down vote

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

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard