6 ms·

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

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

acdha 10y ago
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.
api 10y ago
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.
scurvy 10y ago
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.
- sorkin2 10y ago
  > 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.html https://www.postgresql.org/docs/current/static/app-pgrewind....
  - scurvy 10y ago
    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!
    - mb4nck 10y ago
      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 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.
      - scurvy 9y ago
        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).
mb4nck 10y ago
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).

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

acdha 10y ago

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.

api 10y ago

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.

scurvy 10y ago

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.

sorkin2 10y ago
> 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.html https://www.postgresql.org/docs/current/static/app-pgrewind....
- scurvy 10y ago
  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!
  - mb4nck 10y ago
    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 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.
    - scurvy 9y ago
      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).

mb4nck 10y ago

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