Comparing MySQL and Postgres 9.0 Replication (TheServerSide)

[Posted November 18, 2010 by corbet]

TheServerSide.com has a comparison of the replication features offered by MySQL and PostgreSQL. "As demonstrated above, there are both feature and functional differences between how MySQL and PostgreSQL implement replication. However, for many general application use cases, either MySQL or PostgreSQL replication will serve just fine; technically speaking, from a functional and performance perspective, it won't matter which solution is chosen. That said, there still are some considerations to keep in mind in deciding between the different offerings."

DRBD

Posted Nov 19, 2010 11:36 UTC (Fri) by Richard_J_Neill (subscriber, #23093) [Link] (7 responses)

We actually set this up in a different way, using DRBD (Distributed Replicated Block Device) and protocol B.

We have two servers, located side-by-side, and a dedicated gigabit-ethernet link between them. Each server contains a RAID-1 (mirrored) array of X-25E SSDs. Using SSDs, we can disable the write-cache, and still get decent performance. We then form a DRBD array of these pairs. This is where we mount /var/lib/pgsql.

The result is extremely fast, and very robust. A transaction for Postgresql will commit when:
- it has been flushed to physical disk on the primary.
- it has reached the memory of the secondary.

DRBD

Posted Nov 19, 2010 18:03 UTC (Fri) by kjp (guest, #39639) [Link] (6 responses)

That's interesting, I didn't realize DRBD had different sync modes. Do you have anything that does an automatic failover if the primary goes down, or is it a manual switchover?

DRBD

Posted Nov 19, 2010 20:39 UTC (Fri) by DG (subscriber, #16978) [Link]

Normally you'd use heartbeat for the switchover bit....

DRBD

Posted Nov 21, 2010 0:47 UTC (Sun) by butlerm (subscriber, #13312) [Link] (2 responses)

What I want to know is if DRBD and/or PostgreSQL has "catch up" capability when you want to switch back. After a false alarm, or temporary outage for example. Or do you have to shutdown the secondary, take a backup, and restore it on the primary?

DRBD

Posted Nov 21, 2010 10:41 UTC (Sun) by dlang (guest, #313) [Link]

since the postgres built-in replication is shipping the WAL log over, if you get just a bit behind you should be able to catch up.

however the server doesn't keep the WAL log around forever, so I would expect that if you get so far behind that the master no longer has a copy of things you have to do a backup.

note that this is speculation, I'm not an expert here

DRBD

Posted Nov 21, 2010 18:39 UTC (Sun) by ewan (guest, #5533) [Link]

I believe that as far as DRBD goes you can bring up the failed system as the slave, and just have it sync from the master in the normal manner until the next failover. Or, in short, 'Yes'.

I think.

DRBD

Posted Nov 22, 2010 0:31 UTC (Mon) by Richard_J_Neill (subscriber, #23093) [Link] (1 responses)

To answer a few of the questions here:

DRBD has 3 protocols for synchronisation. The middle one (B) is good enough for us: even if the primary server is completely obliterated, our data still survives, unless the secondary suffers a simultaneous UPS failure.

We could do auto-switchover, but in this case, we'd rather have an actual failure and manual change. If the warehouse stops for 5 minutes, it's inconvenient, but the risk of a switchover happening automatically and possible split-brain is worse. Also, perfect failover can sometimes lead to nobody noticing...and then you think your system has redundancy when it no longer does. (I had this happen once with a RAID5 system: one disk failed, and we didn't notice; since that experience, I tend to prefer systems that fail noisily.)

In terms of switchover, there are 2 parts: DRBD and postgres.

1. DRBD can operate with either system as master; if the link goes down and then comes back up, it will resynchronise quite quickly. Likewise, switching master and slave is fast. What you NEVER EVER want is "split brain" - where both halves of the system think they are primary, and they diverge, without it being obvious which one can be discarded.

2. We just switch postgres by mounting and unmounting /var/lib/pgsql on the drbd device. So only one server has the postgresql process running.

It's simply a case of:

drbdadm primary r0
mount /var/lib/postgresql
service postgresql start

and the converse.

DRBD

Posted Nov 22, 2010 6:58 UTC (Mon) by jjs (guest, #10315) [Link]

Way to solve split brain (and I believe Linux-HA has this, certainly RHAS does) is STONITH - Shoot the Other Node in the Head. Basically each system has a connection to the other's power strip. Before takeover it turns off (normally restarts) the power. So the other system is guaranteed dead on takeover, and comes back up as the slave.

Comparing MySQL and Postgres 9.0 Replication (TheServerSide)

Posted Nov 27, 2010 1:36 UTC (Sat) by vivo (subscriber, #48315) [Link]

As to replication filtering, MySQL provides filtering on the slave server, whereas with PostgreSQL, no filtering is available; in other worlds, the entire database from the master is replicated to the slave. With MySQL, all the information is sent, but then options exist to selectively apply the replicated events on the slave. However, as the MySQL binary log is not used for crash recovery purposes in the same way as PostgreSQLs WAL is, a user can configure a MySQL master so only certain databases are logged and, in that sense, a filter for the master server is available.

Found this confusing, anyway, speaking of Mysql 5.1:
- "replicate-ignore-db=mydb" + "USE mydb; /*query*/"
will NOT send the log to the slaves
- "replicate-wild-ignore-table = mydb.t1" + "USE mydb; INSERT INTO t1"
WILL send the log to the slaves and there it will be fltered