PostgreSQL exporter#

The chart can run postgres_exporter against the PostgreSQL databases a deployment depends on: Materialize’s metadata database and the database behind the bundled Grafana. This page is for the operator who turns it on.

The exporter connects to each database as an ordinary SQL client and reports what the database says about itself. That covers connections against the ceiling, transactions, locks, the size of every database on the instance, transaction-ID age, and vacuum on the tables of the database it connects to. It works the same on RDS, Cloud SQL, Azure Database for PostgreSQL, and a self-managed server.

It complements the two views the stack has without it. Materialize measures every call it makes to the metadata database, which says whether the database is slow, and needs nothing configured. A cloud provider pull sees the machine the database runs on, such as CPU, memory, storage headroom, and burst credits, and nothing inside it. The exporter sees inside the database and not the machine, so it answers why: a stalled autovacuum on persist’s consensus table, a session holding a transaction open, or a neighboring database filling the volume. For the reasoning, see the external dependency design.

How it runs#

One Deployment serves every database. The Alloy gateway scrapes it once per target on /probe, and the exporter opens a connection to that database for the length of the scrape. Each scrape’s series carry the target’s name as target and job="postgres-exporter", including up.

The exporter reads each target’s credentials from a file, postgres_exporter.yml, and from nowhere else. The chart mounts that file from a Secret you create, so no credential passes through Helm values.

Turn it on#

To watch the metadata database and Grafana’s database, follow these steps:

1. Create a role for the exporter on each PostgreSQL instance. Run the following as a role that can grant pg_monitor, such as the administrator role your database service gives you:

   CREATE ROLE mzmon_exporter WITH LOGIN PASSWORD 'PASSWORD' CONNECTION LIMIT 3;
   GRANT pg_monitor TO mzmon_exporter;
   GRANT CONNECT ON DATABASE DATABASE_NAME TO mzmon_exporter;

   Replace the following:

   • PASSWORD: a password for the role.
   • DATABASE_NAME: the database the exporter connects to, such as materialize for the metadata database, or postgres for Grafana’s. For more information, see Choose the database to connect to.

   pg_monitor is a predefined role that reads every statistics view and setting, and it grants no access to table data. A scrape holds one connection, so the connection limit leaves room for an overlapping scrape and nothing more.

2. Create the Secret in the release namespace. It holds one key, postgres_exporter.yml, with one auth_modules entry per target, named after the target:

   auth_modules:
     consensus:
       type: userpass
       userpass:
         username: mzmon_exporter
         password: PASSWORD
       options:
         sslmode: require
     grafana:
       type: userpass
       userpass:
         username: mzmon_exporter
         password: PASSWORD
       options:
         sslmode: require

   kubectl create secret generic postgres-exporter -n monitoring --from-file=postgres_exporter.yml

   Provision it with External Secrets Operator, Vault Agent, SOPS, or your cloud’s CSI driver, so that the password never lands in Git. Each entry under options becomes a connection parameter. For sslmode: verify-full, mount the server’s CA certificate with postgres-exporter.extraVolumes and postgres-exporter.extraVolumeMounts, and point sslrootcert at it.

3. Turn on the exporter, list the targets, and say which one is the metadata database:

   tags:
     postgres-exporter: true
   
   postgres-exporter:
     targets:
       - name: consensus
         host: METADATA_DB_HOST
         database: materialize
       - name: grafana
         host: GRAFANA_DB_HOST
   
   externalDependencies:
     consensus:
       - flavor: postgres
         resourceId: consensus

   Replace METADATA_DB_HOST and GRAFANA_DB_HOST with each database’s host, as a pod in the cluster resolves it. Each target also takes database, which defaults to postgres, port, which defaults to 5432, and authModule, which defaults to the target’s name.

The exporter reads the Secret only when it starts. After you change the Secret, restart the exporter:

kubectl rollout restart deployment/postgres-exporter -n monitoring

The exporter needs network access to each database. It runs in the cluster that already reaches the metadata database, so the path Materialize uses is the one it needs. A database behind a private endpoint, such as a Cloud SQL private IP, is reachable only from networks the endpoint allows.

Turn it on with Terraform#

