5 ms·

It's been on my list for a long time, but I've also struggled to find out like... what, exactly, is the right way to do postgres replication? Every time I go in

by aphyr 6y ago

It's been on my list for a long time, but I've also struggled to find out like... what, exactly, is the right way to do postgres replication? Every time I go into the docs I wind up with a laundry list of different mechanisms for replication and failover, and no idea which one would be most appropriate for a test. I gotta get on this!

bsaul 6y ago
i feel like this is the reaction of everyone having ever tried to setup postgres replication. With your audience, you deciding for a particular setup will probably help a LOT of people, and ultimately the postgres project as well.
- takeda 6y ago
  If you worry about data, you should not use automatic failover. It's nearly impossible for standby to know why master stopped responding. Maybe there was a hardware failure, or maybe master is just busy. This is why manual failover is better, because you can know the real reason and decide whether you should perform failover or just wait. With tools like repmgr it is just a single command invoked on the standby. If you absolutely don't want to lose any data, you should have two masters in close proximity (so the latency isn't high) set up with synchronous replication, then have one or two standbys with asynchronous replication. This will reduce throughout, but then you can be sure that the other machine has all the same transactions. If something happens to both you then can fallback to the asynchronous one which might be a bit behind.
  - feike 6y ago
    One of the authors of Patroni here. Automatic failover for PostgreSQL works great and can be done safely if combined with synchronous replication. Multiple tools will implement this correctly: https://patroni.readthedocs.io/en/latest/replication_modes.html#postgresql-synchronous-replication https://patroni.readthedocs.io/en/latest/replication_modes.h... https://github.com/sorintlab/stolon/blob/master/doc/syncrepl.md#synchronous-replication https://github.com/sorintlab/stolon/blob/master/doc/syncrepl... Quoting a former colleague here, but "if it hurts, do it more often". That is what you should do with your PostgreSQL failovers. I have clusters running on timelines in the hundreds without a byte of data loss due to using synchronous replication, tools that help out with leader election, and just doing it often.
    - takeda 6y ago
      Can Patroni tell if master node is not responsive because it is busy vs dead? GitHub (I believe) had few outages that caused data loss because their auto failover mechanism kicked in when it shouldn't. I would actually be interested if aphyr's analysis of Patroni and other distributed add-ons to PostgreSQL.
      - pas 6y ago
        There is no real difference between dead or too-busy. The only question is how soon are you going to page humans. After the automated mechanism flipped your master 2-3 times but the cluster still hasn't made progress [nothing coming out of the master; or it locks up after a few minutes again]), or right after some other automated mechanism detects that there's a problem. Whatever automation you have in place, it has advantages and disadvantages. In the GitHub case - I suppose - they determined post-mortem that it would have been better to just let the master chug through the incoming onslaught of queries instead of failing over, and over, and over. (But of course this seems like a trivial problem in any auto failover setup, so I suspect there's more to the story.)
      - zozbot234 6y ago
        > tell if master node is not responsive because it is busy vs dead? The postgres documentation will tell you that you'll need to set up your own mechanisms for this, and that they will need to integrate with OS facilities as appropriate. One-size-fits-all does not cut it. Not wrt. replication, not wrt. HA/failover.
      - feike 6y ago
        > Can Patroni tell if master node is not responsive because it is busy vs dead No. But the contract Patroni has is this: I only serve a master (primary) if I have the lock. If I do not have the lock I will demote. This results in that there can be only 1 primary active at any given point in time, even if the network is partitioned. This in and of itself does not guarantee no-split-brain situations, a split-brain can occur if writes were made on the former primary, but not yet on the future primary. This however can be mitigated with synchronous replication.
zbjornson 6y ago
It'd be especially interesting given that MongoDB claims this: > Postgres has both asynchronous (the default) and synchronous replication options, neither of which offers automatic failure detection and failover [12]. The synchronous replication only waits for durability on one additional node, regardless of how many nodes exist [13]. Additionally, Postgres allows one to tune these durability behaviors at the user level. When reading from a node, there is no way to specify the durability or recency of the data read. A query may return data that is subsequently lost. Additionally, Postgres does not guarantee clients can read their own writes across nodes. From http://www.vldb.org/pvldb/vol12/p2071-schultz.pdf http://www.vldb.org/pvldb/vol12/p2071-schultz.pdf
- takeda 6y ago
  > It'd be especially interesting given that MongoDB claims this: > > Postgres has both asynchronous (the default) and synchronous replication options, neither of which offers automatic failure detection and failover [12]. The synchronous replication only waits for durability on one additional node, regardless of how many nodes exist [13]. Additionally, Postgres allows one to tune these durability behaviors at the user level. When reading from a node, there is no way to specify the durability or recency of the data read. A query may return data that is subsequently lost. Additionally, Postgres does not guarantee clients can read their own writes across nodes. > From http://www.vldb.org/pvldb/vol12/p2071-schultz.pdf http://www.vldb.org/pvldb/vol12/p2071-schultz.pdf This is like those commonly seen tables comparing your product with others where your product had checkmarks in all categories, and of course competitors are missing a bunch of them. The problem is that the categories were picked by you, and are often irrelevant to the other product. This is the case here. PostgreSQL is not a distributed database, the master is the one doing all writes. The replicas are read only. By default replicas are asynchronous which means they won't affect master performance, at the cost of having data there being late by few seconds. Since you can't write to replicas, this won't cause data corruption, only delay which often is acceptable. If you design your applications in such way that will have two database endpoints: one for writes and one just for reads, you can then decide based on context which endpoint you want to use. The read only is easy to scale, but as mentioned earlier it is read only, and might slight delay. Now, for failover, you might also opt on using synchronous replicas this will add extra latency, but then you always have at least one machine that has the same data. They mentioned that if you have multiple synchronous standbys then it only one needs to write. Actually that's configurable, you can specify group of synchronous machines and how many and which need to be synchronized, the remaining ones are a backup in case those that you specified aren't available. Besides, the writes don't work the same way as in mongo, when a standby node is in sync it isn't just in sync for that particular write, it is completely in sync, so their following argument about not being able to specify durability/recency of data on read is redundant. If you contact the master or synchronous replica, you will always get the most recent state. If you don't mind slight delay you should query asynchronous replicas (in fact you should prefer them whenever you can, since those are cheap to add)
takeda 6y ago
Well, the built-in ways is the right way to do it. But given that PostgreSQL is quite conservative about it, it will be hard to find issues there (the replicas are read only, so at worst it will be just a replication delay, unless you use synchronous replication, which will remove the replication delay at the cost of slower performance). All the tooling that provides extra distributed functionality not present in postgres (auto failover, multi master replication, sharding etc) will surely have issues, but then you aren't testing the PostgreSQL itself, but the tooling, so to be fair, you the article should evaluate these tools, and any shortcomings shouldn't go to PostgreSQL (unless it really is a PostgreSQL issue).
- pas 6y ago
  This k8s operator advertises production-grade auto-failover auto-healing replicas - basically what MongoDB - but built on postgres: https://github.com/CrunchyData/postgres-operator https://github.com/CrunchyData/postgres-operator And looking at this table, basically the future seems to be WAL shipping anyway ( https://www.postgresql.org/docs/13/different-replication-solutions.html https://www.postgresql.org/docs/13/different-replication-sol... )
pcl 6y ago
I think that it'd be super-valuable to do an analysis of an RDS Postgres deployment. Amazon is doing some dark magic with RDS that sits at this really interesting "distributed, but not that distributed" inflection point, which impacts the basic assumptions of lots of distributed database design. I believe RDS Postgres is probably the right answer for lots of applications, especially for those that already depend on AWS for baseline availability. I'd love to see if that holds up against a rigorous analysis.
- aeyes 6y ago
  Are you talking about Aurora? Because in RDS the replication is just what you get out of the box.
- aphyr 6y ago
  I'd like this too, but I'm not sure how to do fault injection against an Amazon-controlled service.
  - ashtonkem 6y ago
    You’d probably have to work directly with AWS on that one, either to get a custom harness in AWS, or to find out how they configure RDS replication.
  - elesbao 6y ago
    the setup would prob ably be pgsql primary, aurora secondaries on diff zones and something changing cross-zone or cross-region vpc setting to try to break replication ? never tried that but was hurt by rds pure pgsql cross region replication in a network outage situation.
didip 6y ago
It is true that only recently PG has a standard way of replicating. But even then, PG is not a distributed database by default. However, if I may suggest, Stolon, Patroni, Postgres XL or Citus Data might be interesting to you.
grogers 6y ago
I know you've done galera in the past, but MySQL group replication would be a good one since it IS the defacto HA solution, unlike with Postgres.

by aphyr 6y ago

bsaul 6y ago

i feel like this is the reaction of everyone having ever tried to setup postgres replication. With your audience, you deciding for a particular setup will probably help a LOT of people, and ultimately the postgres project as well.

takeda 6y ago
If you worry about data, you should not use automatic failover. It's nearly impossible for standby to know why master stopped responding. Maybe there was a hardware failure, or maybe master is just busy. This is why manual failover is better, because you can know the real reason and decide whether you should perform failover or just wait. With tools like repmgr it is just a single command invoked on the standby. If you absolutely don't want to lose any data, you should have two masters in close proximity (so the latency isn't high) set up with synchronous replication, then have one or two standbys with asynchronous replication. This will reduce throughout, but then you can be sure that the other machine has all the same transactions. If something happens to both you then can fallback to the asynchronous one which might be a bit behind.
- feike 6y ago
  One of the authors of Patroni here. Automatic failover for PostgreSQL works great and can be done safely if combined with synchronous replication. Multiple tools will implement this correctly: https://patroni.readthedocs.io/en/latest/replication_modes.html#postgresql-synchronous-replication https://patroni.readthedocs.io/en/latest/replication_modes.h... https://github.com/sorintlab/stolon/blob/master/doc/syncrepl.md#synchronous-replication https://github.com/sorintlab/stolon/blob/master/doc/syncrepl... Quoting a former colleague here, but "if it hurts, do it more often". That is what you should do with your PostgreSQL failovers. I have clusters running on timelines in the hundreds without a byte of data loss due to using synchronous replication, tools that help out with leader election, and just doing it often.
  - takeda 6y ago
    Can Patroni tell if master node is not responsive because it is busy vs dead? GitHub (I believe) had few outages that caused data loss because their auto failover mechanism kicked in when it shouldn't. I would actually be interested if aphyr's analysis of Patroni and other distributed add-ons to PostgreSQL.
    - pas 6y ago
      There is no real difference between dead or too-busy. The only question is how soon are you going to page humans. After the automated mechanism flipped your master 2-3 times but the cluster still hasn't made progress [nothing coming out of the master; or it locks up after a few minutes again]), or right after some other automated mechanism detects that there's a problem. Whatever automation you have in place, it has advantages and disadvantages. In the GitHub case - I suppose - they determined post-mortem that it would have been better to just let the master chug through the incoming onslaught of queries instead of failing over, and over, and over. (But of course this seems like a trivial problem in any auto failover setup, so I suspect there's more to the story.)
    - zozbot234 6y ago
      > tell if master node is not responsive because it is busy vs dead? The postgres documentation will tell you that you'll need to set up your own mechanisms for this, and that they will need to integrate with OS facilities as appropriate. One-size-fits-all does not cut it. Not wrt. replication, not wrt. HA/failover.
    - feike 6y ago
      > Can Patroni tell if master node is not responsive because it is busy vs dead No. But the contract Patroni has is this: I only serve a master (primary) if I have the lock. If I do not have the lock I will demote. This results in that there can be only 1 primary active at any given point in time, even if the network is partitioned. This in and of itself does not guarantee no-split-brain situations, a split-brain can occur if writes were made on the former primary, but not yet on the future primary. This however can be mitigated with synchronous replication.

zbjornson 6y ago

It'd be especially interesting given that MongoDB claims this: > Postgres has both asynchronous (the default) and synchronous replication options, neither of which offers automatic failure detection and failover [12]. The synchronous replication only waits for durability on one additional node, regardless of how many nodes exist [13]. Additionally, Postgres allows one to tune these durability behaviors at the user level. When reading from a node, there is no way to specify the durability or recency of the data read. A query may return data that is subsequently lost. Additionally, Postgres does not guarantee clients can read their own writes across nodes. From http://www.vldb.org/pvldb/vol12/p2071-schultz.pdf http://www.vldb.org/pvldb/vol12/p2071-schultz.pdf

takeda 6y ago
> It'd be especially interesting given that MongoDB claims this: > > Postgres has both asynchronous (the default) and synchronous replication options, neither of which offers automatic failure detection and failover [12]. The synchronous replication only waits for durability on one additional node, regardless of how many nodes exist [13]. Additionally, Postgres allows one to tune these durability behaviors at the user level. When reading from a node, there is no way to specify the durability or recency of the data read. A query may return data that is subsequently lost. Additionally, Postgres does not guarantee clients can read their own writes across nodes. > From http://www.vldb.org/pvldb/vol12/p2071-schultz.pdf http://www.vldb.org/pvldb/vol12/p2071-schultz.pdf This is like those commonly seen tables comparing your product with others where your product had checkmarks in all categories, and of course competitors are missing a bunch of them. The problem is that the categories were picked by you, and are often irrelevant to the other product. This is the case here. PostgreSQL is not a distributed database, the master is the one doing all writes. The replicas are read only. By default replicas are asynchronous which means they won't affect master performance, at the cost of having data there being late by few seconds. Since you can't write to replicas, this won't cause data corruption, only delay which often is acceptable. If you design your applications in such way that will have two database endpoints: one for writes and one just for reads, you can then decide based on context which endpoint you want to use. The read only is easy to scale, but as mentioned earlier it is read only, and might slight delay. Now, for failover, you might also opt on using synchronous replicas this will add extra latency, but then you always have at least one machine that has the same data. They mentioned that if you have multiple synchronous standbys then it only one needs to write. Actually that's configurable, you can specify group of synchronous machines and how many and which need to be synchronized, the remaining ones are a backup in case those that you specified aren't available. Besides, the writes don't work the same way as in mongo, when a standby node is in sync it isn't just in sync for that particular write, it is completely in sync, so their following argument about not being able to specify durability/recency of data on read is redundant. If you contact the master or synchronous replica, you will always get the most recent state. If you don't mind slight delay you should query asynchronous replicas (in fact you should prefer them whenever you can, since those are cheap to add)

takeda 6y ago

Well, the built-in ways is the right way to do it. But given that PostgreSQL is quite conservative about it, it will be hard to find issues there (the replicas are read only, so at worst it will be just a replication delay, unless you use synchronous replication, which will remove the replication delay at the cost of slower performance). All the tooling that provides extra distributed functionality not present in postgres (auto failover, multi master replication, sharding etc) will surely have issues, but then you aren't testing the PostgreSQL itself, but the tooling, so to be fair, you the article should evaluate these tools, and any shortcomings shouldn't go to PostgreSQL (unless it really is a PostgreSQL issue).

pas 6y ago
This k8s operator advertises production-grade auto-failover auto-healing replicas - basically what MongoDB - but built on postgres: https://github.com/CrunchyData/postgres-operator https://github.com/CrunchyData/postgres-operator And looking at this table, basically the future seems to be WAL shipping anyway ( https://www.postgresql.org/docs/13/different-replication-solutions.html https://www.postgresql.org/docs/13/different-replication-sol... )

pcl 6y ago

I think that it'd be super-valuable to do an analysis of an RDS Postgres deployment. Amazon is doing some dark magic with RDS that sits at this really interesting "distributed, but not that distributed" inflection point, which impacts the basic assumptions of lots of distributed database design. I believe RDS Postgres is probably the right answer for lots of applications, especially for those that already depend on AWS for baseline availability. I'd love to see if that holds up against a rigorous analysis.

aeyes 6y ago
Are you talking about Aurora? Because in RDS the replication is just what you get out of the box.
aphyr 6y ago
I'd like this too, but I'm not sure how to do fault injection against an Amazon-controlled service.
- ashtonkem 6y ago
  You’d probably have to work directly with AWS on that one, either to get a custom harness in AWS, or to find out how they configure RDS replication.
- elesbao 6y ago
  the setup would prob ably be pgsql primary, aurora secondaries on diff zones and something changing cross-zone or cross-region vpc setting to try to break replication ? never tried that but was hurt by rds pure pgsql cross region replication in a network outage situation.

didip 6y ago

It is true that only recently PG has a standard way of replicating. But even then, PG is not a distributed database by default. However, if I may suggest, Stolon, Patroni, Postgres XL or Citus Data might be interesting to you.

grogers 6y ago

I know you've done galera in the past, but MySQL group replication would be a good one since it IS the defacto HA solution, unlike with Postgres.