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

# Prepare your PostgreSQL database

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

01. To prepare your PostgreSQL database instance for Database Observability, complete the following steps:
02. Add `pg_stat_statements` to `shared_preload_libraries` in your `postgresql.conf` file and restart PostgreSQL to apply the change.
03. 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
    CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
    ```
04. Verify the extension is installed:
    
    SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy
    
    ```sql
    SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';
    ```
    
    Expected result: One row confirming the extension exists.
05. Set `pg_stat_statements.track` to `all` in your `postgresql.conf` file:
    
    ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy
    
    ```none
    pg_stat_statements.track = all
    ```
06. Verify the setting:
    
    SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy
    
    ```sql
    SHOW pg_stat_statements.track;
    ```
    
    Expected result: Value is `all`.
07. Increase `track_activity_query_size` to `4096` in your `postgresql.conf` file to capture complete query text.
08. Verify the setting:
    
    SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy
    
    ```sql
    SHOW track_activity_query_size;
    ```
    
    Expected result: `4kB` (4096).
09. Create a dedicated monitoring user and grant the required roles:
    
    SQL ![Copy code to clipboard](/media/images/icons/icon-copy-small-2.svg) Copy
    
    ```sql
    CREATE USER "db-o11y" WITH PASSWORD '<DB_O11Y_PASSWORD>';
    GRANT pg_monitor TO "db-o11y";
    GRANT pg_read_all_stats TO "db-o11y";
    ```
10. Replace the password with a strong value.
11. 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;
    ```
12. Disable tracking of queries executed by the monitoring user:
    
    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';
    ```
13. 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, 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";
    ```
14. You’ve prepared your PostgreSQL database for Database Observability monitoring.

In the next milestone, you’ll configure Grafana Alloy with the Database Observability components.