3 ms·

So what's the goto replication software to use for MySQL or Postgres these days?

ericyan 5y ago
If you use Oracle MySQL or Percona Server, I would recommend to try group replication[1], no third-party software required. MariaDB can use something similar called Galera Cluster. Percona also has its own version, called Percona XtraCluster (PXC). For more traditional MySQL/MariaDB replication typologies, Orchestrator[2] is a great tool. For postgres, I have heard a lot of good words about Patroni[3], although I never used it myself. [1]: https://dev.mysql.com/doc/refman/8.0/en/group-replication.html https://dev.mysql.com/doc/refman/8.0/en/group-replication.ht... [2]: https://github.com/openark/orchestrator https://github.com/openark/orchestrator [3]: https://github.com/zalando/patroni https://github.com/zalando/patroni
- hu3 5y ago
  Hi! I'm interested in scaling MariaDb with active-active replicas. They call it ring replication: https://mariadb.com/kb/en/replication-overview/ https://mariadb.com/kb/en/replication-overview/ I would appreciate any comments. Specially on the upsides and downsides.
  - ericyan 5y ago
    That's a footgun. For ring replication to work, it requires all replication sources (aka. "masters") to be online at all times. This never happens in real life, so it is just a matter of time that you get a nasty split-brain. Star replication tries to mitigate this by a crazy setup with replication filters that is painful to maintain and probably will break after some DDL statements. Moreover, neither of these can handle conflicts. If a row gets updated on different hosts at the same time, you will get broken replication and/or split-brain. To scale writes, sharding is usually the way to go and Vitess can be handy for that. By the way, MySQL group replication has multi-primary mode that is safer. Galera Cluster/PXC probably has something similar but I am not completely sure.
    - samlambert 5y ago
      This.
    - hu3 5y ago
      Thank you! I'll be looking into Galera then.
porsager 5y ago
I would definitely say pg-auto-failover[1]. It's the only one that doesn't have a ton of moving parts, and it helps you get a good base setup too at the same time. [1]: https://github.com/citusdata/pg_auto_failover https://github.com/citusdata/pg_auto_failover
- tiffanyh 5y ago
  I completely missed the news that Microsoft acquired Citrus back in 2019. I noticed in their MIT like license it referred to Microsoft and was confused until I learned they were acquired. https://github.com/citusdata/pg_auto_failover/blob/master/LICENSE https://github.com/citusdata/pg_auto_failover/blob/master/LI...
- ddorian43 5y ago
  You need 1 extra server for the "monitor" process. And it can't yet auto-failover the monitor node, no ?

If you use Oracle MySQL or Percona Server, I would recommend to try group replication[1], no third-party software required. MariaDB can use something similar called Galera Cluster. Percona also has its own version, called Percona XtraCluster (PXC). For more traditional MySQL/MariaDB replication typologies, Orchestrator[2] is a great tool. For postgres, I have heard a lot of good words about Patroni[3], although I never used it myself. [1]: https://dev.mysql.com/doc/refman/8.0/en/group-replication.html https://dev.mysql.com/doc/refman/8.0/en/group-replication.ht... [2]: https://github.com/openark/orchestrator https://github.com/openark/orchestrator [3]: https://github.com/zalando/patroni https://github.com/zalando/patroni

hu3 5y ago
Hi! I'm interested in scaling MariaDb with active-active replicas. They call it ring replication: https://mariadb.com/kb/en/replication-overview/ https://mariadb.com/kb/en/replication-overview/ I would appreciate any comments. Specially on the upsides and downsides.
- ericyan 5y ago
  That's a footgun. For ring replication to work, it requires all replication sources (aka. "masters") to be online at all times. This never happens in real life, so it is just a matter of time that you get a nasty split-brain. Star replication tries to mitigate this by a crazy setup with replication filters that is painful to maintain and probably will break after some DDL statements. Moreover, neither of these can handle conflicts. If a row gets updated on different hosts at the same time, you will get broken replication and/or split-brain. To scale writes, sharding is usually the way to go and Vitess can be handy for that. By the way, MySQL group replication has multi-primary mode that is safer. Galera Cluster/PXC probably has something similar but I am not completely sure.
  - samlambert 5y ago
    This.
  - hu3 5y ago
    Thank you! I'll be looking into Galera then.

porsager 5y ago

I would definitely say pg-auto-failover[1]. It's the only one that doesn't have a ton of moving parts, and it helps you get a good base setup too at the same time. [1]: https://github.com/citusdata/pg_auto_failover https://github.com/citusdata/pg_auto_failover

tiffanyh 5y ago
I completely missed the news that Microsoft acquired Citrus back in 2019. I noticed in their MIT like license it referred to Microsoft and was confused until I learned they were acquired. https://github.com/citusdata/pg_auto_failover/blob/master/LICENSE https://github.com/citusdata/pg_auto_failover/blob/master/LI...
ddorian43 5y ago
You need 1 extra server for the "monitor" process. And it can't yet auto-failover the monitor node, no ?