7 ms·

A fully automated HA solution is yet missing for PostgreSQL.

by tpetry 10y ago

gshulegaard 10y ago
https://www.postgresql.org/docs/9.5/static/high-availability.html https://www.postgresql.org/docs/9.5/static/high-availability... https://www.citusdata.com/solutions/infrastructure/high-availability-postgresql https://www.citusdata.com/solutions/infrastructure/high-avai... Edit: And before someone mentions "Citus" being "third-party" or "closed" or some such: https://www.citusdata.com/blog/2016/03/24/citus-unforks-goes-open-source/ https://www.citusdata.com/blog/2016/03/24/citus-unforks-goes...
- Zikes 10y ago
  There's also CockroachDB, which is wire-compatible with Postgres. Of course, there are also some performance trade-offs to consider, and it is currently missing many SQL and data type features available in Postgres and others.
  - the_duke 10y ago
    It's a completely different database, it just happens to support the postgres binary protocol.
    - Zikes 10y ago
      Yes, it is. However when I went looking for a "HA Postgres" solution I found it to be a viable alternative for my use case.
      - dianasaur323 10y ago
        CockroachDB employee here - Yes, CockroachDB is a completely different database from PostgreSQL. CockroachDB supports HA from the bottom up with data broken up into ranges that are consistently replicated using Raft. As mentioned earlier in the thread, it uses the PostgreSQL wire protocol and supports a good amount of SQL (but not all since the dialect is quite large if you include extensions). If you are looking for HA with SQL compatibility, it would be worth taking a look at CockroachDB.
    - anarazel 10y ago
      s/binary/wire/
- threeseed 10y ago
  Citus by definition is third party. And whilst it has been recently open sourced it is always a risk to depend on a project that is controlled by one vendor. Especially when you are looking for enterprise support.
  - JBReefer 10y ago
    I disagree - I think the one vendor open source thing works well, consider Node, Scala, VS Code, etc.
    - threeseed 10y ago
      Completely different situations. Node and Scala for example are open source projects first then a company comes in and hires engineers and provides support. That model tends to work fine because you still have a rich, diverse community. It's different when it's a company that open sources a project. It can sometimes be difficult then for that project to have that same diversity of views.
- tpetry 10y ago
  CitusDB (not Citus Cloud) is not fully automated: You can have only one master node, if this node fails you have to manually elect a new master node. So it really does not gain anything compared to stock postgresql for HA.
philips 10y ago
People are working on things on-top of Kubernetes, such as https://github.com/CrunchyData/postgres-operator https://github.com/CrunchyData/postgres-operator
- cookiecaper 10y ago
  I hate this. Kubernetes is designed specifically for stateless applications; everything about running stateful applications on it now is experimental. Running k8s/Docker in prod is hard enough for actually stateless apps, running a database in it is an utterly horrifying idea. Please limit the extent of your lemming-ness to at least not attempting to containerize PostgreSQL or other old-school database systems. They could not be less suited to this type of application.
  - elvinyung 10y ago
    > Kubernetes is designed specifically for stateless applications Is that actually true? The Borg paper[1] mentions that Google uses it to run stateful things like Bigtable and GFS nodes, and as far as I can tell, Kubernetes has the exact same goals as Borg.[2] I would love to see a source for your claim. [1] https://pdos.csail.mit.edu/6.824/papers/borg.pdf https://pdos.csail.mit.edu/6.824/papers/borg.pdf [2] http://queue.acm.org/detail.cfm?id=2898444 http://queue.acm.org/detail.cfm?id=2898444
    - threeseed 10y ago
      "Omega, an offspring of Borg stored the state of the cluster in a centralized Paxos-based transaction-oriented store that was accessed by the different parts of the cluster control plane (such as schedulers), using optimistic concurrency control to handle the occasional conflicts" Seems like Google never stored state within the containers.
      - elvinyung 10y ago
        I think that's a description of how the cluster metadata is managed, and not a description of the actual cluster tasks.
