DPM reports that pg_stat_statements is not available, but it is enabled

9/8/2020 6:35 PM

9/8/2020 6:35 PM

In order to monitor a PostgreSQL database remotely, the pg_stat_statements module needs to be loaded when the server is started, and the pg_stat_statements extension must be created on the database that the DPM agent is connecting to. A common issue occurs where the module and extension are available, but they have been created where the DPM agent has not been instructed to connect to. A user may be able to query the table successfully, but typically they are connecting to a different location than the agent.

Database Performance Monitor

In order for the agent to read data from pg_stat_statements, the pg_stat_statements view must be added to the database that DPM is connecting to. This is the internal PostgreSQL database, not the host itself. For example, you may have run the CREATE EXTENSION on the reporting database, while DPM is connecting to postgres (which is the default unless another database is specified).

Check the database that has been provided in the host’s Credentials page (if no database is provided, DPM will default to postgres). Within DPM, go to Inventory, and click the edit icon under Settings next to the name of the Postgres database. Then click the "Edit Credentials" tab and verify that the database is correct. You can also read the agent’s log in /var/log/vividcortex, which will contain the connection information at startup. The database provided must be the same database where you ran CREATE EXTENSION pg_stat_statements.

You can also confirm this is the cause by connecting to PostgreSQL using the same username, password, and database that you have provided to the agent and then executing select count(*) from pg_stat_statements.