PostgreSQL Configuration & Pod Tuning on Kubernetes — CloudNativePG Deep Dive

Introduction

In Post 3, you deployed a working 3-instance PostgreSQL cluster on your local Kind cluster with just 7 lines of YAML. But real workloads need real tuning — shared_buffers, connection limits, authentication rules, extensions like pg_stat_statements, timezones, and more.

Here’s the catch: you can’t SSH into a CloudNativePG pod and edit postgresql.conf directly. The containers are immutable by design. Every configuration change has to go through the declarative Cluster YAML — and once you understand this model, it’s actually far safer and more predictable than editing config files on individual servers.

This post covers everything about the postgresql configuration section and pod-level settings — using the same Kind cluster from Post 3 so you can test every example immediately.

What you’ll learn:

Why you can’t use ALTER SYSTEM in CloudNativePG (and how to enable it if you must)
How to set postgresql.conf parameters declaratively — shared_buffers, max_connections, etc.
Which parameters are fixed by the operator and cannot be overridden
How to configure pg_hba.conf authentication rules
How to enable extensions like pg_stat_statements, auto_explain, and pgaudit
How to set environment variables, timezones, and custom volumes at the pod level

Prerequisites

Before you start, make sure you have:

[ ] The Kind cluster pg from Post 3 up and running (kubectl get nodes shows pg-control-plane)
[ ] The CloudNativePG operator installed and running
[ ] kubectl and the cnpg plugin working (kubectl cnpg --help)
[ ] A basic PostgreSQL cluster already deployed (we’ll modify and redeploy it in this post)

Lab Environment

Component	Version / Details
Kubernetes	Kind v0.27 (from Post 3)
CloudNativePG Operator	v1.28.1
PostgreSQL	16.x
kubectl	v1.32+
cnpg plugin	v1.25.0+

Part 1: Why You Can’t Just Edit postgresql.conf

Anyone coming from traditional PostgreSQL administration is used to three files:

File	Purpose
`postgresql.conf`	Main runtime configuration (GUCs)
`pg_hba.conf`	Client authentication rules
`pg_ident.conf`	Maps external usernames to internal database users

In CloudNativePG, you never touch these files directly. The PostgreSQL containers are immutable — this is intentional. Instead, you define your desired configuration in the postgresql section of the Cluster resource, and the operator generates and maintains these files for you across every instance in the cluster consistently.

⚠️ Never use ALTER SYSTEM to change configuration imperatively. It bypasses the operator, isn’t replicated across the cluster, and can leave your cluster in an unpredictable state. By default, CloudNativePG disables ALTER SYSTEM entirely on new clusters.

This declarative model means:

Every replica has identical configuration — no config drift
Configuration changes are tracked in Git (Infrastructure-as-Code)
The operator handles reloads/restarts automatically based on what changed

Part 2: How the Operator Builds custom.conf

When a PostgreSQL pod starts, its postgresql.conf includes just two lines:

listen_addresses = '*'
include custom.conf

The real configuration lives in custom.conf, which the operator generates by layering settings in this exact order:

1. Global default parameters       (set by the operator, e.g. wal_level = 'logical')
2. PostgreSQL version-specific defaults
3. Your user-provided parameters   (from .spec.postgresql.parameters)
4. Fixed parameters                (always win — cannot be overridden)

This layering matters: your settings can override the defaults, but never the fixed parameters (things like port, ssl, archive_command — these are core to how the operator manages replication and WAL archiving).

Part 3: Setting PostgreSQL Parameters Declaratively

Step 3.1: Basic Parameter Configuration

Let’s tune our cluster from Post 3. Edit cluster-example.yaml:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example
spec:
  instances: 3
  storage:
    size: 1Gi

  postgresql:
    parameters:
      shared_buffers: "256MB"
      max_connections: "200"
      work_mem: "8MB"
      maintenance_work_mem: "128MB"
      effective_cache_size: "1GB"
      log_min_duration_statement: "1000"  # log queries slower than 1s

Important: CloudNativePG only accepts strings for parameter values — always wrap numbers in quotes, even for numeric GUCs.

Apply the change:

kubectl apply -f cluster-example.yaml

