Step 2: Enable auto_explain

Set the parameter

Add auto_explain.* parameters to .spec.postgresql.parameters in your Cluster manifest (the full recommended set is in the next step). This is the same .spec.postgresql.parameters map you set pg_stat_statements.* in during Initial Setup, so merge these in alongside those keys rather than replacing the map:

spec:
  postgresql:
    parameters:
      pg_stat_statements.max: "10000" # already set, keep it
      pg_stat_statements.track: "all" # already set, keep it
      track_io_timing: "on" # already set, keep it
      auto_explain.log_min_duration: "1000"

You typically don’t need to edit shared_preload_libraries yourself. CNPG recognizes the auto_explain.* parameter prefix and automatically adds the auto_explain library to shared_preload_libraries for you.

Apply the change with kubectl apply -f cluster.yaml.

Restart

Enabling auto_explain requires a Postgres restart, since it changes shared_preload_libraries. Unlike most other platforms in these docs, CNPG restarts the cluster for you automatically by default: with primaryUpdateStrategy: unsupervised (the default), the operator restarts replicas first, then performs a switchover and restarts the former primary. No manual restart step is needed.

If this cluster uses primaryUpdateStrategy: supervised, you’ll need to approve the primary’s restart yourself with kubectl cnpg restart my-cluster after applying the parameter change.

Once the rollout completes, verify that shared_preload_libraries now includes auto_explain and that the setting change no longer requires a restart (pending_restart should be f):

kubectl cnpg psql my-cluster -- -c \
  "SELECT setting, pending_restart FROM pg_settings WHERE name = 'shared_preload_libraries';"

If auto_explain is enabled and no longer requires a restart, you can proceed to review our recommended auto_explain configuration settings: