Day 17: CNPG: Operations
Goal:
pg-notesgets 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, typerworro; authentication viaauth_query, without a password list.- Every instance exports metrics on port 9187 (
metrics), every pooler on 9127. You create thePodMonitoryourself –enablePodMonitoris deprecated. - Roles:
DatabaseRole(new in 1.30, recommended) ormanaged.rolesin the cluster; databases:Database. - Everything goes through the
Clusterobject; 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.yamlDrill 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.podIPExpected (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.iniExpected:
* = 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.sqlWhile 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-80Expected (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.yamlAfter 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
EOFExpected:
{"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 -3Expected:
["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=1Expected:
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 %1Expected:
{"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 tag14Expected (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 tag17Expected (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.yamlSave 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 divisionExpected (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
- Show how many server connections the
rwpooler currently has open. Target time: 45 s - Show all Prometheus targets from
tag14and their state. Target time: 45 s - Show all
DatabaseRoles and whether they are applied. Target time: 15 s - 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 tag14Expected: pg-notes-pooler-rw and
pg-notes-pooler-ro, phase active.
kubectl get podmonitor -n tag14Expected: 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-labelsExpected: label
grafana_dashboard=1.
kubectl get scrapeconfig -n monitoringExpected: pg1 is listed.
kubectl get cluster -n tag17Expected: 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 tag17Expected (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;
transactionmode for stateless apps. APoolerof typerw/roattaches to the Services-rw/-ro. - “How do you monitor CNPG?” –
PodMonitorfor instances (9187) and poolers (9127), alerts oncnpg_collector_up, replication lag, WAL archive (ready) and backup age; dashboard fromcloudnative-pg/grafana-dashboards. - “Declarative roles – what works, what doesn’t?” –
DatabaseRole/managed.rolesandDatabasecreate 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_connanddefault_pool_sizeagainst themax_connectionsof all poolers. - Roll out
PodMonitors andPrometheusRules (template:alerts.yamlin the CNPG repo) via GitOps alongside the cluster; metrics port withmonitoring.tls.enabled. - Role passwords from a secret manager (External Secrets,
Vault/OpenBao) with
cnpg.io/reload;DatabaseRolewithretainagainst accidental deletion. - Server certificates from the corporate CA via cert-manager
(
ClusterIssuer), clients withverify-full; cert-manager monitors expiry. - Requests = limits for memory (QoS
Guaranteed),shared_buffersat about 25 % of it,effective_cache_sizeset to match the limit.