Skip to content
Hands-on IT IT Drills

Day 17: CNPG: Operations

Goal: pg-notes gets PgBouncer poolers for writes and reads, metrics in Prometheus and a Grafana dashboard; you create roles and databases via CRD, TLS via cert-manager, and you read parameters, resources and JSON logs. · Duration: approx. 85 min · Prerequisite: Day 7, Day 12, Day 16

Briefing

  • Pooler = PgBouncer as a Deployment with its own Service, type rw or ro; authentication via auth_query, without a password list.
  • Every instance exports metrics on port 9187 (metrics), every pooler on 9127. You create the PodMonitor yourself – enablePodMonitor is deprecated.
  • Roles: DatabaseRole (new in 1.30, recommended) or managed.roles in the cluster; databases: Database.
  • Everything goes through the Cluster object; the webhook rejects nonsense up front.
mkdir -p ~/pg/tag17 && cp ~/.kube/config ~/pg/tag17/.kc
export KUBECONFIG=~/pg/tag17/.kc
kubectl create namespace tag17
kubectl apply -f ~/pg/tag14/psql-client.yaml

Drill 1: Poolers for writes and reads

cat > ~/pg/tag17/pooler.yaml <<'EOF'
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: pg-notes-pooler-rw
  namespace: tag14
spec:
  cluster:
    name: pg-notes
  instances: 1
  type: rw
  pgbouncer:
    poolMode: transaction
    parameters:
      max_client_conn: "200"
      default_pool_size: "10"
  template:
    spec:
      containers:
      - name: pgbouncer
        resources:
          requests: {cpu: 10m, memory: 32Mi}
          limits: {memory: 64Mi}
---
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: pg-notes-pooler-ro
  namespace: tag14
spec:
  cluster:
    name: pg-notes
  instances: 1
  type: ro
  pgbouncer:
    poolMode: transaction
  template:
    spec:
      containers:
      - name: pgbouncer
        resources:
          requests: {cpu: 10m, memory: 32Mi}
          limits: {memory: 64Mi}
EOF
kubectl apply -f ~/pg/tag17/pooler.yaml
kubectl get pooler,svc -n tag14
kubectl get pooler pg-notes-pooler-rw -n tag14 -o jsonpath='{.status.image}{"\n"}'

Expected (abridged, after about 30 s):

pooler.postgresql.cnpg.io/pg-notes-pooler-rw created
pooler.postgresql.cnpg.io/pg-notes-pooler-ro created
NAME                                           AGE   CLUSTER    TYPE   PHASE
pooler.postgresql.cnpg.io/pg-notes-pooler-ro   31s   pg-notes   ro     active
pooler.postgresql.cnpg.io/pg-notes-pooler-rw   31s   pg-notes   rw     active
NAME                         TYPE        CLUSTER-IP       ...   PORT(S)
service/pg-notes-pooler-ro   ClusterIP   10.97.122.93     ...   5432/TCP
service/pg-notes-pooler-rw   ClusterIP   10.101.110.237   ...   5432/TCP
service/pg-notes-r           ClusterIP   10.103.175.65    ...   5432/TCP
service/pg-notes-ro          ClusterIP   10.101.253.25    ...   5432/TCP
service/pg-notes-rw          ClusterIP   10.105.222.205   ...   5432/TCP
ghcr.io/cloudnative-pg/pgbouncer:1.26.1

The application only changes the host – user and password stay the same:

kubectl exec -n tag14 psql-client -- psql -h pg-notes-pooler-rw \
  -Atc 'SELECT inet_server_addr(), pg_is_in_recovery(), count(*) FROM notes'
kubectl exec -n tag14 psql-client -- psql -h pg-notes-pooler-ro \
  -Atc 'SELECT inet_server_addr(), pg_is_in_recovery(), count(*) FROM notes'
kubectl get pods -n tag14 -l cnpg.io/podRole=instance \
  -o custom-columns=NAME:.metadata.name,IP:.status.podIP

Expected (abridged):

