Can someone point to a big postgres replication failure event? I know mysql is getting better, but I still see public failures like this much more frequently than big postgres (or Oracle) failures.
Also, RDS doesn't offer anything but mixed format replication for slaves, and DRBD for HA.
The best middle ground when ROW based replication isn't an option is to check for inconsistencies periodically using something like pt-table-checksum, and fix them when they're found with something like pt-table-sync.
[EDIT] Can you please explain the downvotes?
I've had enough troubles just with transactions to never consider MySQL for a serious project ever again.
What kind of troubles? It's a serious question; I work with InnoDB transactions on a daily basis, and if I can find more real issues, I can usually get them resolved (or explain the behavior).
MySQL is a very powerful and performant DB (and, contrary to seeming popular opinion, 100% production ready), but it's not always obvious why things work the way they do.
Answering your actual question, no, I've never heard of a big postgres replication failure event, but that's also plausibly explained by postgresql's smaller market share and visibility.
Postgres replicates transactions rather than rows or statements. Postgres uses a transaction log, aka xlog (also called the write-ahead log, or WAL), and this is streamed to slaves where the xlog entries are replayed. It's analogous to Oracle's redo log. Each log entry is information about what tuples to update (or insert, or delete), and what to update with.
This means that the replication data flow is basically the same as the data flow that occurs on a single server when clients execute SQL. Something like "update foo set a = 1" will result in a log entry that describes how the physical database files are updated; therefore, replication is a matter of applying the same log entry on the master as on the slaves. (This system is also used to implement streaming backups: You can reconstruct the database at any point in time simply by replaying old transaction logs up to the point in time that you want.)
Everything that involves state change is encapsulated by transaction logging. Postgres has transactional DDL -- in other words, you can do things like "create table", "alter table", "drop table" etc. in transactions -- precisely thanks to this symmetry. It also means that the replication state is unaffected by context: Things like sequences and timestamps are made consistent because they are, by necessity, already calculated by the time the transaction log is written.
So with Postgres' replication it's virtually impossible to end up in a situation like Kickstarter's, where duplicate IDs are replicated. Of course, physical corruption or some weird bug could cause this, but at least the latter is very unlikely, and if it did happen it would very likely hit more than just replication, again because of how the xlog is so central to the entire system. In other words, an xlog bug that only happened to replication would be fairly rare.
I was just responding to the question about Postgres with an example where I found Postgres lacking.
> the replicas were out of sync with the master
Most replicated relational database environments are single-master environments; this mirrors the read-oriented traffic of many database use cases.
Most multi-master solutions require you to leave parts of SQL and ACID behind - the advantage is significantly better write scalability and write availability.
Mysql replication has been around for ages; it seems to blow up spectacularly on occasion. Postgres replication is relatively new, and I'm wondering if it also has similar issues. Anecdotal evidence suggests that it has not: I have never been awakened by panicked developers/operators because of postgres replication, but mysql replication has caused more than one sleepless nights.
For us "old school" guys who have been programming for awhile, this is truly eyebrow-raising. Hearing that DB replication "seems to blow up spectacularly on occasion"...there's something wrong with this picture. A tool like DB replication should be at a much higher level of reliability than application code. It also confirms a lot of the grousing I've heard about the engineering hubris of MySQL over the last decade.
Also reminds me of Alan Kay's quip about what makes programming, "not quite a field."
MySQL replication feels like a hack, not the sort of thing that people can use as part of a reliable infrastructure.
My MySQL replication wishlist would be:
1. bidirectional communication protocol so that slaves can ask the master for a fresh copy of some particular data in the event of an error;
2. built-in notifications for anything that might make an alcoholic out of a sober sysadmin;
3. periodic idle-time consistency checking (master: "I have X tables with Z definitions and N rows each"; slave: "something is wrong with my copy of Y, I need rows 1 - 100").
I have a couple of projects in my pipeline right now that are being held up entirely by the feeling that MySQL is not yet reliable enough and I need to build better monitoring and automated error-handling systems first.
MySQL <X> feels like a hack, not the sort of thing that people can use as part of a reliable infrastructure.
However I do see something a bit... off... about your 2nd concern. Why would you want a database to handle its own monitoring? I'd personally rather just set up a set of MySQL monitoring plugins into Nagios. That way all of my monitoring is one place.
Consistency checking across terabytes of data on multiple servers is a hard problem. It would be great if it could be solved, but I'm not holding my breath for them (well, any DB vendor for that matter) to get it right.
How would you monitor memory, disk and load usage?
Sure - Nagios has its issues, but there are quite a few alternatives that can do the same thing, all of which can interface with your database for monitoring.
We did have a weird replication failure at one point when running pg_repack. It's a tool that reclusters tables by duplicating them (essentially writing a new table sequentially) and then renaming the duplicate back to the original table name. This caused the slave to suddenly try to access a table that had not been created on its end yet. But it's possible that it was due to misconfiguration; we are currently investigating it so we can collect enough to data to maybe submit a bug report.
Postgres-XC looks interesting, but I have not heard of anyone using it in production yet. Would love to hear if anyone has used it for anything serious.