SQL statements are not collected the PostgreSQL agent (4226972)

SQL statements are not collected the PostgreSQL agent

No statements populated on the PostgreSQL statements dashboard data for PostgreSQL.

Error messages like "ERROR: relation "pg_stat_statements" does not exist" may appear in the PostgreSQL agent log file.

CAUSE 1

If statement tracking has been configured in the agent properties properly, the issue is most likely caused by the pg_stat_statements module not being set up.

CAUSE 2

There was an issue where on Postgres 13 pg_stat_statements was modified thus needs to be upgraded when upgrading the Postgres version.

CAUSE 3

Extension created in a different database than the one provided in the agent properties.
 

RESOLUTION 1

Installation of the pg_stat_statements module and creation of the extension in a database is required for statement tracking. Basic steps follow below. For more information on how to install this, refer to the relevant PostgreSQL documentation for your version

1. Confirm that statement tracking is turned on in the agent properties
2. In the configuration file, set the below sample properties. The pg_stat_statements module allows more advanced configuration, but these are the minimum required.

   shared_preload_libraries = 'pg_stat_statements'
   pg_stat_statements.max = 1000
   pg_stat_statements.track = all

   More info on properties: https://www.postgresql.org/docs/current/static/pgstatstatements.html

3. Restart the server
4. In PSQL, pgAdmin, or another client program, execute this statement when connected to any database (postgres is fine):

   CREATE EXTENSION pg_stat_statements;

5. When configuring the Foglight for PostgreSQL agent properties, make sure to enable statement tracking and provide the database name where the extension was created (DB w/ pg_stat_statements view).
6. Confirm that the pg_stat_statements view is present on the PostgreSQL server in the configured database.

   If the view is present and the statements do not display, try recreating the view in the database and ensure that the Foglight user has access to the view.

   GRANT pg_monitor TO USERNAME;

   GRANT pg_read_all_settings TO USERNAME;

   GRANT pg_read_all_stats TO USERNAME;

   GRANT pg_stat_scan_tables TO USERNAME;

7. If the user still cannot find pg_stat_statements, then check which schema the extension was created in. Usually it is installed in the pg_catalog schema.

   SELECT extname, extnamespace::regnamespace AS schema_name
   FROM pg_extension
   WHERE extname = 'pg_stat_statements';

   
   Next, check that the same schema is included user's search path

   SHOW search_path;
   
   and and add schema to the user's search path if necessary, for example to add the public schema it would look like this:
   

   ALTER ROLE USERNAME SET search_path = USERNAME, public;

   
   
   

RESOLUTION 2

Run the following command in each database where pg_stat_statements has been installed.

ALTER EXTENSION pg_stat_statements UPDATE

Source: Foglight for PostgreSQL Installation and Troubleshooting guide.