10.0.1.54|f|1001
10.0.1.107|t|1001
NAME         IP
pg-notes-1   10.0.1.107
pg-notes-2   10.0.2.14
pg-notes-3   10.0.1.54

The rw pooler lands on the primary, the ro pooler on a replica. The generated configuration shows how:

kubectl exec -n tag14 deploy/pg-notes-pooler-rw -c pgbouncer -- \
  grep -E '^\* =|^pool_mode|^auth_(user|query|dbname)|^client_tls_sslmode' \
  /controller/configs/pgbouncer.ini

Expected:

* = host=pg-notes-rw
pool_mode = transaction
auth_user = cnpg_pooler_pgbouncer
auth_query = SELECT usename, passwd FROM public.user_search($1)
auth_dbname = postgres
client_tls_sslmode = prefer

The pooler is just a client of the Services -rw or -ro. It checks the password via auth_query as cnpg_pooler_pgbouncer, logged in with a client certificate – CNPG created that with the first pooler.

Save your progress? Sign up for free

Drill 2: Pooling at work

50 clients through the pooler, with a look at the server side at the same time:

kubectl exec -n tag14 psql-client -- sh -c \
  'echo "SELECT count(*) FROM notes;" > /tmp/q.sql'
kubectl exec -n tag14 psql-client -- pgbench -h pg-notes-pooler-rw -n -c 50 -j 2 \
  -T 15 -f /tmp/q.sql

While pgbench is running, in a second terminal:

export KUBECONFIG=~/pg/tag17/.kc
kubectl cnpg psql pg-notes -n tag14 -- -Atc "SELECT usename, client_addr, count(*)
  FROM pg_stat_activity WHERE datname = 'notes' GROUP BY 1, 2"
kubectl exec -n tag14 deploy/pg-notes-pooler-rw -c pgbouncer -- \
  psql -h /controller/run -U pgbouncer pgbouncer -c 'SHOW POOLS' | cut -c1-80

Expected (abridged):

notes_app|10.0.1.99|10
 database  |         user          | cl_active | cl_waiting | ...  | sv_active |
-----------+-----------------------+-----------+------------+ ...  +-----------+
 notes     | notes_app             |        29 |         21 | ...  |        10 |
...
duration: 15 s
number of transactions actually processed: 21111
number of failed transactions: 0 (0.000%)
tps = 1549.431306 (without initial connection time)

50 clients, but only 10 server connections (default_pool_size) from the pooler Pod 10.0.1.99; the rest wait briefly in the queue (cl_waiting). You can only reach the admin console pgbouncer inside the Pod via the Unix socket (peer).

Save your progress? Sign up for free

Drill 3: Metrics and PodMonitor

An instance’s metrics directly via the API server proxy:

kubectl get --raw /api/v1/namespaces/tag14/pods/pg-notes-1:9187/proxy/metrics \
  | grep -E '^cnpg_collector_(up|fencing_on|pg_wal_archive_status|postgres_version)'

Expected:

cnpg_collector_fencing_on 0
cnpg_collector_pg_wal_archive_status{value="done"} 40
cnpg_collector_pg_wal_archive_status{value="ready"} 0
cnpg_collector_postgres_version{cluster="pg-notes",full="18.7"} 18.7
cnpg_collector_up{cluster="pg-notes"} 1

The kps from Day 14 reads all PodMonitors in the cluster (podMonitorSelector: {}). The documentation example only selects cnpg.io/cluster – but the pooler Pods carry that label too. That’s why you add cnpg.io/podRole:

cat > ~/pg/tag17/podmonitor.yaml <<'EOF'
apiVersion: monitoring.coreos.com/v1
kind: PodMonitor
metadata:
  name: pg-notes
  namespace: tag14
spec:
  selector:
    matchLabels:
      cnpg.io/cluster: pg-notes
      cnpg.io/podRole: instance
  podMetricsEndpoints:
  - port: metrics
---
apiVersion: monitoring.coreos.com/v1
kind: PodMonitor
metadata:
  name: pg-notes-pooler
  namespace: tag14
