6 ms·

PostgreSQL High Availability Solutions – Part 1: Jepsen Test and Patroni

158 points by wb14123 2y ago

hamandcheese 2y ago
> If the PostgreSQL backend is cancelled while waiting to acknowledge replication (as a result of packet cancellation due to client timeout or backend failure) transaction changes become visible for other backends. Such changes are not yet replicated and may be lost in case of standby promotion. This sounds like the two generals problem, which has no solution. But I may be misunderstanding.
- wmf 2y ago
  "[The two generals problem] said that you can't achieve consensus (both safety and liveness at the same time), they did not say you have to sacrifice safety under message-losses or asynchrony conditions. So Paxos preserves safety under all conditions and achieves liveness when conditions improve outside the impossibility realm (less message losses, some timing assumptions start to hold)." http://muratbuffalo.blogspot.com/2010/10/paxos-taught.html http://muratbuffalo.blogspot.com/2010/10/paxos-taught.html
- gmokki 2y ago
  Wouldn't the simple fix be to delay backend (= one connection) closing until all pending replication it initiated is finished? That still leaves actual crashes, which would need to use the shared memory to store the list of pending replications before the recovery of transactions is finished.
karlmdavis 2y ago
What an absolutely delightful little project and write up.
hitpointdrew 2y ago
The best way I have found is to setup keepalived -> pgbouncer -> Postgres. Use repmgr to manage replication and barman for backups. Setup a VIP with keepalived with a small script that checks if the server is primary. You loose about 7-9 pings during a failover, have keepalived check about every 2 seconds and flip after 3 consecutive failures.
emmanueloga_ 2y ago
Is anyone here using YugabyteDB for high-availability Postgres? It seems like a compelling option: * Much closer to Postgres compatibility than CockroachDB. * A more permissive license. * Built-in connection manager [1], which should simplify deployment. * Supports both high availability and geo-distribution, which is useful if scaling globally becomes necessary later. That said, I don't see it mentioned around here often. I wonder if anyone here has tried it and can comment on it. -- 1: https://docs.yugabyte.com/preview/explore/going-beyond-sql/connection-mgr-ysql/ https://docs.yugabyte.com/preview/explore/going-beyond-sql/c...
- anonzzzies 2y ago
  It seems cockroach got all the love here indeed. We use Yugabyte and we are happy with it; for our usecases it is a lot faster and easier to work with than cockroach.
- mroche 2y ago
  One thing possibly holding some folks back is the version of Postgres it's held back to. Right now YDB has PostgreSQL 12 comparability. Support for PG15 is under active development, so hopefully it's a 2025 feature. I really wanted to be able to actually use YugabyteDB for once, but our developers reportedly are using PG15+ features. https://github.com/yugabyte/yugabyte-db/issues/9797 https://github.com/yugabyte/yugabyte-db/issues/9797
  - concerndc1tizen 2y ago
    YDB is another database, they unfortunately didn't protect that trademark. But they do call it yugabyteDB, YugabyteDB, YugaByte DB, yugabyte-db, and Yugabyte.
  - ddorian43 2y ago
    Right now it's based on PostgreSQL 11.2 with some patches pulled from newer releases. The upgrade will be to PG 15, and includes work to make further PG upgrades easier (think online upgrading a cluster on postgresql major versions).
