Documentationbreadcrumb arrow Grafana Cloudbreadcrumb arrow Monitor applicationsbreadcrumb arrow Database Observabilitybreadcrumb arrow Get startedbreadcrumb arrow Set up PostgreSQL
Grafana Cloud

Set up PostgreSQL

Note

Database Observability is currently in public preview. Grafana Labs offers limited support, and breaking changes might occur prior to the feature being made generally available.

Set up Database Observability with Grafana Cloud to collect metrics from PostgreSQL using Grafana Alloy. You configure your database and Alloy to forward telemetry to Grafana Cloud.

What you’ll achieve

In this article, you:

  • Configure PostgreSQL for monitoring.
  • Run Alloy with the public preview Database Observability components.
  • Forward metrics to Grafana Cloud.

Before you begin

Review these requirements:

  • PostgreSQL version 16.0 or later.

Set up the PostgreSQL database

Prepare PostgreSQL for monitoring and query introspection.

Enable pg_stat_statements

Add pg_stat_statements to shared_preload_libraries in postgresql.conf. Restart PostgreSQL to apply the change.

Create the extension in each database you monitor:

SQL 
-- repeat across all logical databases
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify the extension:

SQL 
-- check across all logical databases
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

Increase track_activity_query_size

Set track_activity_query_size to 4096 and verify the setting:

SQL 
show track_activity_query_size;

Expected result: 4kB (4096).

Create a monitoring user and grant required privileges

Create the db-o11y user and grant base privileges:

SQL 
CREATE USER "db-o11y" WITH PASSWORD '<DB_O11Y_PASSWORD>';
GRANT pg_monitor TO "db-o11y";
GRANT pg_read_all_stats TO "db-o11y";

Replace <DB_O11Y_PASSWORD> with the password for the db-o11y PostgreSQL user.

Verify that the user has the correct privileges to query pg_stat_statements:

SQL 
-- run with the `db-o11y` user
SELECT * FROM pg_stat_statements LIMIT 1;

Grant object privileges for detailed data

Connect to each logical database and grant access to each schema. For example, for a payments database:

SQL 
-- switch to the 'payments' database
\c payments

-- grant permissions in the 'public' schema
GRANT USAGE ON SCHEMA public TO "db-o11y";
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "db-o11y";

-- grant permissions in the 'tests' schema
GRANT USAGE ON SCHEMA tests TO "db-o11y";
GRANT SELECT ON ALL TABLES IN SCHEMA tests TO "db-o11y";

Alternatively, use the predefined role to grant USAGE and SELECT access to all objects:

SQL 
GRANT pg_read_all_data TO "db-o11y";

Run and configure Alloy

Run Alloy and add the Database Observability configuration.

Run the latest Alloy version

Run Alloy version 1.11.0 or later with the --stability.level=experimental flag for the database_observability component. Find the latest stable version on Docker Hub.

Add the PostgreSQL configuration blocks

Add these blocks to Alloy for each PostgreSQL instance. Replace <DB_NAME>. Create a local.file with the Data Source Name string, for example, "postgresql://<DB_USER>:<DB_PASSWORD>@(<DB_HOST>:<DB_PORT>)/<DB_DATABASE>?sslmode=require":

Alloy 
local.file "postgres_secret_<DB_NAME>" {
  filename  = "/var/lib/alloy/postgres_secret_<DB_NAME>"
  is_secret = true
}

prometheus.exporter.postgres "postgres_<DB_NAME>" {
  data_source_names  = local.file.postgres_secret_<DB_NAME>.content
  enabled_collectors = ["stat_statements"]

  autodiscovery {
    enabled = true

    // If running on AWS RDS, exclude the `rdsadmin` database
    database_denylist = ["rdsadmin"]
  }
}

database_observability.postgres "postgres_<DB_NAME>" {
  data_source_name  = local.file.postgres_secret_<DB_NAME>.content
  forward_to        = [loki.relabel.database_observability_postgres_<DB_NAME>.receiver]
  targets           = prometheus.exporter.postgres.postgres_<DB_NAME>.targets
  enable_collectors = ["query_details", "query_samples", "schema_details", "explain_plans"]

  // OPTIONAL: provide additional information specific to the cloud provider
  // that hosts the database to enable certain infrastructure observability
  // features. See documentation of `database_observability.mysql` for
  // other cloud providers.
  cloud_provider {
    aws {
      arn = "<AWS_RDS_DB_ARN>"
    }
  }
}

