PostgreSQL

Overview

CloudNativePG (CNPG) runs Postgres as 1 primary + 2 replicas across your 3 nodes, with automatic failover if the primary goes down. A PgBouncer pooler sits in front of it so applications never connect to Postgres directly.

The names below (postgres database, postgres app user) are placeholders for this guide — swap them for your real database/user names when you apply this for real, with one exception (see the superuser note below).

Architecture

Your App
   ↓
PgBouncer Pooler (postgres-pooler-rw)
   ↓
CNPG Cluster
   ├── postgres-cluster-1  (primary)
   ├── postgres-cluster-2  (replica)
   └── postgres-cluster-3  (replica)
        ↓
local-path-retain StorageClass
   └── local disk on each node (no Longhorn — CNPG handles replication itself)

Why this architecture

Concern	Solution
High availability	CNPG manages 1 primary + 2 replicas with automatic failover
Storage	`local-path-retain` — not Longhorn, since CNPG already replicates the data itself
Connection pooling	PgBouncer in `transaction` mode — reuses connections, reduces load on Postgres
Data safety	`Retain` reclaim policy — PVs survive PVC deletion

Why not Longhorn here: Longhorn (from the infrastructure doc) is great for workloads that don't replicate themselves — but CNPG already keeps 3 full copies of the data across the 3 nodes. Putting that on top of Longhorn replication too would mean 9 copies of the same data for no extra benefit, just wasted disk and write overhead. Use local-path-retain for Postgres specifically; keep Longhorn for everything else that needs it.

⚠️ The superuser is always `postgres` — this cannot be renamed

CNPG hardcodes the bootstrap superuser role to the name postgres. Whatever you name your application user and application database, the superuser itself will always be postgres, and there's no supported way to rename it. Plan your access/secrets around this from the start rather than assuming it's configurable later — admin/maintenance tasks always go through the postgres role, application traffic goes through your app user.

Prerequisites

Assumes the 3-node HA k3s cluster from the infrastructure doc — same node count requirement applies here too: CNPG's 1 primary + 2 replicas needs 3 separate nodes to actually be HA, same logic as the Longhorn/APISIX notes in that doc.

On all 3 nodes

open-iscsi must be running (same requirement as Longhorn, if Longhorn is also on these nodes):

sudo systemctl enable iscsid
sudo systemctl start iscsid

If Longhorn is also installed on these nodes, multipathd needs to ignore Longhorn's virtual disks — otherwise it can interfere with iSCSI and cause volumes to go faulted:

sudo bash -c 'cat >> /etc/multipath.conf << EOF

blacklist {
    device {
        vendor  "IET"
        product "VIRTUAL-DISK"
    }
}
EOF'
sudo systemctl restart multipathd

Cluster

3-node k3s cluster (this guide uses node-1, node-2, node-3 as example names)
CloudNativePG operator (installed below)

Installation

1. Install the CloudNativePG operator

helm repo add cnpg https://cloudnative-pg.github.io/charts
helm repo update
helm upgrade --install cnpg cnpg/cloudnative-pg \
  --namespace cnpg-system --create-namespace

Verify:

kubectl get pods -n cnpg-system

2. Apply manifests, in order

kubectl apply -f 00-namespace.yaml
kubectl apply -f 01-storageclass.yaml
kubectl apply -f 02-secret-superuser.yaml
kubectl apply -f 03-secret-appuser.yaml
kubectl apply -f 04-cluster.yaml

# Wait for the cluster to report healthy before continuing
kubectl get cluster -n db --watch

# Only once the cluster above is healthy
kubectl apply -f 05-pooler.yaml
kubectl apply -f 06-podmonitor.yaml

File reference

File	Purpose
`00-namespace.yaml`	Creates the `db` namespace
`01-storageclass.yaml`	`local-path-retain` StorageClass
`02-secret-superuser.yaml`	Secret for the `postgres` superuser (operator-managed, admin use only)
`03-secret-appuser.yaml`	Secret for the application user
`04-cluster.yaml`	CNPG cluster definition — 3 instances
`05-pooler.yaml`	PgBouncer pooler — 2 instances, transaction mode
`06-podmonitor.yaml`	PodMonitor for Prometheus/metrics scraping

Example database details (placeholder names)

Property	Example value	Notes
Superuser	`postgres`	fixed — cannot be renamed, see note above
Database	`postgres`	placeholder — rename for your real app
App user	`postgres`	placeholder — rename for your real app; can differ from the database name
Postgres version	16
Storage	40Gi per instance	adjust to your actual data size

Connection string

Applications should always connect via PgBouncer, never directly to a Postgres pod:

host:     postgres-pooler-rw.db.svc.cluster.local
port:     5432
dbname:   postgres
user:     postgres

Data migration (run once, after the cluster is healthy)

# Copy a SQL file into the primary pod
kubectl cp /path/to/backup.sql \
  db/postgres-cluster-1:/var/lib/postgresql/data/init.sql

# Run it as the postgres superuser
kubectl exec -n db postgres-cluster-1 -- \
  psql -U postgres -d postgres -f /var/lib/postgresql/data/init.sql

Useful commands

# Cluster status
kubectl get cluster -n db

# All pods
kubectl get pods -n db

# Which pod is currently primary
kubectl get pods -n db -l cnpg.io/cluster=postgres-cluster

# Pooler status
kubectl get pooler -n db

# PVCs
kubectl get pvc -n db

# Connect as superuser
kubectl exec -n db postgres-cluster-1 -- psql -U postgres

# Active connections
kubectl exec -n db postgres-cluster-1 -- \
  psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"

# Expand storage later (edit 04-cluster.yaml → storage.size, then re-apply)
kubectl apply -f 04-cluster.yaml

Troubleshooting

Issue	Cause	Fix
Longhorn volume `faulted` (if Longhorn shares these nodes)	`multipathd` interfering with iSCSI	Add the IET/VIRTUAL-DISK blacklist entry above, restart `multipathd`
PVC stuck `Pending`	Not enough schedulable storage on the node	Check node disk usage; clean up orphaned volumes if needed
Peer authentication failed for the app user	CNPG's default peer-auth restrictions on non-superuser roles for certain operations	Use the `postgres` superuser for admin/maintenance tasks, keep the app user for application traffic only
Schema/table not found unexpectedly	PostgreSQL lowercases unquoted identifiers	Use lowercase names consistently, or quote identifiers if mixed case is required