5 ms·

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 t

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.

tene 14y ago
"Getting better" isn't entirely relevant to this specific case, as the relevant bug was introduced in 5.5.6. Personally, I've had quite a few horrible experiences with mysql replication interacting poorly with various mysql features, and I've lost more time than I'd care to admit trying to figure out workarounds. MySQL wasn't designed with data integrity in mind, and that's had significant influence on its development and ecosystem. 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.
- nknighthb 14y ago
  It may also have something to do with the fact that the first version of PostgreSQL to include replication did not emerge until late 2010. Prior to 9.0, it was logically impossible for PostgreSQL itself to have a "replication failure".
  - tene 14y ago
    Yes, that's certainly relevant as well, although I disagree with some of the implications of "logically impossible" due to the third party postgresql replication products, although I don't have personal experience with them.
thaumaturgy 14y ago
Sure: stock Postgres doesn't have multi-master capability at all. Postgres-XC does (http://postgres-xc.sourceforge.net/ http://postgres-xc.sourceforge.net/), but I didn't know about that when I finally decided to move from a mix of MySQL and Postgres to MySQL only.
- tekacs 14y ago
  The article doesn't seem to suggest that multi-master was being used here, rather that many clones were being replicated from a single master - am I missing any reason to believe otherwise?
  - thaumaturgy 14y ago
    Nope. And indeed, maybe Postgres would work better than MySQL for Kickstarter. I don't know anything about their architecture. I was just responding to the question about Postgres with an example where I found Postgres lacking.
- sparkman55 14y ago
  From the article: > 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.
  - stcredzero 14y ago
    > Mysql replication has been around for ages; it seems to blow up spectacularly on occasion. 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."
falcolas 14y ago
PostgreSQL uses essentially MySQL's ROW based format (that is, it replicates the actual table changes instead of the statements). If you use purely row based replication, you likely wouldn't see this in MySQL either. Of course, since TANSTAAFL, your network traffic between nodes is much higher, and the disk storage requirements grow significantly (for the binary logs). 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?
- larrik 14y ago
  I don't know about the downvotes, but using anything non-default in MySQL is dangerous because the devs don't test well enough. I've had enough troubles just with transactions to never consider MySQL for a serious project ever again.
  - falcolas 14y ago
    > I've had enough troubles just with transactions 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.
lobster_johnson 14y ago
I doubt you will see anything similar with Postgres, simply because it's too strict and conservative about how it replicates. 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.

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.

tene 14y ago

"Getting better" isn't entirely relevant to this specific case, as the relevant bug was introduced in 5.5.6. Personally, I've had quite a few horrible experiences with mysql replication interacting poorly with various mysql features, and I've lost more time than I'd care to admit trying to figure out workarounds. MySQL wasn't designed with data integrity in mind, and that's had significant influence on its development and ecosystem. 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.

nknighthb 14y ago
It may also have something to do with the fact that the first version of PostgreSQL to include replication did not emerge until late 2010. Prior to 9.0, it was logically impossible for PostgreSQL itself to have a "replication failure".
- tene 14y ago
  Yes, that's certainly relevant as well, although I disagree with some of the implications of "logically impossible" due to the third party postgresql replication products, although I don't have personal experience with them.

thaumaturgy 14y ago

Sure: stock Postgres doesn't have multi-master capability at all. Postgres-XC does (http://postgres-xc.sourceforge.net/ http://postgres-xc.sourceforge.net/), but I didn't know about that when I finally decided to move from a mix of MySQL and Postgres to MySQL only.

tekacs 14y ago
The article doesn't seem to suggest that multi-master was being used here, rather that many clones were being replicated from a single master - am I missing any reason to believe otherwise?
- thaumaturgy 14y ago
  Nope. And indeed, maybe Postgres would work better than MySQL for Kickstarter. I don't know anything about their architecture. I was just responding to the question about Postgres with an example where I found Postgres lacking.
sparkman55 14y ago
From the article: > 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.
- stcredzero 14y ago
  > Mysql replication has been around for ages; it seems to blow up spectacularly on occasion. 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."

falcolas 14y ago

PostgreSQL uses essentially MySQL's ROW based format (that is, it replicates the actual table changes instead of the statements). If you use purely row based replication, you likely wouldn't see this in MySQL either. Of course, since TANSTAAFL, your network traffic between nodes is much higher, and the disk storage requirements grow significantly (for the binary logs). 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?

larrik 14y ago
I don't know about the downvotes, but using anything non-default in MySQL is dangerous because the devs don't test well enough. I've had enough troubles just with transactions to never consider MySQL for a serious project ever again.
- falcolas 14y ago
  > I've had enough troubles just with transactions 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.

lobster_johnson 14y ago

I doubt you will see anything similar with Postgres, simply because it's too strict and conservative about how it replicates. 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.