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

   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

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

3. Verify that the settings are applied:

   SQL Copy

   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

   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

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

   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

   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

   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.