Evaluate operator alternatives

[Racer159]

Describe what should be investigated or refactored

There currently isn't much ADR history for the choice of the Zalando postgres operator. We should evaluate CloudNativePG as it is more directly supported by the PG project itself.

Links to any relevant code

https://github.com/cloudnative-pg/cloudnative-pg?tab=readme-ov-file

Additional context

If we do not decide to use CNPG we must document the why in an ADR.

[Racer159]

We should also evaluate https://github.com/CrunchyData/postgres-operator

[vanakema]

@Racer159 the CrunchyData operator has some weird license things that made us decide against using it. I forget what exactly, but I just remember it made it a no go for us

[vanakema]

We used CNPG at my last company, and we had a great experience with it. We referenced this HN thread to get people's comparison impressions as part of the decision.

The quotes from it that stood out to us:

Zalando: - Relies on WAL-E which is now obsolete - Documentation all over the place - Hacky setup that deviates from K8s standards (no easy way to set user through supplying secrets, for instance).
CrunchyData: - Incomplete documentation (Certain values settings are missing from their API specs) - Hacky user setup. - Doesn’t support running without backups enabled. (Obviously, you’d never want to run without backups setup on prod. But when testing, it’s nice to not need to have a perfect setup from the start. Without backups, it will let the database pods fill up their PVC’s with a WAL. Even when not doing any writes. It fills up at about 10GB/day.) - Backups seem to randomly fail.

[corang]

Can confirm the "Documentation all over the place" part, working with zalando postgres operator at any level higher than make work as database (backups) is an absolute nightmare

[ericwyles]

Another issue... it seems after pepr mutates the security context the postgres operator keeps detects and keeps reapplying it's own annotations. It creates an infinite loop between the operator and pepr. Related issue: zalando/postgres-operator#2223

 ✎ MUTATED   postgres/pg-cluster-0
   REPLACED:
             /metadata/annotations/uds-core.pepr.dev~1mutated="[\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\"]"

 ✎ MUTATED   acid/pg-cluster-0
   REPLACED:
             /metadata/annotations/uds-core.pepr.dev~1mutated="[\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\"]"

 ✎ MUTATED   acid/pg-cluster-1
   REPLACED:
             /metadata/annotations/uds-core.pepr.dev~1mutated="[\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\"]"

 ✎ MUTATED   acid/pg-cluster-0
   REPLACED:
             /metadata/annotations/uds-core.pepr.dev~1mutated="[\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\"]"

 ✎ MUTATED   acid/pg-cluster-1
   REPLACED:
             /metadata/annotations/uds-core.pepr.dev~1mutated="[\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\"]"

 ✎ MUTATED   acid/pg-cluster-0
   REPLACED:
             /metadata/annotations/uds-core.pepr.dev~1mutated="[\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\",\"require-non-root-user\",\"drop-all-capabilities\"]"