pg_rewind in PostgreSQL 9.5 • Hacker News Board

Re: pg_rewind in PostgreSQL 9.5

#11

Post by Erwin » Mon, Mar 23, 2015, 10:53 PM UTC

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?

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...

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.

Re: pg_rewind in PostgreSQL 9.5

#12

Post by barrkel » Mon, Mar 23, 2015, 11:00 PM UTC

iradik wrote: post #6

how does mysql failover compare in this regard?

MySQL in master/master mode is fairly painless. Set the secondary site to readonly mode, and when failing over, ensure the primary site is definitely down before enabling writes on the secondary site.

When failing back, bring the primary site back up in readonly mode, give it some time to catch up, make secondary site readonly again, verify if you want to be sure (Percona tools help here), and finally make primary site read-write.

The initial setup in MySQL is a bit fiddly, but overall it's been fairly problem-free as far as I've seen. It's not automatic, but the cost of manual intervention is far lower than the headache of split brain and write divergence.

(Disclaimer: I far prefer postgres as a development target, but hassle-free failover and failback is a hard requirement where I work, and our business model includes giving every customer (banks etc.) a completely separate database instance, including multiple VMs on a separate vlan; per-CPU costs don't work out, it's pretty much MySQL or nothing, until postgres makes it just as pain-free.)

Re: pg_rewind in PostgreSQL 9.5

#13

Post by nierman » Mon, Mar 23, 2015, 11:22 PM UTC

If you are performing a planned failover then the old master can be turned into a slave without extra tools or steps. Simply shut down the master first. As part of this process it waits until the slave has the necessary wal.

pg_rewind will be great for remastering under other scenarios (unexpected failovers, etc.)

Re: pg_rewind in PostgreSQL 9.5

#14

Post by mappu » Mon, Mar 23, 2015, 11:24 PM UTC

Can anyone comment on how this compares to postgres-BDR?

I'm in the market for an asynchronous multi-master RDBMS to cope with a dozen masters and huge (~800ms) latencies - i think my best bets are either BDR or maybe the Cassandra storage engine for MariaDB.

Re: pg_rewind in PostgreSQL 9.5

#15

Post by afarrell » Mon, Mar 23, 2015, 11:58 PM UTC

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?

> 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.

Re: pg_rewind in PostgreSQL 9.5

#16

Post by scott_karana » Tue, Mar 24, 2015, 12:09 AM UTC

afarrell wrote: post #15

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?

> 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.

> simon says 10.0.0.2

Re: pg_rewind in PostgreSQL 9.5

#17

Post by twerquie » Tue, Mar 24, 2015, 12:20 AM UTC

afarrell wrote: post #15

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?

> 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.

Let's just agree to use better words starting now.

Primary/Replica?

Re: pg_rewind in PostgreSQL 9.5

#18

Post by moe » Tue, Mar 24, 2015, 12:33 AM UTC

afarrell wrote: post #15

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?

> 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.

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.

Re: pg_rewind in PostgreSQL 9.5

#19

Post by bradhe » Tue, Mar 24, 2015, 12:35 AM UTC

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.

Re: pg_rewind in PostgreSQL 9.5

#20

Post by sanderjd » Tue, Mar 24, 2015, 12:40 AM UTC

afarrell wrote: post #15

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?

> 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.

Heroku uses "follower", which is much less jarring.