9 ms·

This looks like a great tool, but it's also a sour reminder that replication still feels a lot like open heart surgery on postgresql. Why can't we just type "e

by moe 12y ago

This looks like a great tool, but it's also a sour reminder that replication still feels a lot like open heart surgery on postgresql. Why can't we just type "enslave 10.0.0.2" into psql and have the computer do the hard work? The machinery is "almost there" for a half a decade now. Who do we have to bribe (wink wink, nudge) to bring the UX into a state where crutches like pg_rewind are not needed?

Erwin 12y ago
I asked a few people that at the excellent pgnordic conference and got some hand-waving about how repmgr fixes everything: https://github.com/2ndQuadrant/repmgr/blob/master/QUICKSTART.md https://github.com/2ndQuadrant/repmgr/blob/master/QUICKSTART... Having tried that out, I didn't find it particular user friendly compared to the various fancy NoSQL database where it's just something like... database-server --connect the_master:12324 -- and you've got your cluster even with automatic replication of data depending on your sharding rules. I suppose that ACID-SQL makes it harder to set this up reliably. Is there one of the commercial things like EnterpriseDB that fixes that? Effortless, reliably clustering with a nice status that says: slave2 is 95% sync'ed with master1 ETA 2 hours.
afarrell 12y ago
> enslave "10.0.0.2" I know it is functionally immaterial, but boy howdy do I ever wish we'd chosen a better convention for how to refer to the relationship between these system components.
- scott_karana 12y ago
  > simon says 10.0.0.2
- twerquie 12y ago
  Let's just agree to use better words starting now. Primary/Replica?
- lmm 12y ago
  I find it's a useful heuristic for knowing whether someone can think clearly and abstractly. Kind of like the "all green birds have two heads" test.
  - afarrell 12y ago
    Could you expand on that? I get the reference, but what is your heuristic?
    - lmm 12y ago
      Someone who has a problem with master/slave terminology probably can't think abstractly (in the same sense as someone who fails the all green birds have two heads test).
- moe 12y ago
  I know it is functionally immaterial There's nothing wrong with Master/slave. We also read man pages, shove male plugs into female ports, fork and kill children, and use dozens of other odd words to describe perfectly harmless things.
  - waterhouse 12y ago
    http://en.wikipedia.org/wiki/Deforestation_%28computer_science%29 http://en.wikipedia.org/wiki/Deforestation_%28computer_scien...
  - kenko 12y ago
    "man" is at least short for "manual". There's nothing essential about master/slave so why not just switch to primary/replica?
    - robd003 12y ago
      Because the master forces the slave to do it's bidding.
      - diroussel 12y ago
        Not if the replica is pulling changes from the primary.
      - kenko 12y ago
        But ... so what? For one thing, the metaphor doesn't have to go that way. For another, "leader/follower" captures that too.
    - moe 12y ago
      Because there is no reason to screw with common terminology to begin with. Neither Master nor Slave is a derogatory word. And using the concept of slavery to describe a relationship between inanimate objects in a technical context is not an endorsement of such practices between humans.
- sanderjd 12y ago
  Heroku uses "follower", which is much less jarring.
- neilk 12y ago
  My proposal: muse and scribe. It's evocative and if you know the old terminology the initials are the same.
- olefoo 12y ago
  I try to use 'primary' and 'replica'; but depending on who I'm talking to I find myself slipping back to the Master/Slave terminology. And to the OP, replication is a complex process fraught with security risks. Take about 10 minutes thinking about just roles and authentication in the context of your proposed syntax and what you would need to set up ahead of time to make it work. The current method of starting with a pg_basebackup on the replica is nearly as simple. Even if it does require that you do some minor configuration on the master. It's not nearly open heart surgery.
  - moe 12y ago
    what you would need to set up ahead of time to make it work Nothing except a shared secret or keypair. Other databases (rethinkdb, redis, riak etc.) show how it's done.
    - deleted 12y ago
      [deleted]