spec:
  selector:
    matchExpressions:
    - {key: cnpg.io/poolerName, operator: Exists}
  podMetricsEndpoints:
  - port: metrics
EOF
kubectl apply -f ~/pg/tag17/podmonitor.yaml

After about a minute you query Prometheus – also via the API server:

P=/api/v1/namespaces/monitoring/services/kps-kube-prometheus-stack-prometheus:9090\
/proxy/api/v1
kubectl get --raw "$P/targets?state=active" | jq -r '.data.activeTargets[]
  | select(.labels.namespace=="tag14") | [.scrapePool, .labels.pod, .health] | @tsv'

Expected:

podMonitor/tag14/pg-notes-pooler/0  pg-notes-pooler-ro-8bc8fc98b-gxkzr  up
podMonitor/tag14/pg-notes-pooler/0  pg-notes-pooler-rw-598f6fc974-brjvx up
podMonitor/tag14/pg-notes/0 pg-notes-1  up
podMonitor/tag14/pg-notes/0 pg-notes-2  up
podMonitor/tag14/pg-notes/0 pg-notes-3  up

Without cnpg.io/podRole, the pooler Pods also showed up in the lab under podMonitor/tag14/pg-notes/0 – on a port that delivers no instance metrics there.

Now a target outside the cluster: the exporter on pg1 from Day 7. Prometheus accepts ScrapeConfig objects with the label release: kps from all namespaces:

kubectl get prometheus -n monitoring \
  -o jsonpath='{.items[0].spec.scrapeConfigSelector}{"\n"}'
kubectl apply -f - <<'EOF'
apiVersion: monitoring.coreos.com/v1alpha1
kind: ScrapeConfig
metadata:
  name: pg1
  namespace: monitoring
  labels:
    release: kps
spec:
  staticConfigs:
    - targets: ["172.16.0.182:9187"]
      labels:
        instance: pg1
EOF

Expected:

{"matchLabels":{"release":"kps"}}
scrapeconfig.monitoring.coreos.com/pg1 created

After about a minute:

kubectl get --raw "$P/query?query=pg_up" \
  | jq -c '.data.result[] | [.metric.instance, .metric.job, .value[1]]'
kubectl get --raw "$P/query?query=sum+by+(datname)(pg_stat_activity_count)" \
  | jq -c '.data.result[] | [.metric.datname, .value[1]]' | head -3

Expected:

["pg1","scrapeConfig/monitoring/pg1","1"]
["notes","0"]
["postgres","1"]
["template0","0"]

The ScrapeConfig stays in place; from now on Grafana can display the data from pg1 (for example with the community dashboard “PostgreSQL Database”, ID 9628).

Save your progress? Sign up for free

Drill 4: Queries and dashboard

Three questions you really ask in operations – replication lag, WAL archive, pooler queue:

kubectl get --raw "$P/query?query=cnpg_pg_replication_lag" \
  | jq -r '.data.result[] | [.metric.pod, .value[1]] | @tsv'
kubectl get --raw "$P/query?query=cnpg_collector_pg_wal_archive_status" \
  | jq -r '.data.result[] | [.metric.pod, .metric.value, .value[1]] | @tsv'
kubectl get --raw "$P/label/__name__/values" | jq -r '.data[]' | grep -c '^cnpg_'

Expected (abridged):

pg-notes-1  0
pg-notes-3  0
pg-notes-2  0
pg-notes-1  done    41
pg-notes-1  ready   0
...
137

ready > 0 for a longer period means: WAL is piling up, archiving is stuck (Day 15). 137 metrics with the prefix cnpg_ (instances and poolers) are available. The official dashboard lives in the repo cloudnative-pg/grafana-dashboards; the Grafana sidecar of kps loads every ConfigMap with the label grafana_dashboard=1 from all namespaces:

curl -fsSLo ~/pg/tag17/cnpg-dashboard.json https://raw.githubusercontent.com/\
cloudnative-pg/grafana-dashboards/7dd1bc255b5202530e1bae7b858a3bb41a2028c7/\
charts/cluster/grafana-dashboard.json
kubectl create configmap cnpg-dashboard -n tag14 \
  --from-file=$HOME/pg/tag17/cnpg-dashboard.json
