Failover support for a DB

Question

We are currently evaluating failover support in different databases.
We were earlier using HSQLDB but it seems that it does not have clustering/replication support.

Our requirement is simply to have two database servers, one being only for synchronous backup but if the primary server is down, then the secondary should automatically start acting as the primary server.

Has anyone evaluated MySQL, PostgreSQL or any other DB server for such a use case?

Edit: We had thought of using MySQL cluster but it now seems that it is under GPL license which we won't be able to work with. Could anyone please suggest a synchronous replication/clustering solution which can be used? We are currently using HSQL, so a solution with HSQL used in clustered mode will be ideal for us but we are open for change.

Answer 1

Not sure this is within the desired price range of most FOSS-type people :-) but we use DB2 9.7 for exactly this purpose (actually, we mostly use DB2/z on the mainframe for it, but some customers like the DB2/LUW (Linux/UNIX/Windows) option for smaller systems).

DB2 comes with high availability (HA) features built in and you can use db2haicu, the DB2 High Availability Instance Configuration Utility (gotta love those acronym generators employed by Big Blue) to configure things relatively painlessly.

It's active/passive as you desired, although DB2 is certainly capable of active/active setups for load balancing.

The particular setups we're most familiar with at the low end (everything other than a mainframe) are actually shared disk ones, with the HA applying to only DBMS resources and not data, but you can separate the data with DB2 replication features as well.

We've had one client (at least) using Q replication, which is a very low latency replication method, close to synchronous but not quite. DB2 does actually provide real synchronous replication as well.

DeveloperWorks has an interesting article on how this all hangs together, along with the various options.

Answer 2

Just for completeness, the H2 database has some clustering support, but compared to the MySQL and PostgreSQL features it is very limited, it's really only failover. I would first look at HA-JDBC.

Answer 3

for a simple failover where servers are on the same location. you can use DRBD and Heartbeat.

In a nutshell: DRBD stores the data on 2 servers on the same time. fully transparent to the system. with heartbeat the standby checks against the main server, if its not reachable, it takes over the resource, mounts it and starts the database daemon. (works with mysql, postgres and most probably with most other daemons out there)

Answer 4

There is a third-party product that works with HSQLDB:

http://ha-jdbc.sourceforge.net/

Answer 5

Stackoverflow resources
MySQL supports replication out of the box: see this question for MySQL: Scaling solutions for MySQL (Replication, Clustering)

PostgreSQL also support replication, see this question for that: PostgreSQL replication strategies

If your requirements are simple MySQL will work
I've used MySQL is a simple master-master failover scenario using the setup I read in High Performance MySQL. I highly recommend the book if you're keen on using MySQL.

It has worked well for me, because I just wanted a simple fail-over.
If your use case is just as simple. It will work well.