Running Pgpool-II on Kubernetes for PostgreSQL Query Load Balancing and Connection Pooling

Problem

PostgreSQL is a stateful service that requires backup, restore, and automatic failover. Kubernetes does not provide these capabilities natively, so a PostgreSQL Operator is used to manage the database cluster. Existing operators (Crunchy, Zalando, KubeDB) do not include query load‑balancing, which is added by deploying Pgpool‑II in front of the primary and replica services.

Architecture

The PostgreSQL Operator creates the database pods. A single Pgpool‑II pod is placed between the client and the primary and replica services, providing read‑load‑balancing and connection pooling.

Prerequisites

Kubernetes cluster with kubectl installed.

PostgreSQL Operator and a PostgreSQL cluster already deployed.

Minimal Pgpool‑II Configuration

Only load‑balancing and connection pooling are required; health checks, watchdog, and Pgpool‑II‑managed failover are disabled.

backend_hostname0 = '<primary service name>'
backend_hostname1 = '<replica service name>'
backend_port0 = '5432'
backend_port1 = '5432'
backend_flag0 = 'ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER'
backend_flag1 = 'DISALLOW_TO_FAILOVER'

failover_on_backend_error = off

sr_check_period = 10            # when using streaming‑replication check
sr_check_user = 'postgres'      # when using streaming‑replication check

load_balance_mode = on
connection_cache = on
listen_addresses = '*'

Configuration via Environment Variables

Variables prefixed with PGPOOL_PARAMS_ are translated into pgpool.conf entries. Example deployment fragment:

env:
- name: PGPOOL_PARAMS_BACKEND_HOSTNAME0
  value: "mypostgres"
- name: PGPOOL_PARAMS_BACKEND_HOSTNAME1
  value: "mypostgres-replica"
- name: PGPOOL_PARAMS_BACKEND_FLAG0
  value: "ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER"
- name: PGPOOL_PARAMS_BACKEND_FLAG1
  value: "DISALLOW_TO_FAILOVER"

Resulting pgpool.conf entries:

backend_hostname0 = 'mypostgres'
backend_hostname1 = 'mypostgres-replica'
backend_flag0 = 'ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER'
backend_flag1 = 'DISALLOW_TO_FAILOVER'

Client authentication credentials are supplied via environment variables (e.g., POSTGRES_USERNAME, POSTGRES_PASSWORD) and are converted into the pool_passwd file at container start.

Configuration via ConfigMap

A ConfigMap can hold the full pgpool.conf and pool_hba.conf files and be mounted as a volume.

apiVersion: v1
kind: ConfigMap
metadata:
  name: pgpool-config
  labels:
    name: pgpool-config
data:
  pgpool.conf: |- 
    listen_addresses = '*'
    port = 9999
    backend_hostname0 = 'postgres'
    ...
  pool_hba.conf: |- 
    local   all         all                               trust
    host    all         all         127.0.0.1/32          trust
    hostssl all         all         0.0.0.0/0             md5

Credentials are injected from a Kubernetes Secret and referenced in the pod’s environment:

env:
- name: POSTGRES_USERNAME
  valueFrom:
    secretKeyRef:
      name: mypostgres-postgres-secret
      key: username
- name: POSTGRES_PASSWORD
  valueFrom:
    secretKeyRef:
      name: mypostgres-postgres-secret
      key: password

Backend Settings

Only two backends are needed: backend_hostname0 (primary) and backend_hostname1 (replica). The flag ALWAYS_PRIMARY guarantees that the primary service remains primary after pod restarts or failover, while DISALLOW_TO_FAILOVER disables Pgpool‑II‑initiated failover because Kubernetes already restarts failed pods.

backend_flag0 = 'ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER'
backend_flag1 = 'DISALLOW_TO_FAILOVER'
failover_on_backend_error = off

Generating pool_passwd

On pod start Pgpool‑II runs pg_md5 using the *_USERNAME and *_PASSWORD environment variables to create pool_passwd. Example verification:

$ kubectl exec <code>pgpool-pod</code> -it -- cat /opt/pgpool-II/etc/pool_passwd
postgres:md53175bce1d3201d16594cebf9d7eb3f9d

Streaming Replication Check

When enabled, Pgpool‑II periodically connects to the PostgreSQL backends to verify replication delay. The user is defined by sr_check_user; if sr_check_password is omitted, the password is read from pool_passwd. Example:

sr_check_period = 10
sr_check_user = 'postgres'

On Kubernetes the replication‑delay check is often unnecessary; set sr_check_period = 0 to disable it.

SSL Configuration

Enable SSL with:

ssl = on

Pgpool‑II automatically generates a private key and certificate under /opt/pgpool-II/certs/ and sets ssl_key and ssl_cert. Add a hostssl line to pool_hba.conf to require SSL connections:

hostssl    all         all         0.0.0.0/0             md5

Monitoring with Pgpool‑II Exporter

The pgpool2_exporter container exposes Pgpool‑II metrics for Prometheus. A deployment that includes both containers looks like:

spec:
  containers:
  - name: pgpool
    image: pgpool/pgpool
  - name: pgpool-stats
    image: pgpool/pgpool2_exporter

Exporter connection parameters are supplied via the same environment variables used for Pgpool‑II.

Deployment Steps

# Download example manifests
curl -LO https://raw.githubusercontent.com/pgpool/pgpool2_on_k8s/master/pgpool-deploy-minimal.yaml
curl -LO https://raw.githubusercontent.com/pgpool/pgpool2_on_k8s/master/pgpool-configmap.yaml
curl -LO https://raw.githubusercontent.com/pgpool/pgpool2_on_k8s/master/pgpool-deploy-metrics.yaml

# Apply manifests
kubectl apply -f pgpool-configmap.yaml
kubectl apply -f pgpool-deploy-minimal.yaml
kubectl apply -f pgpool-deploy-metrics.yaml

After applying the manifests, verify the Pgpool‑II pod and service with kubectl get pod and kubectl get svc.