Skip to content

7. Connecting & pooling

Goal: connect applications to the database the right way (via Services, never pods), and put a PgBouncer pooler in front for efficiency.

The three Services the operator created

flowchart LR
    app["App: writes"] --> rw["pg-rw"] --> P["Primary"]
    appr["App: reads"] --> ro["pg-ro"] --> S1["Standby 1"]
    ro --> S2["Standby 2"]
    any["Any client"] --> r["pg-r"] --> P
    r --> S1
    r --> S2
Service Points to Use for
pg-rw the current primary all writes (and reads that must be current)
pg-ro standbys, load-balanced read-only queries you want to offload
pg-r any instance read-only where you don't care which

The crucial idea: connect to a Service, never to a pod IP. When the primary changes during a failover, the operator re-points pg-rw to the new primary; your app's connection string never changes.

In-cluster DNS names look like: pg-rw.production.svc.cluster.local:5432.

Connecting from inside the cluster

In an application Deployment, build the connection string from the Secret:

env:
  - name: DB_PASSWORD
    valueFrom:
      secretKeyRef:
        name: pg-app-credentials
        key: password
  - name: DATABASE_URL
    value: "postgresql://app_user:$(DB_PASSWORD)@pg-rw.production.svc.cluster.local:5432/app"
  - name: DATABASE_RO_URL
    value: "postgresql://app_user:$(DB_PASSWORD)@pg-ro.production.svc.cluster.local:5432/app"

Make the app reconnect

In this environment the primary will move (Kured reboots). Use a driver/ pool that retries on connection loss, and keep transactions short.

Connecting from your laptop (debugging)

Port-forward a Service, then use psql:

kubectl port-forward svc/pg-rw 5432:5432 -n production
# in another shell:
psql -h localhost -U app_user -d app          # password from the Secret

For superuser access (only because we set enableSuperuserAccess: true):

kubectl get secret pg-superuser -n production \
  -o jsonpath='{.data.password}' | base64 -d; echo
psql -h localhost -U postgres -d app

Connection pooling with PgBouncer

Direct connections are expensive (one OS process each). A Pooler resource deploys PgBouncer in front of the cluster.

pooler.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: pg-pooler-rw
  namespace: production
spec:
  cluster:
    name: pg
  instances: 2                 # (1)!
  type: rw                     # (2)!
  pgbouncer:
    poolMode: transaction      # (3)!
    parameters:
      max_client_conn: "500"
      default_pool_size: "25"
  1. Two PgBouncer pods for availability.
  2. rw pools to the primary. Use a second pooler with type: ro for read traffic if needed.
  3. Transaction pooling is the most efficient mode but does not support session-level features (prepared statements across transactions, advisory locks, SET). If your app needs those, use session mode instead.
kubectl apply -f pooler.yaml
kubectl get svc -n production | grep pooler   # creates pg-pooler-rw service

Point the application at the pooler instead of the cluster:

postgresql://app_user:$(DB_PASSWORD)@pg-pooler-rw.production.svc.cluster.local:5432/app

flowchart LR
    a1["app pod"] --> pool
    a2["app pod"] --> pool
    pool["pg-pooler-rw<br/>(PgBouncer, transaction mode)"] --> rw["pg-rw → Primary"]

Pooling modes at a glance

Mode A server connection is held for… Use when
session the whole client session app relies on session state (prepared statements, SET, advisory locks)
transaction one transaction default for web apps; most efficient
statement one statement autocommit-only workloads; most aggressive

What could go wrong

  • App connects to a pod IP → breaks on every failover. Always use the Service/pooler DNS name.
  • Transaction mode + prepared statements → subtle errors. Switch to session mode or disable server-side prepared statements in the driver.
  • No pg-superuser secret → you didn't enable superuser access; that's fine, use the app user.

Where to go deeper

Next: Scheduled backups.