Step 1: Create a Monitoring Role

This guide assumes you already have a CNPG cluster running, named my-cluster, with its manifest in a file called cluster.yaml. Substitute your own cluster name and file throughout.

CloudNativePG (CNPG) lets you manage Postgres roles declaratively through the Cluster resource, instead of connecting and running CREATE USER yourself. We recommend using this to create a dedicated monitoring role for pganalyze.

Create a Secret for the role’s password

Unlike most declarative role management in CNPG (and unlike the SQL-based monitoring user setup used for other platforms in these docs), CNPG does not generate a password for you when you declare a role in the Cluster spec. You need to create a Kubernetes Secret with the credentials yourself, and reference it from the role.

Save the following as pganalyze-monitoring-secret.yaml. We’ve generated a random password for you below, but you can replace it with one of your choosing:

apiVersion: v1
kind: Secret
metadata:
  name: pganalyze-monitoring-user
  namespace: default # use the same namespace as your Cluster
type: kubernetes.io/basic-auth
stringData:
  username: pganalyze
  password: mypassword

Apply it with kubectl apply -f pganalyze-monitoring-secret.yaml, or create it inline:

kubectl create secret generic pganalyze-monitoring-user \
  --namespace default \
  --type kubernetes.io/basic-auth \
  --from-literal=username=pganalyze \
  --from-literal=password=mypassword

Declare the role on your Cluster

Add the role to .spec.managed.roles in cluster.yaml, referencing the Secret you just created, and granting it the built-in pg_monitor role (which covers most of what the collector needs to read).

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: my-cluster
spec:
  managed:
    roles:
      - name: pganalyze
        ensure: present
        login: true
        connectionLimit: 5
        inRoles:
          - pg_monitor
        passwordSecret:
          name: pganalyze-monitoring-user

Apply this change with kubectl apply -f cluster.yaml. CNPG’s operator picks it up and creates the role on the primary automatically. No restart is needed for a role change like this.

Add helper functions for column and extended statistics

pg_monitor covers query and activity statistics, but pganalyze also collects some additional column and extended statistics through a small set of SECURITY DEFINER helper functions, which still need to be created with plain SQL as a superuser. The easiest way to get a superuser SQL prompt against a CNPG cluster is the cnpg kubectl plugin:

kubectl cnpg psql my-cluster

This connects you directly to the primary as the postgres superuser over a local socket inside the pod, so it works even if you haven’t enabled network-level superuser access for the cluster.

Then, connect to each database that you plan to monitor on this server as a superuser (or equivalent) and run the following to enable the collection of additional column statistics and extended statistics:

CREATE SCHEMA IF NOT EXISTS pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO pganalyze;


DROP FUNCTION IF EXISTS pganalyze.get_column_stats;
CREATE FUNCTION pganalyze.get_column_stats() RETURNS TABLE(
  schemaname name, tablename name, attname name, inherited bool, null_frac real, avg_width int, n_distinct real, correlation real
) AS $$
  /* pganalyze-collector */
  SELECT schemaname, tablename, attname, inherited, null_frac, avg_width, n_distinct, correlation
  FROM pg_catalog.pg_stats
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND tablename <> 'pg_subscription';
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

DROP FUNCTION IF EXISTS pganalyze.get_relation_stats_ext;
CREATE FUNCTION pganalyze.get_relation_stats_ext() RETURNS TABLE(
  statistics_schemaname text, statistics_name text,
  inherited boolean, n_distinct pg_ndistinct, dependencies pg_dependencies,
  most_common_val_nulls boolean[], most_common_freqs float8[], most_common_base_freqs float8[]
) AS
$$
  /* pganalyze-collector */ SELECT statistics_schemaname::text, statistics_name::text,
  (row_to_json(se.*)::jsonb ->> 'inherited')::boolean AS inherited, n_distinct, dependencies,
  most_common_val_nulls, most_common_freqs, most_common_base_freqs
  FROM pg_catalog.pg_stats_ext se
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND tablename <> 'pg_subscription';
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

Note: We never collect actual table data through this method (see the NULL values in the get_column_stats function and omitted fields like most_common_vals in the get_relation_stats_ext function), but we do collect statistics about the distribution of values in your tables. You can skip creating the get_column_stats and get_relation_stats_ext helper functions if the database contains highly sensitive information and statistics about it should not be collected. This will impact the accuracy of Index Advisor recommendations.

Verify connection

CNPG creates a <cluster-name>-rw Service that always points at the current primary. That’s what the collector should connect to. To verify the role can connect from outside the cluster, forward a local port to it:

kubectl port-forward svc/my-cluster-rw 5432:5432

Then, in another terminal:

PGPASSWORD=mypassword psql -h localhost -d app -U pganalyze

Write down the username and password of the monitoring role. We’ll use it in Step 3.

Next we continue by enabling pg_stat_statements: