Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

pg_rewind in PostgreSQL 9.5

hlinnaka.iki.fi

Post reply on HN
Print view View original

31–40 of 63 posts

Re: pg_rewind in PostgreSQL 9.5

#31

Post by moe »

rosser wrote: post #27
moe wrote: post #9

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?

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: p…

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.

Re: pg_rewind in PostgreSQL 9.5

#32

Post by rosser »

moe wrote: post #31
rosser wrote: post #27

Earlier quoted context omitted.

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: p…

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.

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?

Re: pg_rewind in PostgreSQL 9.5

#33

Post by moe »

rosser wrote: post #32
moe wrote: post #31

Earlier quoted context omitted.

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.

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 the…

fencing

Which relates to a master/slave cluster as in how?

no other open source RDBMS out there manages those things either

Rethinkdb, Redis, Riak, ElasticSearch all do.

(them not being relational is irrelevant to replication)

Making it a runtime thing is one way of doing it.

It's the only way of doing it for modern, dynamic application layouts (the whole "cloud" thing). The stereotypical static database cluster, carefully configured by a graybeard once and then never touched again, it still exists. But it's rapidly losing ground.

Puppet clones the repo on spinning up a new machine, and I repoint the symlinks as necessary.

So how do you monitor e.g. the replication lag?

Re: pg_rewind in PostgreSQL 9.5

#34

Post by rosser »

moe wrote: post #33
rosser wrote: post #32

Earlier quoted context omitted.

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 the…

fencing Which relates to a master/slave cluster as in how? no other open source RDBMS out there manages those things either Rethinkdb, Redis, Riak, ElasticSearch all do. (them not being relational is irrelevant to replication) Making it a runtime thing is one way of doing it. It's the only way of doing it for modern, dynamic application layouts (the whole "cloud" thing). The stereotypical static database cluster, car…

fencing ... relates to a master/slave cluster as in how?

Because when you promote a slave, you also need to fence writes to the old master, otherwise you can end up in a "split-brain" scenario, where the old and new masters are both accepting writes, and nobody's replicating.

Have fun cleaning that up.

(them not being relational is irrelevant to replication)

I disagree. Their being relational imposes extra constraints upon replication. ACID?

So how do you monitor e.g. the replication lag?

Icinga invokes this: https://bucardo.org/check_postgres/check_postgres.pl.html#re...

Look, I'm rapidly losing interest in going back and forth like this. You don't seem very interested in the validity of another perspective, but rather just keep finding fault in things that don't do it your preferred way. If you want to have a discussion, I'll play. If not, let's just walk away.

Re: pg_rewind in PostgreSQL 9.5

#35

Post by jeffdavis »

bradhe wrote: post #19

every step forward in modern relational DBs for reliability is a step backwards for operations and the simplicity of the model. If you're steeped in the ecosystem and know how things "used to be" you don't see how insane things actually are. Forrest, trees, etc.

Can you please expand?

Re: pg_rewind in PostgreSQL 9.5

#36

Post by moe »

rosser wrote: post #34
moe wrote: post #33

Earlier quoted context omitted.

fencing Which relates to a master/slave cluster as in how? no other open source RDBMS out there manages those things either Rethinkdb, Redis, Riak, ElasticSearch all do. (them not being relational is irrelevant to replication) Making it a runtime thing is one way of doing it. It's the only way of doing it for modern, dynamic application layouts (the whole "cloud" thing). The stereotypical static database cluster, car…

fencing ... relates to a master/slave cluster as in how? Because when you promote a slave, you also need to fence writes to the old master, otherwise you can end up in a "split-brain" scenario, where the old and new masters are both accepting writes, and nobody's replicating. Have fun cleaning that up. (them not being relational is irrelevant to replication) I disagree. Their being relational imposes extra constraint…

Because when you promote a slave, you also need to fence writes to the master, otherwise you can end up in a "split-brain" scenario.

There is only one master, hence there can not be a "split brain". You seem to be confusing master/slave and multi-master configuration (which pg doesn't support).

I disagree. Their being relational imposes extra constraints upon replication. ACID?

Well, it doesn't.

Look, I'm rapidly losing interest in going back and forth like this.

You claim things are "easy" that you clearly haven't done before. I was merely calling you out on that, but agree the discussion is unlikely to go anywhere from here.

Edit: Oh and you retouching your comments all the time doesn't help either (the split-brain paragraph just changed...). I'm out.

Re: pg_rewind in PostgreSQL 9.5

#37

Post by rosser »

moe wrote: post #36
rosser wrote: post #34

Earlier quoted context omitted.

fencing ... relates to a master/slave cluster as in how? Because when you promote a slave, you also need to fence writes to the old master, otherwise you can end up in a "split-brain" scenario, where the old and new masters are both accepting writes, and nobody's replicating. Have fun cleaning that up. (them not being relational is irrelevant to replication) I disagree. Their being relational imposes extra constraint…

Because when you promote a slave, you also need to fence writes to the master, otherwise you can end up in a "split-brain" scenario. There is only one master, hence there can not be a "split brain". You seem to be confusing master/slave and multi-master configuration (which pg doesn't support). I disagree. Their being relational imposes extra constraints upon replication. ACID? Well, it doesn't. Look, I'm rapidly los…

You claim things are "easy" that you clearly haven't done before.

I've been a PostgreSQL DBA for a decade, and built HA setups for my last three employers. Care to reevaluate your claims of what I have or haven't done?

Re: pg_rewind in PostgreSQL 9.5

#38

Post by ploxiln »

moe wrote: post #33
rosser wrote: post #32

Earlier quoted context omitted.

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 the…

fencing Which relates to a master/slave cluster as in how? no other open source RDBMS out there manages those things either Rethinkdb, Redis, Riak, ElasticSearch all do. (them not being relational is irrelevant to replication) Making it a runtime thing is one way of doing it. It's the only way of doing it for modern, dynamic application layouts (the whole "cloud" thing). The stereotypical static database cluster, car…

Bringing up Redis, Riak, and ElasticSearch is nice...

You see, ElasticSearch gets split brain surprisingly easily, if you have a real significant load / number of hosts / host failures you will most definitely experience it, whereas with most other databases it'll only happen once every couple of years to the very largest and heaviest users. It's also just not known for data consistency, you just have to be able to refresh data from some other canonical source periodically.

Redis requires 2x memory present as is used to serve the load of requests, in order to backup. You can only safely really use half the server's memory. Because it has to fork to either save the snapshot or to compact the write log.

Riak performance is difficult to manage and keep consistent, as you replace failed nodes.

What I have experience with is a very large / heavy use of mysql at a popular website. We did manual/application sharding, and master/slave streaming replication, with automatic read-failover to slave, but only manual promotion. The slave did backups each day, and there was enough transaction log on the master and slave to spin up a new slave to either, with a backup up to 3 days old, and have it catch up. The nature of the load was that it was not possible to take a backup of the master in order to seed a new slave, because the traffic never stops and we could not afford the memory or iops overhead of backing up the master, and we really didn't do downtime if we could help it. A couple hours a year maybe.

We had trending and alerting of everything, including replication, roles, and we automated it all with something not unlike the configuration it appears you have to use for postgresql. Frankly these new-fangled auto-everything databases are untrustworthy, I've seen three of them fail (in ways you might not notice if you're not a graybeard), and the fourth has a name that really puts me off. RethinkDB? yeah, go ahead kids. I just love all this unreliable crap on the internet these days...

PS I'm 26 :)

Re: pg_rewind in PostgreSQL 9.5

#39

Post by moe »

ploxiln wrote: post #38
moe wrote: post #33

Earlier quoted context omitted.

fencing Which relates to a master/slave cluster as in how? no other open source RDBMS out there manages those things either Rethinkdb, Redis, Riak, ElasticSearch all do. (them not being relational is irrelevant to replication) Making it a runtime thing is one way of doing it. It's the only way of doing it for modern, dynamic application layouts (the whole "cloud" thing). The stereotypical static database cluster, car…

Bringing up Redis, Riak, and ElasticSearch is nice... You see, ElasticSearch gets split brain surprisingly easily, if you have a real significant load / number of hosts / host failures you will most definitely experience it, whereas with most other databases it'll only happen once every couple of years to the very largest and heaviest users. It's also just not known for data consistency, you just have to be able to r…

Frankly these new-fangled auto-everything databases are untrustworthy

I'm not asking for postgres to change its ways and become "untrustworthy".

I'm merely asking that postgres wraps more automation and convenience around the things that it already does.

Replication should generally be operated from inside psql. Spinning up a slave should be a single command. Failing over should be a single command. There should be meaningful error messages, ETAs and progress bars. No rsync'ing of WAL files, no futzing with config files. No elaborate third party 'repmgr' daemons.

Post reply on HN