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

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.

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.