Streaming replication isn't hard at all: http://davide.im/setting-up-a-failover-database-for-postgres...

It's not hard to setup initially, but I'll admit that it's not very good.

It's not very good in a long-lived scenario where you're changing your replication topology for routine maintenance tasks. Changing from master to replica is easy, but now you have to rebuild that original master off of the former replica now. Completely start over. You can't just start up again from a given transaction ID. MySQL's GTID implementation is much better in this regard. You can change masters and replicas all repoint them without rebuilding. You can't do that (currently) with Postgresql. It's a major pain point.

> Changing from master to replica is easy, but now you have to rebuild that original master off of the former replica now. Completely start over. You can't just start up again from a given transaction ID. MySQL's GTID implementation is much better in this regard. You can change masters and replicas all repoint them without rebuilding. You can't do that (currently) with Postgresql.

Have you heard of pg_rewind? https://www.postgresql.org/docs/current/static/app-pgrewind....

I had not. Looks like it requires 9.5 or later? We're running 9.4 so we'll have to upgrade to use it. Thanks!

You can get pg_rewind for 9.4 (and 9.3 in its branch) here:

https://github.com/vmware/pg_rewind/tree/REL9_4_STABLE

It's from the people who wrote it upstream, they provide the code there for earlier Postgres releases.

Does this help at all with upgrades? Upgrading from 9.4 to 9.5 means you need to rebuild your entire replication topology because the master's identifier has changed (in the initdb step of the official docs).

Yes but here's the problem. Consider common scenarios like:

Master goes down. Slave takes over. Master comes back. Slave goes down 10 minutes later. Repeat.

This is common in e.g. multi data center replication and is often due to transient network failures. Netflix has a great open source tool called chaos monkey that can induce lots of random failure scenarios like this or much worse. Don't get me started on transient partial failures due to latency and packet loss spikes.

The manual nature of pg replication setup makes me really nervous here. What happens when it finds itself in a state where manual intervention is needed? You are now down.

This is tolerable for big companies with dedicated SREs and DBAs and enough of them that it's easy to always have someone on call, but it's a nightmare for smaller ventures. Even for larger ventures this adds a lot of cost overhead.

Like I said elsewhere this was really the true killer feature of the more successful NoSQL document store type databases. Everything else was largely hype.

We switched recently to RethinkDB for this reason. We miss the richness of SQL (to the point that we still use PG too for warehousing and analytics) but in return we got incredible robustness across three data centers. Of course our app does not need rich queries or strong consistency 99% of the time so YMMV. For some jobs ACID and complex queries on live data are not optional.

Note that starting from Postgres 10 (which this thread is about), you don't need to adjust wal_level and max_wal_senders (or max_replication_slots, for that matter) anymore. You still have to enable hot_standby=on on the standbys, though.

(and it is in general a good idea to keep the configuration the same as much as possible between primary and standbys).

Thanks! I was asking here mostly out of curiousity about how people felt after running it for awhile since it has certainly sounded like it has improved massively since I last dealt with it in the 8.x era.

get two or three dedicated servers, stick a 2 disk mirrored raid in each one, run one primary and have barman manage your snapshots+streaming replication. hook in some cronjobs to monitor age of stuff and failure of stuff. that is pretty much it for anything less than, say, 5TB. Beyond that your copy times are too high and making snapshots becomes extremely difficult.