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.

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

2. Add pg_stat_statements to shared_preload_libraries in your postgresql.conf file and restart PostgreSQL to apply the change.

3. Create the extension in each database you want to monitor:

   SQL Copy

   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

4. Verify the extension is installed:

   SQL Copy

   SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

   Expected result: One row confirming the extension exists.

5. Set pg_stat_statements.track to all in your postgresql.conf file:

   Copy

   pg_stat_statements.track = all

6. Verify the setting:

   SQL Copy

   SHOW pg_stat_statements.track;

   Expected result: Value is all.

7. Increase track_activity_query_size to 4096 in your postgresql.conf file to capture complete query text.

8. Verify the setting:

   SQL Copy

   SHOW track_activity_query_size;

   Expected result: 4kB (4096).

9. Create a dedicated monitoring user and grant the required roles:

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

10. Replace the password with a strong value.

11. Verify that the monitoring user can query pg_stat_statements:

    SQL Copy

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

    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

    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

    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.