Thread: HA, failover and load balancing / howto?

I have some web applications and rich clients that need to
geographically localized copies (for network latency reasons) of a
database (East Coast, Central, West Coast and Japan)  These will be
mostly read however there will be full CRUD activities going on.  I
think this means that there will be a cluster in each region to deal
with load and single failures and when a whole region perhaps dies,
clients will fall back to another region.

ex:
    4 servers for load East Coast
        - db and webservers
    4 servers for load Central USA Coast
        - db and webservers
    4 servers for load West Coast
        - db and webservers

The web applications (Java, tomcat, ejb3, jboss4, php)  If one one, or
more web or db servers die, the others in the region are still used (ie:
just 'degraded')  If all the db servers die in a region, the web server
and applications will hit the db servers in another region)

How can all of this be setup and configured and how can failed db
servers be brought back online and updated to sync into the clusters?

Also looking at the pro/con of doing this in Postgres vs mysql

Are those geographical copies, or geographical subsets? Multi-master
replication is hard with postgres (read: probably not going to happen) but
if you can partition your data up so that you have one writer for a
subset of records, that could work quite well. Especially if you have rich
clients that can afford fast links between your regional servers.

On Sat, 4 Aug 2007, hanasaki wrote:

> I have some web applications and rich clients that need to
> geographically localized copies (for network latency reasons) of a
> database (East Coast, Central, West Coast and Japan)  These will be
> mostly read however there will be full CRUD activities going on.  I
> think this means that there will be a cluster in each region to deal
> with load and single failures and when a whole region perhaps dies,
> clients will fall back to another region.
>
> ex:
>     4 servers for load East Coast
>         - db and webservers
>     4 servers for load Central USA Coast
>         - db and webservers
>     4 servers for load West Coast
>         - db and webservers
>
> The web applications (Java, tomcat, ejb3, jboss4, php)  If one one, or
> more web or db servers die, the others in the region are still used (ie:
> just 'degraded')  If all the db servers die in a region, the web server
> and applications will hit the db servers in another region)
>
> How can all of this be setup and configured and how can failed db
> servers be brought back online and updated to sync into the clusters?
>
> Also looking at the pro/con of doing this in Postgres vs mysql
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org/
>

Single master point subsets wasn't the plan but is doable.  Each
geographic region should have a local read copy.

Ben wrote:
> Are those geographical copies, or geographical subsets? Multi-master
> replication is hard with postgres (read: probably not going to happen)
> but if you can partition your data up so that you have one writer for a
> subset of records, that could work quite well. Especially if you have
> rich clients that can afford fast links between your regional servers.
>
> On Sat, 4 Aug 2007, hanasaki wrote:
>
>> I have some web applications and rich clients that need to
>> geographically localized copies (for network latency reasons) of a
>> database (East Coast, Central, West Coast and Japan)  These will be
>> mostly read however there will be full CRUD activities going on.  I
>> think this means that there will be a cluster in each region to deal
>> with load and single failures and when a whole region perhaps dies,
>> clients will fall back to another region.
>>
>> ex:
>>     4 servers for load East Coast
>>         - db and webservers
>>     4 servers for load Central USA Coast
>>         - db and webservers
>>     4 servers for load West Coast
>>         - db and webservers
>>
>> The web applications (Java, tomcat, ejb3, jboss4, php)  If one one, or
>> more web or db servers die, the others in the region are still used (ie:
>> just 'degraded')  If all the db servers die in a region, the web server
>> and applications will hit the db servers in another region)
>>
>> How can all of this be setup and configured and how can failed db
>> servers be brought back online and updated to sync into the clusters?
>>
>> Also looking at the pro/con of doing this in Postgres vs mysql
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>>               http://archives.postgresql.org/
>>

I will have to look into slony.. not much into db admin yet...
OK.. so
    1. lets stick with complete duplicates in each region
        identical
    2. what opensource/free rdbms are there that can do this better?

Hmm got an answer from Ben but didnt see my post on the list...

Ben wrote:
> You should take it up on the slony list to be sure, but it sure sounds
> like you should be able to use Slony-I for this. Slony hasn't worked for
> my situations yet, but for you, it sounds like you can have seperate
> schemas for each region, and then have the master for each region
> replicate to the other regions. Combine these regional schemas with
> views, and you should have a working system.
>
> On Sun, 5 Aug 2007, hanasaki wrote:
>
>> Single master point subsets wasn't the plan but is doable.  Each
>> geographic region should have a local read copy.
>>
>> Ben wrote:
>>> Are those geographical copies, or geographical subsets? Multi-master
>>> replication is hard with postgres (read: probably not going to happen)
>>> but if you can partition your data up so that you have one writer for a
>>> subset of records, that could work quite well. Especially if you have
>>> rich clients that can afford fast links between your regional servers.
>>>
>>> On Sat, 4 Aug 2007, hanasaki wrote:
>>>
>>>> I have some web applications and rich clients that need to
>>>> geographically localized copies (for network latency reasons) of a
>>>> database (East Coast, Central, West Coast and Japan)  These will be
>>>> mostly read however there will be full CRUD activities going on.  I
>>>> think this means that there will be a cluster in each region to deal
>>>> with load and single failures and when a whole region perhaps dies,
>>>> clients will fall back to another region.
>>>>
>>>> ex:
>>>>     4 servers for load East Coast
>>>>         - db and webservers
>>>>     4 servers for load Central USA Coast
>>>>         - db and webservers
>>>>     4 servers for load West Coast
>>>>         - db and webservers
>>>>
>>>> The web applications (Java, tomcat, ejb3, jboss4, php)  If one one, or
>>>> more web or db servers die, the others in the region are still used
>>>> (ie:
>>>> just 'degraded')  If all the db servers die in a region, the web server
>>>> and applications will hit the db servers in another region)
>>>>
>>>> How can all of this be setup and configured and how can failed db
>>>> servers be brought back online and updated to sync into the clusters?
>>>>
>>>> Also looking at the pro/con of doing this in Postgres vs mysql
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 4: Have you searched our list archives?
>>>>
>>>>               http://archives.postgresql.org/
>>>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>       choose an index scan if your joining column's datatypes do not
>>       match
>>