The read-write endpoint connects to replica database

[Eric-zch]

Environment

• Platform: OpenShift
• Platform Version: 4.16
• PGO Image Tag: ubi8-5.2.0
• Postgres Version: ubi8-14.6
• Pgbouncer Version: ubi8-1.21

Hi There, I have a Crunchy PostgresCluster ods setup with below configuration:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: ods
spec:
  image: crunchy-postgres:ubi8-14.6-4
  postgresVersion: 14
  openshift: true
  ......
  proxy:
    pgBouncer:
      image: crunchy-pgbouncer:ubi8-1.21-0
      replicas: 2
      minAvailable: 1
      customTLSSecret:
        name: ods-pgbouncer-tls
      service:
        type: NodePort
      config:
        global:
          server_tls_sslmode: "verify-ca"
          client_tls_sslmode: require
          ignore_startup_parameters: extra_float_digits,search_path
          auth_type: md5
          client_tls_ciphers: normal
          pool_mode: session
          server_idle_timeout: "3600"
          server_lifetime: "3600"
          max_client_conn: "1000"
          default_pool_size: "400"
        databases:
          # Added separate endpoints for read-write instances
          ods-read: "host=ods-replicas port=5432 dbname=ODS"
          ods-read-write: "host=ods-primary port=5432 dbname=ODS"

Our application connects to the ods-read-write endpoint. Most of the time, it functions without any issues.
The Issue: The application occasionally connects to the replica database instead of the primary (master) database. Restarting the PostgresCluster resolved the issue as a workaround, but the root cause has not yet been identified.
Further investigation is needed for the two services: ods-primary and ods-replicas.

➜  ~ oc get svc ods-primary -o yaml
apiVersion: v1
kind: Service
metadata:
  creationTimestamp: "2025-02-24T02:01:32Z"
  labels:
    postgres-operator.crunchydata.com/cluster: ods
    postgres-operator.crunchydata.com/role: primary
  name: ods-primary
  namespace: int-ods
  ownerReferences:
  - apiVersion: postgres-operator.crunchydata.com/v1beta1
    blockOwnerDeletion: true
    controller: true
    kind: PostgresCluster
    name: ods
    uid: a61f5019-9276-4b37-8c46-555f2f7a90fc
  resourceVersion: "554352387"
  uid: 224dd437-4544-413f-b2d5-2b4e4646850d
spec:
  clusterIP: None
  clusterIPs:
  - None
  internalTrafficPolicy: Cluster
  ipFamilies:
  - IPv4
  - IPv6
  ipFamilyPolicy: RequireDualStack
  ports:
  - name: postgres
    port: 5432
    protocol: TCP
    targetPort: postgres
  sessionAffinity: None
  type: ClusterIP
status:
  loadBalancer: {}
➜  ~ 
➜  ~ oc get svc ods-replicas -o yaml
apiVersion: v1
kind: Service
metadata:
  creationTimestamp: "2025-02-24T02:01:32Z"
  labels:
    postgres-operator.crunchydata.com/cluster: ods
    postgres-operator.crunchydata.com/role: replica
  name: ods-replicas
  namespace: int-ods
  ownerReferences:
  - apiVersion: postgres-operator.crunchydata.com/v1beta1
    blockOwnerDeletion: true
    controller: true
    kind: PostgresCluster
    name: ods
    uid: a61f5019-9276-4b37-8c46-555f2f7a90fc
  resourceVersion: "554352398"
  uid: e9e0f8ec-bd4a-4feb-9f42-b2e5ee053284
spec:
  clusterIP: 172.30.208.68
  clusterIPs:
  - 172.30.208.68
  internalTrafficPolicy: Cluster
  ipFamilies:
  - IPv4
  ipFamilyPolicy: SingleStack
  ports:
  - name: postgres
    port: 5432
    protocol: TCP
    targetPort: postgres
  selector:
    postgres-operator.crunchydata.com/cluster: ods
    postgres-operator.crunchydata.com/role: replica
  sessionAffinity: None
  type: ClusterIP
status:
  loadBalancer: {}
➜  ~ 

There is selector for ods-replicas, but no selector for ods-primary.
Is this issue related to no selector defined for ods-primary?

[andrewlecuyer]

@Eric-zch do you typically see this behavior after a failover?

[Eric-zch]

Hi @andrewlecuyer
We upgraded OpenShift from version 4.14 to 4.16 approximately two weeks ago, and everything appeared to be functioning well after the upgrade. However, on March 5, 2025, this issue occurred. Upon reviewing the PostgreSQL logs, we discovered that some connections were being routed to the replica pod. To resolve the issue, we restarted the PostgresCluster.

Since this issue did not occur immediately following a failover, we are uncertain if it might happen again.

[Eric-zch]

Another question: Why doesn't Crunchy add a selector to the primary service?