loki.relabel "database_observability_postgres_<DB_NAME>" {
  forward_to = [loki.write.logs_service.receiver]

  // OPTIONAL: add any additional relabeling rules; must be consistent with rules in "discovery.relabel"
  rule {
    target_label = "instance"
    replacement  = "<INSTANCE_LABEL>"
  }
  rule {
    target_label = "<CUSTOM_LABEL_1>"
    replacement  = "<CUSTOM_VALUE_1>"
  }
}

discovery.relabel "database_observability_postgres_<DB_NAME>" {
  targets = database_observability.postgres.postgres_<DB_NAME>.targets

  rule {
    target_label = "job"
    replacement  = "integrations/db-o11y"
  }

  // OPTIONAL: add any additional relabeling rules; must be consistent with rules in "loki.relabel"
  rule {
    target_label = "instance"
    replacement  = "<INSTANCE_LABEL>"
  }
  rule {
    target_label = "<CUSTOM_LABEL_1>"
    replacement  = "<CUSTOM_VALUE_1>"
  }
}

prometheus.scrape "database_observability_postgres_<DB_NAME>" {
  targets    = discovery.relabel.database_observability_postgres_<DB_NAME>.output
  forward_to = [prometheus.remote_write.metrics_service.receiver]
}

Replace the placeholders:

  • DB_NAME: Database name Alloy uses in component identifiers (appears in component names and secret filenames).
  • AWS_RDS_DB_ARN: AWS RDS database ARN for cloud provider integration (optional).
  • INSTANCE_LABEL: Value that sets the instance label on logs and metrics (optional).
  • CUSTOM_LABEL_1, CUSTOM_VALUE_1: Optional custom label key and value you attach to logs and metrics.
  • Secret file content example: "postgresql://DB_USER:DB_PASSWORD@(DB_HOST:DB_PORT)/DB_DATABASE?sslmode=require".
    • DB_USER: Database user Alloy uses to connect (e.g. db-o11y).
    • DB_PASSWORD: Password for the database user.
    • DB_HOST: Hostname or IP address of the database.
    • DB_PORT: Database port number.
    • DB_DATABASE: Logical database name in the DSN (Recommend: use postgres).

Find more about the options supported by the database_observability.postgres component in the reference documentation.

Add Prometheus and Loki write configuration

Add the Prometheus remote write and Loki write configuration. From Grafana Cloud, open your stack to get the URLs and generate API tokens:

Alloy 
prometheus.remote_write "metrics_service" {
  endpoint {
    url = sys.env("GCLOUD_HOSTED_METRICS_URL")

    basic_auth {
      password = sys.env("GCLOUD_RW_API_KEY")
      username = sys.env("GCLOUD_HOSTED_METRICS_ID")
    }
  }
}

loki.write "logs_service" {
  endpoint {
    url = sys.env("GCLOUD_HOSTED_LOGS_URL")

    basic_auth {
      password = sys.env("GCLOUD_RW_API_KEY")
      username = sys.env("GCLOUD_HOSTED_LOGS_ID")
    }
  }
}

Replace the placeholders:

  • GCLOUD_HOSTED_METRICS_URL: Your Grafana Cloud Prometheus remote write URL.
  • GCLOUD_HOSTED_METRICS_ID: Your Grafana Cloud Prometheus instance ID (username).
  • GCLOUD_HOSTED_LOGS_URL: Your Grafana Cloud Loki write URL.
  • GCLOUD_HOSTED_LOGS_ID: Your Grafana Cloud Loki instance ID (username).
  • GCLOUD_RW_API_KEY: Grafana Cloud API token with write permissions.

Configure the k8s-monitoring Helm chart

Extend your values.yaml when you use the k8s-monitoring Helm chart:

YAML 
alloy:
  image:
    repository: 'grafana/alloy'
    tag: <ALLOY_VERSION> # e.g. "v1.11.0"

  alloy:
    stabilityLevel: experimental

extraConfig: |
  // Add the config blocks for Database Observability
  prometheus.exporter.postgres "postgres_<DB_NAME>" {
    ...
  }
  ...
  database_observability.postgres "postgres_<DB_NAME>" {
    ...
  }
  ...

Replace the placeholders:

  • ALLOY_VERSION: Alloy stable release version you use, for example, v1.11.0.
  • DB_NAME: Database name Alloy uses in component identifiers.

Next steps

For an overview of key concepts, refer to Introduction to Database Observability.