kubectl label configmap cnpg-dashboard -n tag14 grafana_dashboard=1

Expected:

configmap/cnpg-dashboard created
configmap/cnpg-dashboard labeled

create instead of apply: the JSON file is 250 kB, and apply would additionally copy it into an annotation. The commit is pinned – the repo has had no releases since 2025. Check via the Grafana API (admin password from Day 14):

kubectl port-forward -n monitoring svc/kps-grafana 3000:80 >/dev/null &
curl -s -u admin:lab-grafana-123 \
  'http://localhost:3000/api/search?query=CloudNativePG' \
  | jq -c '.[] | {title, uid, url}'
kill %1

Expected:

{"title":"CloudNativePG","uid":"cloudnative-pg","url":"/d/cloudnative-pg/cloudnativepg"}

In the browser (port forward with --address 0.0.0.0, then http://172.16.0.126:3000) you select namespace tag14 and cluster pg-notes at the top.

Save your progress? Sign up for free

Drill 5: Roles and databases as CRDs

The password lives in a Secret of type basic-auth whose username matches the role name:

kubectl create secret generic tag17-shop-pw -n tag14 \
  --type=kubernetes.io/basic-auth \
  --from-literal=username=tag17_shop --from-literal=password=lab-shop-123
cat > ~/pg/tag17/shop.yaml <<'EOF'
apiVersion: postgresql.cnpg.io/v1
kind: DatabaseRole
metadata:
  name: tag17-shop
  namespace: tag14
spec:
  cluster:
    name: pg-notes
  name: tag17_shop
  login: true
  connectionLimit: 10
  databaseRoleReclaimPolicy: delete
  passwordSecret:
    name: tag17-shop-pw
---
apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
  name: tag17-shop
  namespace: tag14
spec:
  cluster:
    name: pg-notes
  name: tag17_shop
  owner: tag17_shop
  databaseReclaimPolicy: delete
EOF
kubectl apply -f ~/pg/tag17/shop.yaml
kubectl get databaserole,database -n tag14

Expected (abridged, immediately afterwards):

databaserole.postgresql.cnpg.io/tag17-shop created
database.postgresql.cnpg.io/tag17-shop created
NAME                                         ...   APPLIED   MESSAGE
databaserole.postgresql.cnpg.io/tag17-shop   ...   false     secrets "tag17-shop-pw" is
forbidden: User "system:serviceaccount:tag14:pg-notes" cannot get resource "secrets"
NAME                                     ...   APPLIED   MESSAGE
database.postgresql.cnpg.io/tag17-shop   ...   false     while creating database
"tag17_shop": ERROR: role "tag17_shop" does not exist (SQLSTATE 42704)

Not your mistake: the instances may only read Secrets listed in their Role. The operator adds the new Secret at the next reconciliation, and after that everything works. Wait a minute:

kubectl get databaserole,database -n tag14
kubectl get role pg-notes -n tag14 -o json \
  | jq -c '.rules[] | select(.resources == ["secrets"]) | .resourceNames'
kubectl cnpg psql pg-notes -n tag14 -- -c '\du tag17_shop'
kubectl exec -n tag14 psql-client -- env PGPASSWORD=lab-shop-123 \
  psql -U tag17_shop -d tag17_shop -c 'CREATE TABLE artikel (id int)' \
  -Atc 'SELECT current_user, current_database()'

Expected (abridged):

databaserole.postgresql.cnpg.io/tag17-shop   ...   true
database.postgresql.cnpg.io/tag17-shop       ...   true
["pg-notes-app","pg-notes-ca","pg-notes-replication","pg-notes-server",
"pg-notes-superuser","tag17-shop-pw"]
        List of roles
 Role name  |   Attributes
------------+----------------
 tag17_shop | 10 connections
CREATE TABLE
tag17_shop|tag17_shop

DatabaseRole creates roles, but no privileges on tables – GRANT stays SQL (or a migration). Password rotation: give the Secret the label cnpg.io/reload=true, and a change takes effect immediately.

Save your progress? Sign up for free

Drill 6: Your own certificates with cert-manager

Instead of the CNPG CA, cert-manager can supply the server certificate. To try it out, a small cluster in tag17; the names belong in dnsNames:

cat > ~/pg/tag17/pg-tls.yaml <<'EOF'
apiVersion: cert-manager.io/v1
kind: Issuer
metadata:
  name: selfsigned
  namespace: tag17
spec:
  selfSigned: {}
---
apiVersion: cert-manager.io/v1
kind: Certificate
metadata:
  name: pg-tls-server
  namespace: tag17
spec:
  secretName: pg-tls-server
  secretTemplate:
    labels:
      cnpg.io/reload: ""
  duration: 2160h
  renewBefore: 360h
  usages: [server auth]
  dnsNames:
  - pg-tls-rw
  - pg-tls-rw.tag17
  - pg-tls-rw.tag17.svc
  - pg-tls-ro
  - pg-tls-ro.tag17
  - pg-tls-ro.tag17.svc
  - pg-tls-r
  - pg-tls-r.tag17
  - pg-tls-r.tag17.svc
  issuerRef:
    name: selfsigned
    kind: Issuer
    group: cert-manager.io
---
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-tls
  namespace: tag17
spec:
  instances: 1
  imageName: ghcr.io/cloudnative-pg/postgresql:18.7-minimal-trixie
  storage:
    size: 512Mi
  resources:
    requests: {cpu: 50m, memory: 128Mi}
    limits: {memory: 256Mi}
  certificates:
    serverTLSSecret: pg-tls-server
    serverCASecret: pg-tls-server
EOF
kubectl apply -f ~/pg/tag17/pg-tls.yaml
kubectl wait --for=condition=Ready certificate/pg-tls-server -n tag17
kubectl wait --for=condition=Ready cluster/pg-tls -n tag17 --timeout=180s
kubectl get certificate,secret -n tag17

Expected (abridged):

certificate.cert-manager.io/pg-tls-server condition met
cluster.postgresql.cnpg.io/pg-tls condition met
NAME                                        READY   SECRET          AGE
certificate.cert-manager.io/pg-tls-server   True    pg-tls-server   32s
NAME                        TYPE                       DATA   AGE
secret/pg-tls-app           kubernetes.io/basic-auth   11     30s
secret/pg-tls-ca            Opaque                     2      31s
secret/pg-tls-replication   kubernetes.io/tls          2      31s
secret/pg-tls-server        kubernetes.io/tls          3      31s

A self-signed certificate is its own CA, which is why both fields point to the same Secret (it contains ca.crt). CNPG still creates pg-tls-ca and pg-tls-replication itself – for the client certificates. Check from cp1 and with verify-full:

IP=$(kubectl get svc pg-tls-rw -n tag17 -o jsonpath='{.spec.clusterIP}')
echo | openssl s_client -starttls postgres -connect $IP:5432 2>/dev/null \
  | openssl x509 -noout -ext subjectAltName -enddate
PW=$(kubectl get secret pg-tls-app -n tag17 -o jsonpath='{.data.password}' | base64 -d)
kubectl exec -n tag17 pg-tls-1 -- env PGPASSWORD=$PW psql "host=pg-tls-rw dbname=app \
  user=app sslmode=verify-full sslrootcert=/controller/certificates/server-ca.crt" \
  -c '\conninfo' | grep -E 'Host  |SSL (Conn|Proto)'

Expected:

X509v3 Subject Alternative Name: critical
    DNS:pg-tls-rw, DNS:pg-tls-rw.tag17, DNS:pg-tls-rw.tag17.svc, DNS:pg-tls-ro, ...
notAfter=Feb 17 06:27:33 2027 GMT
 Host                 | pg-tls-rw
 SSL Connection       | true
 SSL Protocol         | TLSv1.3

With the wrong CA (client-ca.crt) you get SSL error: certificate verify failed. When cert-manager renews the certificate, CNPG reloads it without a restart thanks to cnpg.io/reload.

kubectl delete -f ~/pg/tag17/pg-tls.yaml

Save your progress? Sign up for free

Drill 7: Parameters and resources

What PostgreSQL really uses and where it comes from:

kubectl cnpg psql pg-notes -n tag14 -- -c "SELECT name, setting, unit, source
  FROM pg_settings WHERE name IN ('shared_buffers', 'work_mem', 'max_connections',
  'effective_cache_size', 'wal_level')"
kubectl get pod pg-notes-1 -n tag14 -o json \
  | jq -c '.spec.containers[] | {name, resources}'
kubectl cnpg status pg-notes -n tag14 | sed -n '/Instances status/,/^$/p'

Expected (abridged):

         name         | setting | unit |       source
----------------------+---------+------+--------------------
 effective_cache_size | 524288  | 8kB  | default
 max_connections      | 120     |      | configuration file
 shared_buffers       | 8192    | 8kB  | configuration file
 wal_level            | logical |      | configuration file
 work_mem             | 8192    | kB   | configuration file
{"name":"postgres","resources":{"limits":{"memory":"512Mi"},"requests":{"cpu":"100m",
"memory":"256Mi"}}}
Instances status
Name        Current LSN  Replication role  Status  QoS        Manager Version  Node
pg-notes-3  0/19000060   Primary           OK      Burstable  1.30.2           worker1
...

effective_cache_size is at the default of 4 GB – more than the Pod will ever have; the planner calculates too optimistically with it. Two failed attempts that the webhook catches:

kubectl patch cluster pg-notes -n tag14 --type merge \
  -p '{"spec":{"postgresql":{"parameters":{"listen_addresses":"127.0.0.1"}}}}'
kubectl patch cluster pg-notes -n tag14 --type merge --dry-run=server \
  -p '{"spec":{"postgresql":{"parameters":{"shared_buffers":"600MB"}}}}'

Expected:

The Cluster "pg-notes" is invalid: spec.postgresql.parameters.listen_addresses:
Invalid value: "127.0.0.1": Can't set fixed configuration parameter
The Cluster "pg-notes" is invalid: spec.resources.requests: Invalid value: "256Mi":
Memory request is lower than PostgreSQL `shared_buffers` value

The operator itself determines networking, paths, replication and archiving. shared_buffers must fit into the memory request – otherwise the OOM killer looms.

Save your progress? Sign up for free

Drill 8: Reading logs

CNPG writes everything as JSON to stdout; PostgreSQL messages have logger: postgres and live in record:

kubectl exec -n tag14 psql-client -- psql -c 'SELECT 1/0'
kubectl logs pg-notes-3 -n tag14 -c postgres --since=1m | grep division | jq .

Use your own primary.

Expected (abridged):

ERROR:  division by zero
{
  "level": "info",
  "ts": "2026-11-19T06:28:45.242467084Z",
  "logger": "postgres",
  "msg": "record",
  "logging_pod": "pg-notes-3",
  "record": {
    "log_time": "2026-11-19 06:28:45.241 UTC",
    "user_name": "notes_app",
    "database_name": "notes",
    "connection_from": "10.0.1.135:58360",
    "error_severity": "ERROR",
    "sql_state_code": "22012",
    "message": "division by zero",
    "query": "SELECT 1/0",
    "application_name": "psql",
    ...

Careful: level is always info; the severity is in record.error_severity. Filter with jq or with the plugin across all Pods:

kubectl logs pg-notes-3 -n tag14 -c postgres --since=10m | jq -r 'select(
  .logger == "postgres" and .record.error_severity == "ERROR")
  | [.record.log_time, .record.user_name, .record.message] | @tsv'
kubectl cnpg logs cluster pg-notes -n tag14 --tail 200 \
  | kubectl cnpg logs pretty --loggers postgres | grep -i division

Expected (abridged):

2026-11-19 06:26:01.405 UTC postgres    role "tag17_shop" does not exist
2026-11-19 06:28:45.241 UTC notes_app   division by zero
2026-11-19T06:28:45.242 ERROR    pg-notes-3 postgres         division by zero

In production, a log agent (Fluent Bit, Vector, Promtail) collects stdout; thanks to JSON, every field is searchable.

Save your progress? Sign up for free

Exercise: Speed Drills

  1. Show how many server connections the rw pooler currently has open. Target time: 45 s
  2. Show all Prometheus targets from tag14 and their state. Target time: 45 s
  3. Show all DatabaseRoles and whether they are applied. Target time: 15 s
  4. Show all errors (ERROR, FATAL) of the primary from the last 10 minutes. Target time: 60 s

Solution

kubectl exec -n tag14 deploy/pg-notes-pooler-rw -c pgbouncer -- psql \
  -h /controller/run -U pgbouncer pgbouncer -c 'SHOW SERVERS'
kubectl get --raw "$P/targets?state=active" | jq -r '.data.activeTargets[]
  | select(.labels.namespace=="tag14") | [.labels.pod, .health] | @tsv'
kubectl get databaserole -n tag14
kubectl logs pg-notes-3 -n tag14 -c postgres --since=10m | jq -r 'select(
  .record.error_severity == "ERROR" or .record.error_severity == "FATAL")
  | .record.message'

Expected (abridged, without load):

 type | user | database | replication | state | addr | port | ...
------+------+----------+-------------+-------+------+------+ ...
(0 rows)

pg-notes-pooler-ro-8bc8fc98b-gxkzr  up
pg-notes-pooler-rw-598f6fc974-brjvx up
pg-notes-2  up
pg-notes-3  up
pg-notes-1  up
NAME         AGE    CLUSTER    PG NAME      APPLIED   MESSAGE
tag17-shop   7m6s   pg-notes   tag17_shop   true
role "tag17_shop" does not exist
division by zero

$P comes from Drill 3. Without load, the pooler has no server connection open – it only opens them on demand and closes them after server_idle_timeout.

Save your progress? Sign up for free

Checkpoint

The poolers, PodMonitors and dashboard stay in place for Days 18 and 19. The challenge and the cleanup remove tag17-shop and tag17_monitor again.

kubectl get pooler -n tag14

Expected: pg-notes-pooler-rw and pg-notes-pooler-ro, phase active.

kubectl get podmonitor -n tag14

Expected: pg-notes and pg-notes-pooler.

kubectl get --raw "$P/query?query=cnpg_collector_up" | jq '.data.result | length'

Expected: 3.

kubectl get configmap cnpg-dashboard -n tag14 --show-labels

Expected: label grafana_dashboard=1.

kubectl get scrapeconfig -n monitoring

Expected: pg1 is listed.

kubectl get cluster -n tag17

Expected: No resources found – pg-tls is deleted.

Challenge

Create the role tag17_monitor (login, member of pg_monitor, comment “nur lesen”) inline in the cluster. Take away its login via SQL and watch who wins. Then remove it declaratively.

Hint: spec.managed.roles with ensure; reconciliation runs periodically.

Solution

kubectl patch cluster pg-notes -n tag14 --type merge -p '{"spec":{"managed":{"roles":[
  {"name":"tag17_monitor","ensure":"present","login":true,
   "inRoles":["pg_monitor"],"comment":"nur lesen"}]}}}'
