Documentation Grafana Cloud Monitor applications Database Observability Get started Set up PostgreSQL

Set up PostgreSQL

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 Copy

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify the extension:

SQL Copy

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 Copy

show track_activity_query_size;

 track_activity_query_size
---------------------------
 4kB

Create a monitoring user and grant required privileges

Create the db-o11y user and grant base privileges:

SQL Copy

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 Copy

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

Grant object privileges for detailed data

Connect to a database, for example, payments, and grant schema access:

SQL Copy

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

-- grant USAGE and SELECT 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 USAGE and SELECT 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 access to all objects:

SQL Copy

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 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":

Copy

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

prometheus.exporter.postgres "integrations_postgres_exporter_<DB_NAME>" {
  data_source_name  = 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.integrations_postgres_exporter_<DB_NAME>.targets

  // OPTIONAL: enable collecting samples of queries with their execution metrics. The SQL text is redacted to hide sensitive params.
  enable_collectors = ["query_samples", "query_details"]

  // OPTIONAL: if `query_samples` collector is enabled, you can use
  // the following setting to disable SQL text redaction (by default
  // query samples are redacted).
  query_samples {
    disable_query_redaction = true
  }

  // OPTIONAL: provide additional information specific to the Cloud Provider
  // that hosts the database to enable certain infrastructure observability features.
  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 = concat(prometheus.exporter.postgres.integrations_postgres_exporter_<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
  job_name   = "integrations/db-o11y"
  forward_to = [prometheus.remote_write.metrics_service.receiver]
}

Replace the placeholders:

• DB_NAME: Logical 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.
  • DB_PASSWORD: Password for the database user.
  • DB_HOST: Hostname or IP address of the database.
  • DB_PORT: Database port number.
  • DB_DATABASE: Database name in the DSN.

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:

Copy

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.

Next steps

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