elvinyung 10y ago
Quorum commits are coming.[1] This provides a similar feature to the "Group Replication" functionality for MySQL outlined in the post. [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=3901fd70cc7ccacef1b0549a6835bb7d8dcaae43 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...
- morgo 10y ago
  I'm not sure it is quite the same, since it is WAL based (i.e. can you write to any node?) There is semi-sync in MySQL as well, which ensures N replicas have received a write.
  - p4lindromica 10y ago
    That's not what semi-sync does. Semi-sync commits the write to the master and then blocks on returning a result to the client until N replicas have received a write OR a timeout is reached. There is no ability to roll back the write on the master once it is committed.
    - morgo 10y ago
      This was the case for 5.5 and 5.6. MySQL 5.7+ will default to wait for the ack before committing locally: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_rpl_semi_sync_master_wait_point https://dev.mysql.com/doc/refman/5.7/en/server-system-variab...
      - p4lindromica 10y ago
        Does AFTER_SYNC mean MySQL 5.7+ can have unacknowledged writes appear on the slaves after a master crash and failover?
        morgo 10y ago
        I believe the answer is "yes" but this problem is not unique to semi-sync. Take a single server example: You could ask the database to "COMMIT"; it receives the command and processes it, but does not manage to send you notification that it has managed. There is a further failure state: The master could be behind when it crashed. So you need to look at it's gtid executed set or re-image it before plugging it back in. (Not a distributed systems guy; I work on the non HA+replication parts of the server)

A fully automated HA solution is yet missing for PostgreSQL.

by tpetry 10y ago

A fully automated HA solution is yet missing for PostgreSQL.

https://www.postgresql.org/docs/9.5/static/high-availability.html https://www.postgresql.org/docs/9.5/static/high-availability... https://www.citusdata.com/solutions/infrastructure/high-availability-postgresql https://www.citusdata.com/solutions/infrastructure/high-avai... Edit: And before someone mentions "Citus" being "third-party" or "closed" or some such: https://www.citusdata.com/blog/2016/03/24/citus-unforks-goes-open-source/ https://www.citusdata.com/blog/2016/03/24/citus-unforks-goes...

Zikes 10y ago
There's also CockroachDB, which is wire-compatible with Postgres. Of course, there are also some performance trade-offs to consider, and it is currently missing many SQL and data type features available in Postgres and others.
- the_duke 10y ago
  It's a completely different database, it just happens to support the postgres binary protocol.
  - Zikes 10y ago
    Yes, it is. However when I went looking for a "HA Postgres" solution I found it to be a viable alternative for my use case.
    - dianasaur323 10y ago
      CockroachDB employee here - Yes, CockroachDB is a completely different database from PostgreSQL. CockroachDB supports HA from the bottom up with data broken up into ranges that are consistently replicated using Raft. As mentioned earlier in the thread, it uses the PostgreSQL wire protocol and supports a good amount of SQL (but not all since the dialect is quite large if you include extensions). If you are looking for HA with SQL compatibility, it would be worth taking a look at CockroachDB.
  - anarazel 10y ago
    s/binary/wire/
threeseed 10y ago
Citus by definition is third party. And whilst it has been recently open sourced it is always a risk to depend on a project that is controlled by one vendor. Especially when you are looking for enterprise support.
- JBReefer 10y ago
  I disagree - I think the one vendor open source thing works well, consider Node, Scala, VS Code, etc.
  - threeseed 10y ago
    Completely different situations. Node and Scala for example are open source projects first then a company comes in and hires engineers and provides support. That model tends to work fine because you still have a rich, diverse community. It's different when it's a company that open sources a project. It can sometimes be difficult then for that project to have that same diversity of views.
tpetry 10y ago
CitusDB (not Citus Cloud) is not fully automated: You can have only one master node, if this node fails you have to manually elect a new master node. So it really does not gain anything compared to stock postgresql for HA.

philips 10y ago

People are working on things on-top of Kubernetes, such as https://github.com/CrunchyData/postgres-operator https://github.com/CrunchyData/postgres-operator

cookiecaper 10y ago
I hate this. Kubernetes is designed specifically for stateless applications; everything about running stateful applications on it now is experimental. Running k8s/Docker in prod is hard enough for actually stateless apps, running a database in it is an utterly horrifying idea. Please limit the extent of your lemming-ness to at least not attempting to containerize PostgreSQL or other old-school database systems. They could not be less suited to this type of application.
- elvinyung 10y ago
  > Kubernetes is designed specifically for stateless applications Is that actually true? The Borg paper[1] mentions that Google uses it to run stateful things like Bigtable and GFS nodes, and as far as I can tell, Kubernetes has the exact same goals as Borg.[2] I would love to see a source for your claim. [1] https://pdos.csail.mit.edu/6.824/papers/borg.pdf https://pdos.csail.mit.edu/6.824/papers/borg.pdf [2] http://queue.acm.org/detail.cfm?id=2898444 http://queue.acm.org/detail.cfm?id=2898444
  - threeseed 10y ago
    "Omega, an offspring of Borg stored the state of the cluster in a centralized Paxos-based transaction-oriented store that was accessed by the different parts of the cluster control plane (such as schedulers), using optimistic concurrency control to handle the occasional conflicts" Seems like Google never stored state within the containers.
    - elvinyung 10y ago
      I think that's a description of how the cluster metadata is managed, and not a description of the actual cluster tasks.

elvinyung 10y ago

Quorum commits are coming.[1] This provides a similar feature to the "Group Replication" functionality for MySQL outlined in the post. [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=3901fd70cc7ccacef1b0549a6835bb7d8dcaae43 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

morgo 10y ago
I'm not sure it is quite the same, since it is WAL based (i.e. can you write to any node?) There is semi-sync in MySQL as well, which ensures N replicas have received a write.
- p4lindromica 10y ago
  That's not what semi-sync does. Semi-sync commits the write to the master and then blocks on returning a result to the client until N replicas have received a write OR a timeout is reached. There is no ability to roll back the write on the master once it is committed.
  - morgo 10y ago
    This was the case for 5.5 and 5.6. MySQL 5.7+ will default to wait for the ack before committing locally: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_rpl_semi_sync_master_wait_point https://dev.mysql.com/doc/refman/5.7/en/server-system-variab...
    - p4lindromica 10y ago
      Does AFTER_SYNC mean MySQL 5.7+ can have unacknowledged writes appear on the slaves after a master crash and failover?
      - morgo 10y ago
        I believe the answer is "yes" but this problem is not unique to semi-sync. Take a single server example: You could ask the database to "COMMIT"; it receives the command and processes it, but does not manage to send you notification that it has managed. There is a further failure state: The master could be behind when it crashed. So you need to look at it's gtid executed set or re-image it before plugging it back in. (Not a distributed systems guy; I work on the non HA+replication parts of the server)