kubectl cnpg psql pg-notes -n tag14 -- -c '\du+ tag17_monitor'
kubectl get cluster pg-notes -n tag14 -o jsonpath='{.status.managedRolesStatus}' | jq -c
kubectl cnpg psql pg-notes -n tag14 -- -c 'ALTER ROLE tag17_monitor NOLOGIN'

Expected (abridged):

cluster.postgresql.cnpg.io/pg-notes patched
              List of roles
   Role name   | Attributes | Description
---------------+------------+-------------
 tag17_monitor |            | nur lesen
{"byStatus":{"not-managed":["notes_app","tag17_shop"],"reconciled":["tag17_monitor"],
"reserved":["cnpg_metrics_exporter","streaming_replica","postgres",
"cnpg_pooler_pgbouncer"]},"passwordStatus":{"tag17_monitor":{...}}}
ALTER ROLE

Now check every 20 s:

kubectl cnpg psql pg-notes -n tag14 -- -Atc \
  "SELECT now()::time(0), rolcanlogin FROM pg_roles WHERE rolname = 'tag17_monitor'"

Expected (abridged): f for a long time, eventually t.

06:30:24|f
...
06:35:56|f
06:36:58|t

The cluster wins – but only at the instance’s next reconciliation, in the lab after more than six minutes, or immediately after a restart/switchover (for example kubectl cnpg promote pg-notes <replica> -n tag14, Day 16). With a DatabaseRole, the operator doesn’t correct manual changes at all until you apply it again. Remember: change roles only declaratively.

