5 ms·

When it comes to replication & automatic failover in PG what is the 2021 gold standard setup?

by beck5 6y ago

__s 6y ago
pg_auto_failover is good https://www.citusdata.com/blog/2019/05/30/introducing-pg-auto-failover https://www.citusdata.com/blog/2019/05/30/introducing-pg-aut... Disclosure: I work for Citus
ksec 6y ago
In 2016 I was expecting some sane defaults like MySQL would have arrived by 2018 or 2019. Looks like 2021 isn't that much different to 2016. There are work being done, but doesn't seems to be anywhere close to the level of MySQL.
- dijit 6y ago
  wow, are you kidding? MySQL replication is possibly the worst I've ever seen. There is almost no consideration for the target being up to date, I have personally experienced missed inserts, replication lags, and the replica being set to read/write. PGs integrated replication is far superior, it even has support for bootstrapping a new replica node without rsync and a command to `promote` the replica to write master.
  - claytonjy 6y ago
    What about Percona? I haven't used it, but a common sentiment on HN has been that Postgres doesn't have something quite on par with it. Citus and, more recently, Patroni, seem to be the dominant Postgres analogues; have they caught up? Where do they dominate?
  - evanelias 6y ago
    Replication issues in modern MySQL are caused by user error, not inherent bugs. For example if you're configuring replication manually, you must ensure that replicas are set to read_only. Clustering options introduced over the past few years do this automatically though. > PGs integrated replication is far superior, it even has support for bootstrapping a new replica node without rsync and a command to `promote` the replica to write master MySQL has this functionality too, e.g. https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html and https://dev.mysql.com/doc/mysql-shell/8.0/en/working-with-replicasets.html#set-replicaset-primary https://dev.mysql.com/doc/mysql-shell/8.0/en/working-with-re... To be clear, I am not claiming MySQL is superior to PG. However, I have found that many PG users are unfamiliar with the huge improvements introduced in MySQL over the past ~8 years.
hintbits 6y ago
Use built-in physical replication, it works and it's good. Patroni is widely used for automatic failover, at least where you don't want a possibility of a split brain.

When it comes to replication & automatic failover in PG what is the 2021 gold standard setup?

by beck5 6y ago

When it comes to replication & automatic failover in PG what is the 2021 gold standard setup?

__s 6y ago

pg_auto_failover is good https://www.citusdata.com/blog/2019/05/30/introducing-pg-auto-failover https://www.citusdata.com/blog/2019/05/30/introducing-pg-aut... Disclosure: I work for Citus

ksec 6y ago

In 2016 I was expecting some sane defaults like MySQL would have arrived by 2018 or 2019. Looks like 2021 isn't that much different to 2016. There are work being done, but doesn't seems to be anywhere close to the level of MySQL.

dijit 6y ago
wow, are you kidding? MySQL replication is possibly the worst I've ever seen. There is almost no consideration for the target being up to date, I have personally experienced missed inserts, replication lags, and the replica being set to read/write. PGs integrated replication is far superior, it even has support for bootstrapping a new replica node without rsync and a command to `promote` the replica to write master.
- claytonjy 6y ago
  What about Percona? I haven't used it, but a common sentiment on HN has been that Postgres doesn't have something quite on par with it. Citus and, more recently, Patroni, seem to be the dominant Postgres analogues; have they caught up? Where do they dominate?
- evanelias 6y ago
  Replication issues in modern MySQL are caused by user error, not inherent bugs. For example if you're configuring replication manually, you must ensure that replicas are set to read_only. Clustering options introduced over the past few years do this automatically though. > PGs integrated replication is far superior, it even has support for bootstrapping a new replica node without rsync and a command to `promote` the replica to write master MySQL has this functionality too, e.g. https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html and https://dev.mysql.com/doc/mysql-shell/8.0/en/working-with-replicasets.html#set-replicaset-primary https://dev.mysql.com/doc/mysql-shell/8.0/en/working-with-re... To be clear, I am not claiming MySQL is superior to PG. However, I have found that many PG users are unfamiliar with the huge improvements introduced in MySQL over the past ~8 years.

hintbits 6y ago

Use built-in physical replication, it works and it's good. Patroni is widely used for automatic failover, at least where you don't want a possibility of a split brain.