Failover support for a DB

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)

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.