Kubegres replication can leave primary stuck in read-only mode

[hach-que]

Kubegres needs to run the following command to fix up primaries after they've been promoted at the Kubernetes level:

NAMESPACE=...
KUBEGRES_NAME=...
KUBEGRES_SECRET_NAME=...
kubectl -n $NAMESPACE exec -it $(kubectl -n $NAMESPACE get pod -l replicationRole=primary,app=$KUBEGRES_NAME -o=name) -- /bin/bash -c "PGPASSWORD=$(kubectl -n $NAMESPACE get secret $KUBEGRES_SECRET_NAME --template '{{.data.superUserPassword}}' | base64 --decode) /bin/psql -U postgres -c 'SELECT pg_promote();'"

[matheushent]

I also face this issue a lot. The solution I found was to create a CronJob to periodically verify if the database is in failover mode. It'd be good if there was a native solution in the operator. This could be added in the postStart script perhaps.

[zerepe]

I do face this issue a lot too. Looking forward for new version of kubegres, any fix or method to avoid/solve this problem.

[alex-arica]

Hi
Does this issue only happen with a specific version of Postgres?

[zerepe]

Well I'm using kubegres 4 installations ( 2 of postgres12 and 2 of postgres15) running on kubernetes v1.31.6.
It happens on both versions 12 and 15 of postgres.
I need to manually promote primary, otherwise it keeps as replica/read only.
Thing is, I started experiencing this issue since kubegres v1.17, not before.
Actually our production environment keeps running Kubegres 1.16 and postgres 12 because of this. And it keeps running fine still with old v.1.16. But our staging and test environments were upgraded experiencing this issue.

But the problem that its pushing us to an urgent solution is this issue:

#188

We Need to to upgrade from old v.1.16 to newer v.1.19 because deadline 18th march of 2025 (already due).
I'll be happy to help if you need further details.

[alex-arica]

This Friday, I will try reproducing this issue with the existing automatised tests by using the Postgres versions that you mentioned.
If I cannot reproduce it, I will contact you.

[hach-que]

Had this happen again after a power failure over the weekend. Here is an updated script that you can use to auto-promote all pods that are marked as primary across all namespaces, without having to fill in any environment variables ahead of time:

#!/bin/bash

NAMESPACES=$(kubectl get namespaces -o name)

for NAMESPACE in $NAMESPACES; do
  NAMESPACE=${NAMESPACE#*/}
  PRIMARY_POSTGRES_PODS=$(kubectl -n $NAMESPACE get pod -l replicationRole=primary -o=name)
  for PRIMARY_POSTGRES_POD in $PRIMARY_POSTGRES_PODS; do
    echo "Need to promote $PRIMARY_POSTGRES_POD in $NAMESPACE ..."
    SECRET_NAME=$(kubectl -n $NAMESPACE get $PRIMARY_POSTGRES_POD -o 'jsonpath={.spec.containers[0].env[?(@.name=="POSTGRES_PASSWORD")].valueFrom.secretKeyRef.name}')
    SECRET_KEY=$(kubectl -n $NAMESPACE get $PRIMARY_POSTGRES_POD -o 'jsonpath={.spec.containers[0].env[?(@.name=="POSTGRES_PASSWORD")].valueFrom.secretKeyRef.key}')
    POSTGRES_PASSWORD=$(kubectl -n $NAMESPACE get secret $SECRET_NAME --template '{{.data.superUserPassword}}' | base64 --decode)
    kubectl -n $NAMESPACE exec -it $PRIMARY_POSTGRES_POD -- /bin/bash -c "PGPASSWORD=$POSTGRES_PASSWORD /bin/psql -U postgres -c 'SELECT pg_promote();'"
  done
done

[hach-que]

Ran into this again last night. I'm now deploying the above script as a cronjob that runs every 2 minutes to help things recover after power failure.

The bug is almost certainly in https://github.com/reactive-tech/kubegres/blob/main/internal/controller/spec/enforcer/resources_count_spec/statefulset/failover/PrimaryToReplicaFailOver.go#L242. This function creates a new pod with replicationRole=primary and tells it to run /tmp/promote_replica_to_primary.sh as a post-start. However it does not wait for that script to finish running successfully before marking the new pod as a primary.

So I think there's a window where the following sequence of events is possible:

• Node 1 running the primary starts shutting down and evicting pods.
• Kubegres starts the failover to the pod running on node 2.
• Node 2 running the pod marked as primary (but for whom the promotion hasn't finished yet) starts shutting down and evicting pods.
  • Possibly Kubegres tries to schedule the creation of a new primary but it never happens? Since a power outage is near instant?
• Machines later turn back on.
• Node 2's pod is marked as primary, but it never actually got promoted at the Postgres level.

That function should be updated so that it does not modify the replicationRole to primary until it knows the database is promoted at the Postgres level.

[alex-arica]

@hach-que thank you for sharing these details. It is helpful that you identified the potential file where the logic can be fixed,

[zerepe]

Thanks @hach-que for the update and the script!
I'm using it to monitor status and fix when that happens on my Kubernetes clusters.
I'm also having this issue quite often (due some errors and restarts on kubegress-controller pod that I still unable to resolve...) so It's really helpfull in the meanwhile there is a new release with a fix... looking forward for any update on this regard or any upcoming new release.
Thank you all for your work on this project!

[hach-que]

Just as a heads up to folks - even with the script above I found there were still some scenarios where things could get out of whack and not recover.

I ended up moving to CloudNativePG instead. It was a bit of work to migrate the database and get all the application connections working again, but so far it's been much more reliable.