- 2y ago
logifail 2y ago
I'm currently looking for similar info but for MySQL/MariaDB for an IoT side project ... any suggestions?
- raffraffraff 2y ago
  MySQL has had first class replication and failover built into it for years. You deploy a server, enable binlogs, clone the server (percona's xtranackup can live-clone a server locally or remotely) and start the new instance. Then you point the replica at the master using 'CHANGE MASTER TO <host> ...' and it starts pulling binlogs and applying them. On the master, you can repeat this last step, making the replica it's master. This means that you have multi-master replication. And it just works. There are some other tools you can use to detect failure of the current master and switch to another, that is up to you. There are also solutions like MySQL Cluster and Galera which provide a more cluster-like solution with synchronous replication. If you've got a suitable use case (low writes, high reads and no gigantic transactions) this can work extremely well. You bootstrap a cluster on node 1, and new members automatically take a copy of the cluster data when they join. You can have 3 or 5 mode clusters, and reads are distributed across all nodes since it's synchronous. Beware though, operating one of these things requires care. I've seen people suffer read downtime or full cluster outages by doing operations without understanding how they work under the hood. And if you're cluster is hard-down, you need to pick the node with "most recent transaction" to re-bootstrap the cluster or you can lose transactions.
- anonzzzies 2y ago
  We use proxysql (https://proxysql.com https://proxysql.com) which works very well. We have not seen any downtime for years. We wrote our own master promotion code a very long time ago; it has proven to be very robust.
- stephenr 2y ago
  Depends what sort of solution you want. There's regular single primary/n replica replication built in. There's no built in automatic failover. There's also Group replication built in. This can be either single primary/n replica with automatic election of a new primary during a failure, or it can be multi-primary. Then there's Galera, which is similar to the multi-primary mode of Group replication.
ahoka 2y ago
Is there any alternative to Jepsen that does not involve writing spaghetti Clojure code?
- refset 2y ago
  Writing Clojure without spaghetti isn't too hard, and definitely more practical than waiting for a Jepsen alternative to come along. The Jepsen author gave a great talk on all the performance engineering work that has gone into it, Jepsen is near enough an entire DBMS in its own right https://www.youtube.com/watch?v=EUdhyAdYfpA https://www.youtube.com/watch?v=EUdhyAdYfpA
  - ahoka 2y ago
    I have found that some projects are using Porcupine as an alternative. I just really couldn’t justify Clojure in my project and personally if I want Lisp, I know where to find it.
no1youknowz 2y ago
Haven't used it yet. But seeing as both Yugabyte and Cockroach being mentioned... pgEdge: https://github.com/pgedge/pgedge https://github.com/pgedge/pgedge Demo: https://youtu.be/Gpty7yNlwH4?t=1873 https://youtu.be/Gpty7yNlwH4?t=1873 Not affiliated with them. I recall that aspirationally pgEdge aims to be compatible with the latest pg version or one behind.
nh2 2y ago
Great that nobody can track, or easily contribute to, the underlying postgres bug, because postgres has no issue tracker. Keeps the number of reported bugs nice and low. The discussion of critical bugs that lose your data is left to HN and Twitter threads instead.
- williamstein 2y ago
  Wow, they do public issue tracking in an unusual Way, via a mailing list: https://www.postgresql.org/list/pgsql-bugs/ https://www.postgresql.org/list/pgsql-bugs/
  - hiatus 2y ago
    So, like Linux? https://docs.kernel.org/admin-guide/reporting-issues.html https://docs.kernel.org/admin-guide/reporting-issues.html
- zxexz 2y ago
  The issue mailing list issue tracker works quite well.
nh2 2y ago
It would be awesome if you could do the same test with Stolon!
- ahoka 2y ago
  The attached code actually mentions Stolon in some comments, so maybe that would be a future post from the author?
ptman 2y ago
Anyone familiar with autobase.tech?
arcastroe 2y ago
> require mandatory telemetry collection for free version Couldn't one simply define kubernetes network policies to limit egress from CockroachDB pods?

158 points by wb14123 2y ago

> If the PostgreSQL backend is cancelled while waiting to acknowledge replication (as a result of packet cancellation due to client timeout or backend failure) transaction changes become visible for other backends. Such changes are not yet replicated and may be lost in case of standby promotion. This sounds like the two generals problem, which has no solution. But I may be misunderstanding.

wmf 2y ago
"[The two generals problem] said that you can't achieve consensus (both safety and liveness at the same time), they did not say you have to sacrifice safety under message-losses or asynchrony conditions. So Paxos preserves safety under all conditions and achieves liveness when conditions improve outside the impossibility realm (less message losses, some timing assumptions start to hold)." http://muratbuffalo.blogspot.com/2010/10/paxos-taught.html http://muratbuffalo.blogspot.com/2010/10/paxos-taught.html
gmokki 2y ago
Wouldn't the simple fix be to delay backend (= one connection) closing until all pending replication it initiated is finished? That still leaves actual crashes, which would need to use the shared memory to store the list of pending replications before the recovery of transactions is finished.

karlmdavis 2y ago

What an absolutely delightful little project and write up.

hitpointdrew 2y ago

The best way I have found is to setup keepalived -> pgbouncer -> Postgres. Use repmgr to manage replication and barman for backups. Setup a VIP with keepalived with a small script that checks if the server is primary. You loose about 7-9 pings during a failover, have keepalived check about every 2 seconds and flip after 3 consecutive failures.

emmanueloga_ 2y ago

Is anyone here using YugabyteDB for high-availability Postgres? It seems like a compelling option: * Much closer to Postgres compatibility than CockroachDB. * A more permissive license. * Built-in connection manager [1], which should simplify deployment. * Supports both high availability and geo-distribution, which is useful if scaling globally becomes necessary later. That said, I don't see it mentioned around here often. I wonder if anyone here has tried it and can comment on it. -- 1: https://docs.yugabyte.com/preview/explore/going-beyond-sql/connection-mgr-ysql/ https://docs.yugabyte.com/preview/explore/going-beyond-sql/c...

anonzzzies 2y ago
It seems cockroach got all the love here indeed. We use Yugabyte and we are happy with it; for our usecases it is a lot faster and easier to work with than cockroach.
mroche 2y ago
One thing possibly holding some folks back is the version of Postgres it's held back to. Right now YDB has PostgreSQL 12 comparability. Support for PG15 is under active development, so hopefully it's a 2025 feature. I really wanted to be able to actually use YugabyteDB for once, but our developers reportedly are using PG15+ features. https://github.com/yugabyte/yugabyte-db/issues/9797 https://github.com/yugabyte/yugabyte-db/issues/9797
- concerndc1tizen 2y ago
  YDB is another database, they unfortunately didn't protect that trademark. But they do call it yugabyteDB, YugabyteDB, YugaByte DB, yugabyte-db, and Yugabyte.
- ddorian43 2y ago
  Right now it's based on PostgreSQL 11.2 with some patches pulled from newer releases. The upgrade will be to PG 15, and includes work to make further PG upgrades easier (think online upgrading a cluster on postgresql major versions).
2y ago

logifail 2y ago

I'm currently looking for similar info but for MySQL/MariaDB for an IoT side project ... any suggestions?

raffraffraff 2y ago
MySQL has had first class replication and failover built into it for years. You deploy a server, enable binlogs, clone the server (percona's xtranackup can live-clone a server locally or remotely) and start the new instance. Then you point the replica at the master using 'CHANGE MASTER TO <host> ...' and it starts pulling binlogs and applying them. On the master, you can repeat this last step, making the replica it's master. This means that you have multi-master replication. And it just works. There are some other tools you can use to detect failure of the current master and switch to another, that is up to you. There are also solutions like MySQL Cluster and Galera which provide a more cluster-like solution with synchronous replication. If you've got a suitable use case (low writes, high reads and no gigantic transactions) this can work extremely well. You bootstrap a cluster on node 1, and new members automatically take a copy of the cluster data when they join. You can have 3 or 5 mode clusters, and reads are distributed across all nodes since it's synchronous. Beware though, operating one of these things requires care. I've seen people suffer read downtime or full cluster outages by doing operations without understanding how they work under the hood. And if you're cluster is hard-down, you need to pick the node with "most recent transaction" to re-bootstrap the cluster or you can lose transactions.
anonzzzies 2y ago
We use proxysql (https://proxysql.com https://proxysql.com) which works very well. We have not seen any downtime for years. We wrote our own master promotion code a very long time ago; it has proven to be very robust.
stephenr 2y ago
Depends what sort of solution you want. There's regular single primary/n replica replication built in. There's no built in automatic failover. There's also Group replication built in. This can be either single primary/n replica with automatic election of a new primary during a failure, or it can be multi-primary. Then there's Galera, which is similar to the multi-primary mode of Group replication.

ahoka 2y ago

Is there any alternative to Jepsen that does not involve writing spaghetti Clojure code?

refset 2y ago
Writing Clojure without spaghetti isn't too hard, and definitely more practical than waiting for a Jepsen alternative to come along. The Jepsen author gave a great talk on all the performance engineering work that has gone into it, Jepsen is near enough an entire DBMS in its own right https://www.youtube.com/watch?v=EUdhyAdYfpA https://www.youtube.com/watch?v=EUdhyAdYfpA
- ahoka 2y ago
  I have found that some projects are using Porcupine as an alternative. I just really couldn’t justify Clojure in my project and personally if I want Lisp, I know where to find it.

no1youknowz 2y ago

Haven't used it yet. But seeing as both Yugabyte and Cockroach being mentioned... pgEdge: https://github.com/pgedge/pgedge https://github.com/pgedge/pgedge Demo: https://youtu.be/Gpty7yNlwH4?t=1873 https://youtu.be/Gpty7yNlwH4?t=1873 Not affiliated with them. I recall that aspirationally pgEdge aims to be compatible with the latest pg version or one behind.

nh2 2y ago

Great that nobody can track, or easily contribute to, the underlying postgres bug, because postgres has no issue tracker. Keeps the number of reported bugs nice and low. The discussion of critical bugs that lose your data is left to HN and Twitter threads instead.

williamstein 2y ago
Wow, they do public issue tracking in an unusual Way, via a mailing list: https://www.postgresql.org/list/pgsql-bugs/ https://www.postgresql.org/list/pgsql-bugs/
- hiatus 2y ago
  So, like Linux? https://docs.kernel.org/admin-guide/reporting-issues.html https://docs.kernel.org/admin-guide/reporting-issues.html
zxexz 2y ago
The issue mailing list issue tracker works quite well.

nh2 2y ago

It would be awesome if you could do the same test with Stolon!

ahoka 2y ago
The attached code actually mentions Stolon in some comments, so maybe that would be a future post from the author?

ptman 2y ago

Anyone familiar with autobase.tech?

arcastroe 2y ago

> require mandatory telemetry collection for free version Couldn't one simply define kubernetes network policies to limit egress from CockroachDB pods?