rosser 12y ago
A thing people consistently seem not to get when bitching about PostgreSQL (streaming) replication is that, by design, it's slave pulls instead of master pushes. There are Very Good Reasons for that. And it's really not that hard. MVPRC (Minimum Viable PostgreSQL Replication Configuration): Master: postgresql.conf: wal_level = 'hot_standby' max_wal_senders = N pg_hba.conf: host replication all A.B.C.D/E md5 Slave: postgresql.conf: hot_standby = 'on' recovery.conf standby_mode = 'on' primary_conninfo = 'host=W port=X user=Y password=Z' Literally six lines of "directive = value". (Well, five plus the HBA rule.) Then you use pg_basebackup, and start the slave. EDIT: Decided to be specific instead of handwavy.
- moe 12y ago
  Literally six lines Which is six lines too many for what is inherently a runtime configuration that shouldn't be mentioned in any config file at all. Then you use pg_basebackup, and start the slave. I take it you don't care for failover and monitoring then? Otherwise you would have mentioned either repmgr and pgpool here, or your conglomerate of rather complex homegrown scripts to mimic their functionality.
  - rosser 12y ago
    No, and nor did I mention "fencing". I said "minimum viable". No other open source RDBMS manages those things, so calling postgres out for lacking them is a little disingenuous. Replication isn't "inherently" anything. Making it a runtime thing is one way of doing it. Just because it's your preferred way (or — as often seems to be the case among people who share your opinion about how postgres does it — just how they were first exposed to it) doesn't make any of the myriad other, perfectly viable ways of doing it wrong. As for my "conglomerate of homegrown scripts", I keep my db configs versioned in git, and use symlinks from $PGDATA into a role- and environment-specific (e.g., testing-master or production-slave or whatever) subdirectory in a clone of that repo. Puppet clones the repo on spinning up a new machine, and I repoint the symlinks as necessary. You know: $ ln -sf /path/to/repo/environment/role/*.conf $PGDATA/ Complex, huh?
jeffdavis 12y ago
I'd summarize the replication story of postgres to be: "harder to use but easier to understand". PostgreSQL generally builds things from the bottom up -- first working really hard to get the fundamental infrastructure right, and then the utility starts falling into place. This has a few advantages: * The fundamental infrastructure tends to be more general, so many different functional improvements come out quickly after the infrastructure is in place. * It forces everyone involved to think through the edge cases first, leading to a more robust feature and a better-tested feature. * When things go wrong, all of the parts make more sense individually, so it's easier to understand what happened and fix it. * Eventually, the usability issues are addressed in a way that's not dependent on a lot of black magic. Anecdotally, when things go wrong in $MAGICALLY_REPLICATING_DB, they tend to go very wrong, very fast. I am skeptical by nature, so I generally assume that $MAGICALLY_REPLICATING_DB glosses over a lot of the finer points, which don't get exercised in a normal testing environment. I haven't presented any facts here, so you may disagree. A lot of people share my skepticism, and are willing to put up with a few rough edges in the usability as long as the pieces make sense and they feel they can understand and trust them. I was at a presentation by Instagram, and replication was one of the primary reasons they went with postgres. They used a tiny engineering team (one and a half engineers working on the database) to scale to tens of millions of active users (also using sharding; another thing that is harder to use but easier to understand in postgres).

by moe 12y ago

Erwin 12y ago

I asked a few people that at the excellent pgnordic conference and got some hand-waving about how repmgr fixes everything: https://github.com/2ndQuadrant/repmgr/blob/master/QUICKSTART.md https://github.com/2ndQuadrant/repmgr/blob/master/QUICKSTART... Having tried that out, I didn't find it particular user friendly compared to the various fancy NoSQL database where it's just something like... database-server --connect the_master:12324 -- and you've got your cluster even with automatic replication of data depending on your sharding rules. I suppose that ACID-SQL makes it harder to set this up reliably. Is there one of the commercial things like EnterpriseDB that fixes that? Effortless, reliably clustering with a nice status that says: slave2 is 95% sync'ed with master1 ETA 2 hours.

afarrell 12y ago

> enslave "10.0.0.2" I know it is functionally immaterial, but boy howdy do I ever wish we'd chosen a better convention for how to refer to the relationship between these system components.

scott_karana 12y ago
> simon says 10.0.0.2
twerquie 12y ago
Let's just agree to use better words starting now. Primary/Replica?
lmm 12y ago
I find it's a useful heuristic for knowing whether someone can think clearly and abstractly. Kind of like the "all green birds have two heads" test.
- afarrell 12y ago
  Could you expand on that? I get the reference, but what is your heuristic?
  - lmm 12y ago
    Someone who has a problem with master/slave terminology probably can't think abstractly (in the same sense as someone who fails the all green birds have two heads test).
moe 12y ago
I know it is functionally immaterial There's nothing wrong with Master/slave. We also read man pages, shove male plugs into female ports, fork and kill children, and use dozens of other odd words to describe perfectly harmless things.
- waterhouse 12y ago
  http://en.wikipedia.org/wiki/Deforestation_%28computer_science%29 http://en.wikipedia.org/wiki/Deforestation_%28computer_scien...