The materialize-monitoring Terraform module creates the Secret for you. Set enable_postgres_exporter, and the exporter watches Grafana’s database whenever the module configures one, as a target named grafana. List any other database in postgres_exporter_targets, with its password in postgres_exporter_passwords:

module "monitoring" {
  # ...
  enable_postgres_exporter = true

  postgres_exporter_targets = [{
    name              = "consensus"
    host              = METADATA_DB_HOST
    database          = "materialize"
    user              = "mzmon_exporter"
    metadata_database = true
  }]
  postgres_exporter_passwords = {
    consensus = var.metadata_exporter_password
  }
}

Replace METADATA_DB_HOST with the metadata database’s host. metadata_database = true adds the target to externalDependencies.consensus. A database a cloud provider pull watches is declared in metadata_databases instead, by its flavor and the provider’s name for it. The module rolls the exporter when a password changes.

The per-cloud wrappers in materialize-terraform-self-managed connect as the users they already hold: the metadata database’s administrator and Grafana’s database owner. On RDS, Cloud SQL, and Azure Database for PostgreSQL, both hold pg_monitor through the service’s administrator role (rds_superuser, cloudsqlsuperuser, or azure_pg_admin), so the exporter sees every session. Both can also write. For a role that can only read statistics, create mzmon_exporter as described earlier, and pass it to the wrapper instead.

Choose the database to connect to#

Point each target’s database at a database whose tables you want statistics for, and leave it at postgres otherwise. Per-table statistics, the pg_stat_user_tables_* and pg_statio_user_tables_* families, come from that database alone, at about 29 series per table. For the metadata database, that’s where persist’s consensus and timestamp_oracle tables live, so connect to materialize. Grafana’s database holds about 90 tables, so connecting to it costs about 2,600 series that no panel reads. Everything instance-wide, including pg_database_size_bytes, covers every database on the instance whichever one the connection opens. The chart keeps the size collector unscoped, because a neighboring database filling the volume is what per-database sizes exist to attribute.

Grafana’s database#

The bundled Grafana keeps users, preferences, API keys, and annotations in its database when you use the grafana-postgres profile. Its target is scraped like any other, and you can pick it on the dashboard. Leave it out of externalDependencies.consensus, so that it’s never recorded as a metadata database.

What it collects#

The exporter’s default collectors run, plus the following, which the chart turns on:

• --collector.database_wraparound: pg_database_wraparound_age_datfrozenxid_seconds per database, the transaction-ID age that wraparound is measured against. The name ends in _seconds, but it counts transactions.
• --collector.stat_activity_autovacuum: pg_stat_activity_autovacuum_timestamp_seconds, the start time of each autovacuum worker running now, by table.

The families the dashboard reads are in the extended metric importance tier. A destination that keeps only recommended metrics and above drops them. The exporter publishes more families than the dashboard reads, and a destination keeps those only at all.

Where it shows up#

The Database Internals tab of the Materialize Consensus (Metadata) dashboard has a PostgreSQL section. It renders when the exporter is scraping, and its Database picker chooses the target. For more information, see Available Dashboards.

The target you name in externalDependencies.consensus is also recorded into the normalized metadata-database series, with flavor="postgres" and the target’s name as resource. The following table lists the series the exporter adds:

For the rest of the normalized series, see Recorded Series.

Troubleshoot it#

If the PostgreSQL section doesn’t render, check up{job="postgres-exporter"}. No series means nothing scrapes the exporter: check that postgres-exporter.targets isn’t empty and that the Alloy gateway is running.

If a target’s up is 0, the exporter refused the probe. The usual cause is a Secret with no auth_modules entry named after the target or its authModule, which the exporter answers with HTTP status 400.

If up is 1 and pg_up is 0, the exporter can’t connect to the database. The exporter’s log names the target and the error, which is usually one of the following:

• password authentication failed: the entry’s credentials are wrong, or the Secret changed and the exporter wasn’t restarted.
• A timeout: the pod has no network path to the database.

If connection counts look low, the role is likely missing pg_monitor. Without it, PostgreSQL hides the state of other roles’ sessions, and the exporter can’t count them.