root@E-5CG1467JZY:/mnt/c/Users/emaideb# vi cluster-example.yaml
root@E-5CG1467JZY:/mnt/c/Users/emaideb# kubectl apply -f cluster-example.yaml
cluster.postgresql.cnpg.io/cluster-example configured

Step 3.2: Verify the Change Took Effect

Some parameters apply with a simple reload (no downtime), others require a rolling restart. The operator determines this automatically based on PostgreSQL’s own parameter classification (context column in pg_settings).

kubectl cnpg status cluster-example

Look for the Instances section — if a rolling update is in progress, you’ll see the pods being recreated one at a time (starting with replicas, then the primary last, to avoid unnecessary switchovers).

Verify the setting is live:

kubectl cnpg psql cluster-example

SHOW shared_buffers;
--  shared_buffers
-- ----------------
--  256MB

SHOW max_connections;
--  max_connections
-- -----------------
--  200

\q

Step 3.3: See the Generated custom.conf

You can inspect the actual generated configuration file from inside the pod:

kubectl exec -it cluster-example-1 -c postgres -- cat /controller/log/../pgdata/custom.conf

Or more reliably:

kubectl exec -it cluster-example-1 -- cat /var/lib/postgresql/data/pgdata/custom.conf

You’ll see your parameters merged with the operator’s global defaults — things like wal_level = 'logical', archive_timeout = '5min', and max_replication_slots = '32' that are set automatically.

5CG1467JZY:/mnt/c/Users/emaideb# root@E-5CG1467JZY:/mnt/c/Users/emaideb# kubectl exec -it cluster-example-2 — cat /var/lib/postgresql/data/pgdata/custom.conf

Defaulted container “postgres” out of: postgres, bootstrap-controller (init) allow_alter_system = ‘off’ archive_command = ‘/controller/manager wal-archive –log-destination /controller/log/postgres.json %p’ archive_mode = ‘on’ archive_timeout = ‘5min’ cluster_name = ‘cluster-example’ dynamic_shared_memory_type = ‘posix’ effective_cache_size = ‘1GB’ full_page_writes = ‘on’ hot_standby = ‘true’ listen_addresses = ‘‘
log_destination = ‘csvlog’
log_directory = ‘/controller/log’
log_filename = ‘postgres’
log_min_duration_statement = ‘1000’
log_rotation_age = ‘0’
log_rotation_size = ‘0’
log_truncate_on_rotation = ‘false’
logging_collector = ‘on’
maintenance_work_mem = ‘128MB’
max_connections = ‘200’
max_parallel_workers = ’32’
max_replication_slots = ’32’
max_worker_processes = ’32’
port = ‘5432’
restart_after_crash = ‘false’
shared_buffers = ‘256MB’
shared_memory_type = ‘mmap’
shared_preload_libraries = ”
ssl = ‘on’
ssl_ca_file = ‘/controller/certificates/client-ca.crt’
ssl_cert_file = ‘/controller/certificates/server.crt’
ssl_key_file = ‘/controller/certificates/server.key’
ssl_max_protocol_version = ‘TLSv1.3’
ssl_min_protocol_version = ‘TLSv1.3’
unix_socket_directories = ‘/controller/run’
wal_keep_size = ‘512MB’
wal_level = ‘logical’
wal_log_hints = ‘on’
wal_receiver_timeout = ‘5s’
wal_sender_timeout = ‘5s’
work_mem = ‘8MB’
cnpg.config_sha256 = ‘3a6998e7119d8bf706354b384de8fe6d61f1238f503bc543ebca4b63584d6acb’

Part 4: Understanding wal_level — An Important Default

Here’s a detail that trips up a lot of people migrating from standard PostgreSQL: CloudNativePG sets wal_level = 'logical' by default, not replica (which is upstream PostgreSQL’s default).

wal_level	What it includes
`minimal`	Only crash recovery info — single-instance clusters only, WAL archiving disabled
`replica`	Adds WAL archiving + streaming replication support
`logical`	Everything in `replica`, plus logical decoding/replication support

Why does CloudNativePG default to logical? To support logical replication out of the box — useful for migrations from external PostgreSQL servers (which we’ll cover in a later post on Logical Replication).

If you don’t need logical replication, set it to replica to reduce WAL volume:

postgresql:
  parameters:
    wal_level: "replica"

Note: minimal is only allowed for single-instance clusters with WAL archiving disabled — not useful for HA clusters.

Part 5: The Complete List of Fixed Parameters (Read-Only)

The operator locks down certain parameters entirely — attempting to set these in your YAML triggers a webhook rejection before the change is even applied. This protects the cluster’s core replication and WAL mechanics.

You cannot set these in .spec.postgresql.parameters:

allow_alter_system         data_directory              log_rotation_age
allow_system_table_mods    data_sync_retry             log_rotation_size
archive_cleanup_command    external_pid_file            log_truncate_on_rotation
archive_command             hba_file                     logging_collector
archive_mode                 hot_standby                  port
bonjour                        ident_file                     primary_conninfo
bonjour_name                jit_provider                  primary_slot_name
cluster_name                listen_addresses            promote_trigger_file
config_file                  log_destination              recovery_target*
log_directory                 recovery_end_command    restart_after_crash
log_file_mode                 restore_command             shared_preload_libraries
log_filename                   ssl / ssl_ca_file / ssl_cert_file / ssl_key_file
                                    stats_temp_directory       synchronous_standby_names
                                    syslog_*                          unix_socket_*

If you try to set one of these, kubectl apply will fail immediately with a validation error — this is a good thing. It prevents you from accidentally breaking replication or WAL archiving.

Part 6: Configuring pg_hba.conf — Authentication Rules

CloudNativePG builds pg_hba.conf from four layered sections, evaluated top to bottom (first match wins):

1. Fixed rules           (operator-managed — replication, pooler auth)
2. Your user-defined rules
3. Optional LDAP section
4. Default catch-all rule (scram-sha-256 for PG14+, md5 for PG13-)

Step 6.1: Add a Custom Authentication Rule

Suppose you want to allow the app user to connect to the app database from a specific subnet using SCRAM authentication:

spec:
  postgresql:
    pg_hba:
      - hostssl app app 10.244.0.0/16 scram-sha-256

Apply:

kubectl apply -f cluster-example.yaml

This is a reload-only change — no pod restart needed. The resulting pg_hba.conf on each instance will look like:

local   all             all                                     peer
hostssl postgres        streaming_replica   all   cert map=cnp_streaming_replica
hostssl replication     streaming_replica   all   cert map=cnp_streaming_replica
hostssl all             cnp_pooler_pgbouncer all  cert map=cnp_pooler_pgbouncer
hostssl app             app                 10.244.0.0/16      scram-sha-256   ← your rule
host    all             all                 all                scram-sha-256   ← default

Step 6.2: LDAP Authentication (Optional, Enterprise Use)

If your organization uses LDAP for centralized auth, CloudNativePG supports it directly in the spec:

postgresql:
  ldap:
    server: 'openldap.default.svc.cluster.local'
    bindSearchAuth:
      baseDN: 'ou=org,dc=example,dc=com'
      bindDN: 'cn=admin,dc=example,dc=com'
      bindPassword:
        name: 'ldapBindPassword'
        key: 'data'
    searchAttribute: 'uid'

This generates the corresponding pg_hba.conf LDAP entry automatically — no manual file editing.

Part 7: Enabling PostgreSQL Extensions

CloudNativePG automatically manages shared_preload_libraries for four well-known extensions. You don’t add them to shared_preload_libraries yourself — you just configure their parameters, and the operator detects the prefix and wires everything up.

Step 7.1: Enable pg_stat_statements (Most Common)

This is the single most useful extension for query performance monitoring:

spec:
  postgresql:
    parameters:
      pg_stat_statements.max: "10000"
      pg_stat_statements.track: "all"

Apply and verify:

kubectl apply -f cluster-example.yaml
kubectl cnpg psql cluster-example

-- Run a few test queries first
SELECT * FROM pg_database;
SELECT count(*) FROM pg_class;

-- Then check pg_stat_statements
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

The operator automatically runs CREATE EXTENSION IF NOT EXISTS pg_stat_statements on every database in the cluster — you don’t need to run this manually.

Step 7.2: Enable auto_explain (Slow Query Debugging)

Automatically logs execution plans for slow queries — invaluable for tracking down unoptimized queries without manually running EXPLAIN:

spec:
  postgresql:
    parameters:
      auto_explain.log_min_duration: "10s"

⚠️ Caution: auto_explain adds overhead. Use a conservative threshold (e.g., 10s or higher) in production, and only lower it temporarily for debugging.

Step 7.3: Enable pgaudit (Compliance / Audit Logging)

For detailed session and object-level audit logging (common requirement in regulated environments):

spec:
  postgresql:
    parameters:
      pgaudit.log: "all, misc"
      pgaudit.log_catalog: "off"
      pgaudit.log_parameter: "on"
      pgaudit.log_relation: "on"

Step 7.4: The Managed Extensions Reference Table

Extension	Parameter Prefix	Use Case
`pg_stat_statements`	`pg_stat_statements.*`	Query performance monitoring
`auto_explain`	`auto_explain.*`	Automatic slow query plan logging
`pgaudit`	`pgaudit.*`	Session/object audit logging
`pg_failover_slots`	`pg_failover_slots.*`	Logical replication slots that survive failover

For any of these, simply adding a parameter with the matching prefix is enough — the operator adds the library to shared_preload_libraries automatically and removes it when no longer needed (which triggers a restart, since removing preload libraries always requires one).

Part 8: Pod-Level Configuration

Beyond postgresql.conf, you can also configure things at the pod level — environment variables, custom volumes, and shared memory sizing.

Step 8.1: Setting the Cluster Timezone

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example
spec:
  instances: 3
  env:
    - name: TZ
      value: "Asia/Kolkata"
  storage:
    size: 1Gi

Note: Any change to env or envFrom triggers a rolling update of all pods. Also, the operator blocks setting POD_NAME, NAMESPACE, or any variable prefixed with PG — these are reserved for internal use.

Step 8.2: Environment Variables from ConfigMaps/Secrets

spec:
  envFrom:
    - configMapRef:
        name: my-app-config
    - secretRef:
        name: my-app-secrets

⚠️ Gotcha: If you update the ConfigMap or Secret content itself (not the reference), the operator does not detect the change automatically — same behavior as standard Kubernetes pods. You’ll need to trigger a manual restart:

kubectl cnpg restart cluster-example

Step 8.3: Shared Memory Sizing (Important for Parallel Queries)

PostgreSQL uses /dev/shm for dynamic shared memory — critical for parallel query performance. By default, CloudNativePG mounts a memory-backed emptyDir for this.

Check the current size inside a running pod:

kubectl exec -it cluster-example-1 -- mount | grep shm

Expected output:

shm on /dev/shm type tmpfs (rw,nosuid,nodev,noexec,relatime,size=65536k)

If your workload does heavy parallel query processing and hits shared memory limits, increase it:

spec:
  ephemeralVolumesSizeLimit:
    shm: "1Gi"

Step 8.4: Projected Volumes (Mounting Custom Files)

Sometimes an extension needs an extra data file inside the pod — for example, a custom TLS certificate. Use projectedVolumeTemplate to mount Secret or ConfigMap content as files:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example-projected
spec:
  instances: 3
  projectedVolumeTemplate:
    sources:
      - secret:
          name: sample-secret
          items:
            - key: tls.crt
              path: certificate/tls.crt
            - key: tls.key
              path: certificate/tls.key
  storage:
    size: 1Gi

This mounts the secret’s tls.crt and tls.key values as actual files at /projected/certificate/tls.crt and /projected/certificate/tls.key inside the pod.

Part 9: Operator-Level Configuration (Cluster-Wide Defaults)

Everything so far has been per-Cluster. But you can also set cluster-wide defaults that apply to every PostgreSQL cluster the operator manages — useful for things like inherited labels, default images, or spreading out rolling upgrades.

Step 9.1: Create the Operator ConfigMap

kubectl apply -f - <<EOF
apiVersion: v1
kind: ConfigMap
metadata:
  name: postgresql-operator-controller-manager-config
  namespace: cnpg-system
data:
  INHERITED_LABELS: "environment, team, app"
  INHERITED_ANNOTATIONS: "cost-center"
  CLUSTERS_ROLLOUT_DELAY: "60"
  INSTANCES_ROLLOUT_DELAY: "10"
EOF

Setting	What it does
`INHERITED_LABELS`	Labels on the Cluster resource that get copied to every generated pod/service
`INHERITED_ANNOTATIONS`	Same, but for annotations
`CLUSTERS_ROLLOUT_DELAY`	Seconds to wait between rolling out different clusters during an operator upgrade
`INSTANCES_ROLLOUT_DELAY`	Seconds to wait between rolling out instances within the same cluster