Removal – first absent, then delete the entry:

kubectl patch cluster pg-notes -n tag14 --type merge -p '{"spec":{"managed":{"roles":[
  {"name":"tag17_monitor","ensure":"absent"}]}}}'
kubectl cnpg psql pg-notes -n tag14 -- -Atc \
  "SELECT count(*) FROM pg_roles WHERE rolname = 'tag17_monitor'"
kubectl patch cluster pg-notes -n tag14 --type json \
  -p '[{"op":"remove","path":"/spec/managed"}]'

Expected (after about 15 s):

cluster.postgresql.cnpg.io/pg-notes patched
0
cluster.postgresql.cnpg.io/pg-notes patched

Save your progress? Sign up for free

Cleanup

The poolers, PodMonitors, dashboard and the ScrapeConfig for pg1 stay. The shop’s role and database go – Reclaim Policy: delete also deletes them in PostgreSQL:

kubectl delete -f ~/pg/tag17/shop.yaml
kubectl delete secret tag17-shop-pw -n tag14
kubectl cnpg psql pg-notes -n tag14 -- -Atc \
  "SELECT count(*) FROM pg_roles WHERE rolname LIKE 'tag17%'" \
  -c "SELECT count(*) FROM pg_database WHERE datname LIKE 'tag17%'"
