3

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.

Has QUIT--Anony-Mousse
  • 76,138
  • 12
  • 138
  • 194
Ashish
  • 3,028
  • 5
  • 28
  • 35
  • What's the actual question? If someone used MySQL / Postgres or other db in a failover system? My guess is that many have, but what are the specifics you are interested about? – Michael J.V. Jun 10 '11 at 10:17
  • Yes, I am just evaluating building a failover system. The only requirement is that the cluster can continue working when the primary node goes down. The secondary node should automatically start serving the incoming request. And most importantly, the secondary should be updated synchronously so that there is no lag between the primary and secondary data. – Ashish Jun 10 '11 at 10:59
  • Belongs in the [DBA SE](http://dba.stackexchange.com/). – Denis de Bernardy Jun 10 '11 at 11:51

5 Answers5

6

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.

Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319
  • Thanks a lot. I have a few questions:does your system have synchronized data replication? does your secondary server automatically comes into action whenever the primary goes down? and from the application which accesses your db, do you have to configure just one server and the clustering takes care of the db servers going down? – Ashish Jun 10 '11 at 11:14
  • yes, yes and yes. But read the book I linked to if you just follow the steps there, all will work out. – Johan Jun 10 '11 at 11:23
  • 1
    master-master replication is the root of many evil things. where is the failover in db connections handelt ? in the application itself ? in an heartbeat failover env. the slave gets the ip of the master so its application transparent. – Rufinus Jun 10 '11 at 14:16
  • i found several replication topologies in the booke mentioned by you but none of them is synchronous. All of them relies on row/statement based mysql replication which is inherently asynchronous. Are you using mysql clustering instead of mysql replication? – Ashish Jun 11 '11 at 21:35
  • I could not find the replication you were talking about, I had to settle for MySQL cluster. – Ashish Jun 21 '11 at 10:01
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.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • we only require failover, and it seems H2 is free to use for a commercial product too (something where MySQL cluster failed us :-(). Although, I have not fully evaluated H2, I feel that it may just work for me and so I am awarding the bounty to you. Please check back and try to answer any queries which I may have in next couple of days regarding H2. Thanks. – Ashish Sep 04 '11 at 17:26
1

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)

Rufinus
  • 29,200
  • 6
  • 68
  • 84
  • thanks for the response. From the DRBD documentation, it seems that DRBD relies on disk changes to sync changes across DBs. Whereas, in our case the data is expected to be in memory mostly. One of our prime requirements is synchronous data replication, which means that the backup server data should get immediately updated when there are any changes in the data of primary server. – Ashish Jun 10 '11 at 10:25
  • Leaving aside Ashish's comment above ewhich seems a bit confused. DRBD and Heartbeat provide tools for building clusters - they are not a clustering solution. Particularly, they have no visibility awareness of the record locking required for a DBMS, although that does not explicitly prevent having a passive node acting as a data sink - but that still leaves the problem of automating failover and of resolving slpit-brain. Not a good answer. – symcbean Jun 10 '11 at 12:02
  • a simple failover.... read the words dude. the database stores to file when a transaction is complete, so is it copied to the second node. no transaction problems and split brains occour under different cercumstances.... have such setups running for years, and with heartbeat v2 it even more simpler to move resources around among nodes. – Rufinus Jun 10 '11 at 14:15
1

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

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

fredt
  • 24,044
  • 3
  • 40
  • 61
  • Yes. In Hibernate or any other ORM, just use the correct URL form. – fredt Aug 30 '11 at 09:00
  • this does not seem to be a project under active development, would you consider this for a critical production deployment – Ashish Aug 30 '11 at 11:15
  • It is still being developed and supported (the Develop page shows recent SVN updates). There isn't much to add to this type of software once it works and the reported bugs have been fixed. Apart from this, only you can judge on deployment issues, as there are many variables. – fredt Aug 30 '11 at 11:39
  • i wasted one day on trying to make it work but realized that it uses jgroups version 2.6.x which is quite old and another library used by us is dependent on version 2.12.x, I think that is the problem in working with projects which are not under active development :-( The last realease was 2 years back, how can someone use such a project for a major deployment? – Ashish Sep 01 '11 at 13:22
  • You are right about the jar dependency issue. But otherwise, a high level of quality may be achieved after several bugfix releases when no major new bugs are reported. – fredt Sep 01 '11 at 15:04
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.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953