Merge pull request DataDog#16072 from DataDog/zach.smith/dbm-troubleshooting-pg-stat-statements-wrong-search-path

maycmlee · web-flow · commit febf04d2c786 · 2022-11-30T10:16:14.000-05:00
[DBM] Update troubleshooting pg_stat_statements not in search_path
diff --git a/content/en/database_monitoring/setup_postgres/troubleshooting.md b/content/en/database_monitoring/setup_postgres/troubleshooting.md
@@ -97,6 +97,24 @@ psql -h localhost -U datadog -d postgres -c "select * from pg_stat_statements LI
 
 If you specified a `dbname` other than the default `postgres` in your Agent config, you must run `CREATE EXTENSION pg_stat_statements` in that database.
 
+If you created the extension in your target database and you still see this warning, the extension may have been created in a schema that is not accessible to the `datadog` user. To verify this, run this command to check which schema `pg_stat_statements` was created in:
+
+```bash
+psql -h localhost -U datadog -d postgres -c "select nspname from pg_extension, pg_namespace where extname = 'pg_stat_statements' and pg_extension.extnamespace = pg_namespace.oid;"
+```
+
+Then, run this command to check which schemas are visible to the `datadog` user:
+
+```bash
+psql -h localhost -U datadog -d <your_database> -c "show search_path;"
+```
+
+If you do not see the `pg_stat_statements` schema in the `datadog` user's `search_path`, you need to add it to the `datadog` user. For example:
+
+```sql
+ALTER ROLE datadog SET search_path = "$user",public,schema_with_pg_stat_statements;
+```
+
 ### Certain queries are missing
 
 If you have data from some queries, but do not see a particular query or set of queries in Database Monitoring that you're expecting to see , follow this guide.
@@ -239,4 +257,4 @@ For more information, see the appropriate version of the [Postgres `contrib` doc
 [21]: https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
 [22]: https://www.postgresql.org/docs/12/contrib.html
 [23]: https://github.com/DataDog/integrations-core/blob/master/postgres/datadog_checks/postgres/data/conf.yaml.example#L281
-[24]: https://pkg.go.dev/github.com/jackc/pgx/v4#QuerySimpleProtocol
+[24]: https://pkg.go.dev/github.com/jackc/pgx/v4#QuerySimpleProtocol
