---
title: "Prepare your PostgreSQL database | Grafana Labs"
description: "Enable pg_stat_statements, configure query tracking settings, create a monitoring user, and grant the privileges Database Observability needs."
---

> For a curated documentation index, see [llms.txt](/llms.txt). For the complete documentation index, see [llms-full.txt](/llms-full.txt).

# Prepare your PostgreSQL database

In this milestone, you’ll prepare your PostgreSQL database for monitoring. This involves enabling the `pg_stat_statements` extension, configuring query tracking settings for complete query capture, creating a dedicated monitoring user, and granting the privileges Alloy needs.

To prepare your PostgreSQL database instance for Database Observability, complete the following steps:

1. Add or update the query tracking settings in your `postgresql.conf` file:
   
   conf ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy
   
   ```conf
   shared_preload_libraries = 'pg_stat_statements'
   compute_query_id = on
   pg_stat_statements.track = all
   track_activity_query_size = 4096
   ```
   
   `shared_preload_libraries`, `compute_query_id`, and `track_activity_query_size` are startup settings. If you change any of them, restart PostgreSQL before continuing.
2. Create the extension in each database you want to monitor:
   
   SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy
   
   ```sql
   -- repeat across all logical databases
   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
   ```
3. Verify that the settings are applied:
   
   SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy
   
   ```sql
   SHOW shared_preload_libraries;
   SELECT extname FROM pg_extension WHERE extname = 'pg_stat_statements';
   SHOW compute_query_id;
   SHOW pg_stat_statements.track;
   SHOW track_activity_query_size;
   ```
   
   Expected results: `shared_preload_libraries` includes `pg_stat_statements`, the extension query returns `pg_stat_statements`, `compute_query_id` is `on`, `pg_stat_statements.track` is `all`, and `track_activity_query_size` is `4096` or `4kB`.
4. Create the `db-o11y` monitoring user and grant its base roles. Replace `YOUR_DB_O11Y_PASSWORD` with a strong password:
   
   SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy
   
   ```sql
   CREATE USER "db-o11y" WITH PASSWORD 'YOUR_DB_O11Y_PASSWORD';
   GRANT pg_monitor TO "db-o11y";
   GRANT pg_read_all_stats TO "db-o11y";
   ```
   
   Alloy connects as this user. If you choose a different user name, use it in every later step.
5. Verify that the monitoring user can query `pg_stat_statements`:
   
   SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy
   
   ```sql
   -- run with the db-o11y user
   SELECT * FROM pg_stat_statements LIMIT 1;
   ```
6. Disable tracking of queries executed by the monitoring user, so Alloy’s own collection queries don’t show up as database workload:
   
   SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy
   
   ```sql
   ALTER ROLE "db-o11y" SET pg_stat_statements.track = 'none';
   ```
7. Grant object-level privileges for schema details and explain plans. Connect to each database and grant access to the schemas you want to monitor:
   
   SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy
   
   ```sql
   GRANT USAGE ON SCHEMA public TO "db-o11y";
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO "db-o11y";
   ```
   
   Alternatively, if you’re unsure which schemas need access, use the predefined role to grant read access to all objects:
   
   SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy
   
   ```sql
   GRANT pg_read_all_data TO "db-o11y";
   ```

You’ve prepared your PostgreSQL database for Database Observability monitoring. `pg_stat_statements` tracks full query text with stable query IDs, and the `db-o11y` user can read the data Alloy needs.

In the next milestone, you’ll generate the Grafana Alloy configuration for this database and deploy it.