5 ms·

Is this multi-master replication? It will be interesting if it can be accepted into Postgres proper.

stephenr 1y ago
Sounds like "yes, with an if" where the "if" is "if you don't really care about data consistency". "Last write wins" sounds like a recipe for disaster IMO. This is still one of those things that keeps people on MySQL - there are not one, but two open-source solutions available that provide synchronous cluster replication, allowing for "safe" writes against multiple primaries.
- nyrikki 1y ago
  It's all tradeoffs, with MySQL multi-master and multi-source models having their own issues and pg also has other options with their own tradoffs. ACID+distributed== tradoffs that will always keep this a horses for courses problem.
- wfn 1y ago
  Out of curiosity, what conflict resolution options exist in mysql and/or mysql cluster (never checked / exp. in PG)? Because you'll always have to address conflicts of course - we come to CAP / PACELC. Hm [1][2] - looks like they support more strategies (possibly) but I mean none of them are somehow magical, and timestamp comparison based methods comprise the better part of offered strategy set (looks like?) - and "latest timestamp wins" at least used to be the default (did not read thoroughly mind you, was just curious)? But I could be totally wrong - (1) curious if someone could link to things / explain, and (2) fyi ('stephenr) last write wins based on timestamp is a thing im mysql world as well (though again maybe set of options / different conflict resolution methods available is larger in mysql?) [1]: https://dev.mysql.com/doc/refman/8.4/en/mysql-cluster-replication-conflict-resolution.html https://dev.mysql.com/doc/refman/8.4/en/mysql-cluster-replic... [2]: https://dev.mysql.com/blog-archive/enhanced-conflict-resolution-with-mysql-cluster-active-active-replication/ https://dev.mysql.com/blog-archive/enhanced-conflict-resolut... (nice writeup, maybe outdated idk?)
  - stephenr 1y ago
    For reference those two pages are both about NDB cluster. The two "options" I was referring to are MySQL group replication and the Galera replication plugin for MySQL. Both provide synchronous replication, so the write either succeeds to a majority of the cluster or is rejected.
    - wfn 1y ago
      Understood, thanks! I wasn't even sure where to look - thank you
- agnivade 1y ago
  Just for my own understanding, could you clarify why you think "last write wins" is a recipe for disaster?
  - stephenr 1y ago
    Because two simultaneous writes mean some change is just lost, without feedback to the writer.
dehrmann 1y ago
Did Postgres ever get a built-in, blessed replication offering? It's been a while since I set it up, but I remember this was always a big missing feature compared to Mysql.
- philipwhiuk 1y ago
  Streaming and logical replication is built in: https://www.postgresql.org/docs/current/runtime-config-replication.html https://www.postgresql.org/docs/current/runtime-config-repli...
- wrs 1y ago
  The basic replication mechanisms have been built-in for quite a while. What’s not there is cluster management (replica launching, leader election, load balancing, that sort of thing) that makes it practical in a nontrivial situation. There are several 3rd party solutions to that. [0] Same situation as, e.g., backups. You can just use pg_dump, but to be serious you need a 3rd party solution that does log shipping and so on. [0] https://www.postgresql.org/download/products/3-clusteringreplication/ https://www.postgresql.org/download/products/3-clusteringrep...

Sounds like "yes, with an if" where the "if" is "if you don't really care about data consistency". "Last write wins" sounds like a recipe for disaster IMO. This is still one of those things that keeps people on MySQL - there are not one, but two open-source solutions available that provide synchronous cluster replication, allowing for "safe" writes against multiple primaries.

nyrikki 1y ago
It's all tradeoffs, with MySQL multi-master and multi-source models having their own issues and pg also has other options with their own tradoffs. ACID+distributed== tradoffs that will always keep this a horses for courses problem.
wfn 1y ago
Out of curiosity, what conflict resolution options exist in mysql and/or mysql cluster (never checked / exp. in PG)? Because you'll always have to address conflicts of course - we come to CAP / PACELC. Hm [1][2] - looks like they support more strategies (possibly) but I mean none of them are somehow magical, and timestamp comparison based methods comprise the better part of offered strategy set (looks like?) - and "latest timestamp wins" at least used to be the default (did not read thoroughly mind you, was just curious)? But I could be totally wrong - (1) curious if someone could link to things / explain, and (2) fyi ('stephenr) last write wins based on timestamp is a thing im mysql world as well (though again maybe set of options / different conflict resolution methods available is larger in mysql?) [1]: https://dev.mysql.com/doc/refman/8.4/en/mysql-cluster-replication-conflict-resolution.html https://dev.mysql.com/doc/refman/8.4/en/mysql-cluster-replic... [2]: https://dev.mysql.com/blog-archive/enhanced-conflict-resolution-with-mysql-cluster-active-active-replication/ https://dev.mysql.com/blog-archive/enhanced-conflict-resolut... (nice writeup, maybe outdated idk?)
- stephenr 1y ago
  For reference those two pages are both about NDB cluster. The two "options" I was referring to are MySQL group replication and the Galera replication plugin for MySQL. Both provide synchronous replication, so the write either succeeds to a majority of the cluster or is rejected.
  - wfn 1y ago
    Understood, thanks! I wasn't even sure where to look - thank you
agnivade 1y ago
Just for my own understanding, could you clarify why you think "last write wins" is a recipe for disaster?
- stephenr 1y ago
  Because two simultaneous writes mean some change is just lost, without feedback to the writer.

dehrmann 1y ago

Did Postgres ever get a built-in, blessed replication offering? It's been a while since I set it up, but I remember this was always a big missing feature compared to Mysql.

philipwhiuk 1y ago
Streaming and logical replication is built in: https://www.postgresql.org/docs/current/runtime-config-replication.html https://www.postgresql.org/docs/current/runtime-config-repli...
wrs 1y ago
The basic replication mechanisms have been built-in for quite a while. What’s not there is cluster management (replica launching, leader election, load balancing, that sort of thing) that makes it practical in a nontrivial situation. There are several 3rd party solutions to that. [0] Same situation as, e.g., backups. You can just use pg_dump, but to be serious you need a 3rd party solution that does log shipping and so on. [0] https://www.postgresql.org/download/products/3-clusteringreplication/ https://www.postgresql.org/download/products/3-clusteringrep...