- kenko 12y ago
  "man" is at least short for "manual". There's nothing essential about master/slave so why not just switch to primary/replica?
  - robd003 12y ago
    Because the master forces the slave to do it's bidding.
    - diroussel 12y ago
      Not if the replica is pulling changes from the primary.
    - kenko 12y ago
      But ... so what? For one thing, the metaphor doesn't have to go that way. For another, "leader/follower" captures that too.
  - moe 12y ago
    Because there is no reason to screw with common terminology to begin with. Neither Master nor Slave is a derogatory word. And using the concept of slavery to describe a relationship between inanimate objects in a technical context is not an endorsement of such practices between humans.
sanderjd 12y ago
Heroku uses "follower", which is much less jarring.
neilk 12y ago
My proposal: muse and scribe. It's evocative and if you know the old terminology the initials are the same.
olefoo 12y ago
I try to use 'primary' and 'replica'; but depending on who I'm talking to I find myself slipping back to the Master/Slave terminology. And to the OP, replication is a complex process fraught with security risks. Take about 10 minutes thinking about just roles and authentication in the context of your proposed syntax and what you would need to set up ahead of time to make it work. The current method of starting with a pg_basebackup on the replica is nearly as simple. Even if it does require that you do some minor configuration on the master. It's not nearly open heart surgery.
- moe 12y ago
  what you would need to set up ahead of time to make it work Nothing except a shared secret or keypair. Other databases (rethinkdb, redis, riak etc.) show how it's done.
  - deleted 12y ago
    [deleted]

rosser 12y ago

A thing people consistently seem not to get when bitching about PostgreSQL (streaming) replication is that, by design, it's slave pulls instead of master pushes. There are Very Good Reasons for that. And it's really not that hard. MVPRC (Minimum Viable PostgreSQL Replication Configuration): Master: postgresql.conf: wal_level = 'hot_standby' max_wal_senders = N pg_hba.conf: host replication all A.B.C.D/E md5 Slave: postgresql.conf: hot_standby = 'on' recovery.conf standby_mode = 'on' primary_conninfo = 'host=W port=X user=Y password=Z' Literally six lines of "directive = value". (Well, five plus the HBA rule.) Then you use pg_basebackup, and start the slave. EDIT: Decided to be specific instead of handwavy.

moe 12y ago
Literally six lines Which is six lines too many for what is inherently a runtime configuration that shouldn't be mentioned in any config file at all. Then you use pg_basebackup, and start the slave. I take it you don't care for failover and monitoring then? Otherwise you would have mentioned either repmgr and pgpool here, or your conglomerate of rather complex homegrown scripts to mimic their functionality.
- rosser 12y ago
  No, and nor did I mention "fencing". I said "minimum viable". No other open source RDBMS manages those things, so calling postgres out for lacking them is a little disingenuous. Replication isn't "inherently" anything. Making it a runtime thing is one way of doing it. Just because it's your preferred way (or — as often seems to be the case among people who share your opinion about how postgres does it — just how they were first exposed to it) doesn't make any of the myriad other, perfectly viable ways of doing it wrong. As for my "conglomerate of homegrown scripts", I keep my db configs versioned in git, and use symlinks from $PGDATA into a role- and environment-specific (e.g., testing-master or production-slave or whatever) subdirectory in a clone of that repo. Puppet clones the repo on spinning up a new machine, and I repoint the symlinks as necessary. You know: $ ln -sf /path/to/repo/environment/role/*.conf $PGDATA/ Complex, huh?

jeffdavis 12y ago

I'd summarize the replication story of postgres to be: "harder to use but easier to understand". PostgreSQL generally builds things from the bottom up -- first working really hard to get the fundamental infrastructure right, and then the utility starts falling into place. This has a few advantages: * The fundamental infrastructure tends to be more general, so many different functional improvements come out quickly after the infrastructure is in place. * It forces everyone involved to think through the edge cases first, leading to a more robust feature and a better-tested feature. * When things go wrong, all of the parts make more sense individually, so it's easier to understand what happened and fix it. * Eventually, the usability issues are addressed in a way that's not dependent on a lot of black magic. Anecdotally, when things go wrong in $MAGICALLY_REPLICATING_DB, they tend to go very wrong, very fast. I am skeptical by nature, so I generally assume that $MAGICALLY_REPLICATING_DB glosses over a lot of the finer points, which don't get exercised in a normal testing environment. I haven't presented any facts here, so you may disagree. A lot of people share my skepticism, and are willing to put up with a few rough edges in the usability as long as the pieces make sense and they feel they can understand and trust them. I was at a presentation by Instagram, and replication was one of the primary reasons they went with postgres. They used a tiny engineering team (one and a half engineers working on the database) to scale to tens of millions of active users (also using sharding; another thing that is harder to use but easier to understand in postgres).