Page MenuHomePhabricator

Decide on which postgresql operator to use
Closed, ResolvedPublic

Description

We have decided to embark on a project to deploy a postgresql operator to the dse-k8s cluster, in support of migrating Airflow to Kubernetes.

There are several possible PostgreSQL operators available, which may meet our needs.

This ticket is about selecting one operator to implement. There may be architectural differences ond/or nuances which make one or other of them a better choice for this use case.

Some of the most widely known and adopted open-source postgresql operators at present are:

Details

Other Assignee
BTullis

Event Timeline

These are raw notes I'm taking while I'm reading through the various documentation pages.

Cloudnative PG

I think it its a non-starter as our current version of Kubernetes (1.23) is not supported.

Zalando

  • Comes with HA support out of the box, using Patroni.
  • Comes with a web UI
  • it seems that PostgreSQL metrics export as Prometheus format is left as an exercise to the administrator
  • A single operator seem to only be able to watch a single k8s namespace, meaning that if we want to deploy PostgreSQL clusters in several namespaces (e.g. to avoid a fat-fingering deletion of all clusters), we'd need to deploy several instances of the operator, meaning several web UIs, etc.
NOTE: that does not seem to be true actually, as I read To make the operator listen to all namespaces, explicitly set the field/env var to "*", so I am going to assume that there's a way to watch several namespaces.
  • there's a way to prevent fat-fingering the deletion of the operator (and subsequently the PG clusters themselves) using deletion protection.
  • password rotation is enabled by default, should we need it
  • load balancing is done at the K8s service level. I don't see a native pgbouncer integration. As I'm not sure we're planning to require the clients to use TLS, issuing a new non-TLS connection to the master for every new client connection shouldn't be too expensive anyway. That might change if we plan to require clients to use TLS.
  • WAL-E/G is used to push the base backups. We'd need to make sure we have somewhere to push it to (HDFS? Ceph with the S3 API?)
NOTE: actually, maybe just a Persistent Volume backed by Ceph?
  • The documentation itself seems to be quite good, and many common operations seem to be documented
  • in-place PG upgrade seem to be supported
  • Seems to be running PG 16 by default

Crunchy

  • The first thing that pops to mind is that this Operator installs a specific distribution of PostgreSQL: Crunchy Postgres, Crunchy Data's open source distribution of Postgres, along with leading Postgres tools and extensions such as pgbackrest, Patroni, pgaudit, PostGIS, and more . To me, this is a red flag, as we have a strong dependency on an enteprise-maintained product.
NOTE: the same thing could be said of Zalando though, as their installation depends on Spilo, their own packaging of Postgres (vanilla though) and Patroni
  • Kubernetes 1.23 is supported
  • The fact that all connection information can be pulled out of a Secret is pretty nice. However, how does it work if the application does not belong to the same namespace the secret lives in? The provided example deploys an application in the postgres-operator namespace, which seems to validate the fact that this method won't be usable.
  • pgbouncer seems to be available by default
  • TLS between pgbouncer and postgres seems to be the default
  • pgBackRest is used for WAL and PIT backups, with retention policies, as well as recoveries
  • patroni is also made available by default, and is used to performed failovers
  • They seem to roll out their own monitoring solution, by deploying alertmanager/prometheus/etc to kubernetes: https://access.crunchydata.com/documentation/postgres-operator/latest/tutorials/day-two/monitoring I'm not sure whether they can easily integrate with an already existing setup.

Percona

  • They provide this neat comparison table between operators
  • Provides pgbouncer, pgbackrest, Patroni OotB
  • Kubernetes 1.23 seems to be unsupported sadly
  • Telemetey is sent to a Monitoring SaaS.

StackGres

  • Kubernetes 1.23 is supported
  • Provides pgBouncer for pooling
  • Provides Wal-g for backup (that we've successfully used at Datadog for 100s-TB scale PG instances)
  • Envoy seems to be used to manage routing connections to the primary _and_ parsing PG metrics and exposing them in native prometheus format
  • We can also use https://github.com/prometheus-community/postgres_exporter to scrape metrics and export them to prometheus format
  • a management UI is provided
  • Patroni is provided for HA management, failovers, etc
  • Backups can be stored in a volume, but this seems to require a bit more setup
  • PG extensions are supported
  • 2 flavors of postgres can be used:
  • there;'s builtin way to manage migrations

Conclusion

I think that StackGres is the safest choice. It provides us with the same kind of features as the rest:

  • pooling
  • backups and resores
  • management via Kube CRDs
  • support for recent PG versions

On top of that, some features also differentiate it from other operators:

  • it supports our Kubernetes version
  • builtin prometheus metrics exposition
  • management web UI
  • SQL migrations management support
  • it runs vanilla PostgreSQL (the last 2 most recent versions, in the free, open-source version)

All in all, I think this operator avoids vendor lock-in, while providing many interesting features.

@brouberol - Thanks ever so much for that work and comparison.

Do you think that it might be worth considering the functionality of CloudnativePG, even though version 1.19.x was the last version officially to support Kubernetes 1.23.
I know that version 1.19.x is technically EOL, but then again so is Kubernetes 1.23 itself.

The latest version (1.23.x) of CloudnativePG says that Kubernetes 1.23 is 'tested but not supported' but that doesn't necessarily mean 'tested and found to be incompatible'. Even if we found that we had to revert to version 1.19.x of the operator, that might not be too bad.

Also, it's worth bearing in mind that we are currently planning the upgrade of the k8s clusters to a later version anyway (see: T341984#9595007) and that might well be 1.27 or 1.29.

I'm not saying that I disagree with your conclusion to recommend StackGres, but excluding CNPG based solely on its release cycle might be missing some potential value.

@BTullis Good point. Let me re-read the CNPG documentation with this in mind.

Cloud Native PG


I think that if we can get it to run on our k8s cluster, then it is probably the safest choice, due to the CNCF stewardship. The fact that it exposes prometheus metrics, as well as embarks a minimum of external tooling (no Patroni) might make it a bit less complicated to run?

Conclusion (bis)

All in all, I'd say I'd recommend trying CNPG and it it does not work on our k8s cluster, then I'd say that StackGres is our second safest choice.

Regarding your comment on Zalando:

NOTE: that does not seem to be true actually, as I read To make the operator listen to all namespaces, explicitly set the field/env var to "*", so I am going to assume that there's a way to watch several namespaces.

I was looking into this too, but from what I can see I don't believe that there is a way at the moment. The operator either watches a single namespace or all namespaces.
See this open issue: https://github.com/zalando/postgres-operator/issues/1839

I too would consider this missing feature a pretty bad negative point for Zalando.

Agreed, this isn't great, and would complicate out usage in a pretty significant way.

BTullis updated Other Assignee, added: BTullis; removed: brouberol.

Conclusion (bis)

All in all, I'd say I'd recommend trying CNPG and it it does not work on our k8s cluster, then I'd say that StackGres is our second safest choice.

I'm in complete agreement with you.
For the record, when I wrote the first draft of this document, I had Zalando in mind, which is why the diagrams ended up with Patroni mentioned in them.

But on reflection and further research I thought that CNPG was probably a better fit, if we could make it work.

My evaluation of StackGres was probably less complete than yours, but your assessment and recommendation of StackGres as a fallback option makes a lot of sense.

Alright then, it seems we have a way forward!