kubectl delete pod psql-client -n tag14
kubectl delete namespace tag17

Expected (abridged):

databaserole.postgresql.cnpg.io "tag17-shop" deleted from tag14 namespace
database.postgresql.cnpg.io "tag17-shop" deleted from tag14 namespace
secret "tag17-shop-pw" deleted from tag14 namespace
0
0
pod "psql-client" deleted from tag14 namespace
namespace "tag17" deleted

Interview Note

  • “Why a pooler in front of CNPG?” – To bundle many short connections (Pods, serverless) onto a few server connections; transaction mode for stateless apps. A Pooler of type rw/ro attaches to the Services -rw/-ro.
  • “How do you monitor CNPG?” – PodMonitor for instances (9187) and poolers (9127), alerts on cnpg_collector_up, replication lag, WAL archive (ready) and backup age; dashboard from cloudnative-pg/grafana-dashboards.
  • “Declarative roles – what works, what doesn’t?” – DatabaseRole/managed.roles and Database create roles, passwords and databases; table privileges remain a matter for SQL or migrations.

Enterprise Note

  • Poolers with at least two instances and anti-affinity; tune max_client_conn and default_pool_size against the max_connections of all poolers.
  • Roll out PodMonitors and PrometheusRules (template: alerts.yaml in the CNPG repo) via GitOps alongside the cluster; metrics port with monitoring.tls.enabled.
  • Role passwords from a secret manager (External Secrets, Vault/OpenBao) with cnpg.io/reload; DatabaseRole with retain against accidental deletion.
  • Server certificates from the corporate CA via cert-manager (ClusterIssuer), clients with verify-full; cert-manager monitors expiry.
  • Requests = limits for memory (QoS Guaranteed), shared_buffers at about 25 % of it, effective_cache_size set to match the limit.