Step 9.2: Restart the Operator to Apply

Config changes require a restart to take effect, and only apply to new clusters created after the reload:

kubectl rollout restart deployment \
  -n cnpg-system \
  cnpg-controller-manager

Common Errors and Fixes

Error 1: “unknown field” validation error on apply

Symptom:

error: error validating "cluster-example.yaml": error validating data:
ValidationError(Cluster.spec.postgresql.parameters.port): must not be set

Cause: You tried to set a fixed/reserved parameter (like port, ssl, or archive_command) directly.

Fix: Remove the fixed parameter from your YAML — check the fixed parameters list in Part 5. The operator intentionally blocks these via a validating webhook.

Error 2: shared_preload_libraries changes don’t seem to apply

Symptom: You added pg_stat_statements.max but querying pg_stat_statements fails with “relation does not exist.”

Cause: Adding a new managed library requires a restart, not just a reload — and the extension itself needs to be created in each database.

Fix:

# Confirm a rolling restart actually happened
kubectl cnpg status cluster-example
# Look for recent pod recreation timestamps

# If stuck, force a restart
kubectl cnpg restart cluster-example

# Then verify extension exists
kubectl cnpg psql cluster-example

\dx pg_stat_statements

Error 3: ALTER SYSTEM fails with “Permission denied”

Symptom:

ALTER SYSTEM SET work_mem = '16MB';
ERROR: could not open file "postgresql.auto.conf": Permission denied

Cause: This is expected behavior. enableAlterSystem defaults to false — CloudNativePG intentionally blocks ALTER SYSTEM.

Fix (only if you really need it):

spec:
  postgresql:
    enableAlterSystem: true

⚠️ Even with this enabled, changes via ALTER SYSTEM are not replicated to other instances and are not tracked in your YAML — use the declarative parameters section instead whenever possible.

Key Takeaways

✅ CloudNativePG configuration is 100% declarative — you edit the Cluster YAML, never postgresql.conf directly. This guarantees every instance in the cluster has identical configuration.

✅ Parameters merge in a fixed order: global defaults → version-specific defaults → your settings → fixed operator-controlled parameters (which always win and can’t be overridden).

✅ wal_level defaults to logical (not PostgreSQL’s usual replica) — set it to replica explicitly if you don’t need logical replication, to reduce WAL overhead.

✅ Four extensions — pg_stat_statements, auto_explain, pgaudit, pg_failover_slots — are auto-managed. Just add a parameter with the matching prefix and the operator wires up shared_preload_libraries and CREATE EXTENSION automatically.

✅ ALTER SYSTEM is disabled by default and should stay that way — it breaks the Infrastructure-as-Code guarantee that makes CloudNativePG reliable.

Test Your Knowledge

Ready to test what you’ve learned? Take the free quiz:

👉 PostgreSQL Configuration Quiz → gradeupnow.in/postgres-queryplan-quiz/

20 questions · Instant feedback · Detailed explanations · Free

What’s Next

This post is part of the PostgreSQL on Kubernetes (CloudNativePG) series:

#	Post	Status
1	CloudNativePG Architecture on AWS EKS	✅ Published
2	Installing CloudNativePG on AWS EKS — Step by Step	✅ Published
3	PostgreSQL on Kind in WSL Ubuntu with Grafana Monitoring	✅ Published
4	PostgreSQL Configuration & Pod Tuning on Kubernetes	📍 You are here
5	Bootstrap Methods — initdb, Recovery, pg_basebackup	⬜ Coming next week

In Post 5, we cover cluster bootstrapping in depth — the different ways to initialize a new PostgreSQL cluster: fresh initdb, restoring from an existing backup, or cloning via pg_basebackup from an external server.

👉 [Next Post: Bootstrap Methods — initdb, Recovery, pg_basebackup → coming next week]

References

Found this helpful? Share it with your DBA team! Questions? Drop them in the comments below.

Screenshot guide: 5 screenshot placeholders in this post. The most valuable ones for readers: the SHOW shared_buffers confirmation (Step 3.2), the generated custom.conf (Step 3.3), and the pg_stat_statements query results (Step 7.1).