Prerequisites for monitoring
This section provides the prerequisite steps for monitoring.
To enable the pg_stat_statements extention in the database
- Modify the postgresql.conf file and add following properties:
postgresql.conf shared_preload_libraries = 'pg_stat_statements'pg_stat_statements.max = 10000pg_stat_statements.track = all - Save the file and restart the PostgreSQL server.
- Create and enable the pg_stat_statements extention in the database by using the command:
If you do not enable the pg_stat_statements extension for a database, that database is shown offline and an individual SQL container is not created for the TopWaitTimeSQL instance.
Monitoring users permissions
This section provides the user permissions that you need to assign to the users that you would use while configuring monitoring of your PostgreSQL database.
Database users
If you are using PostgreSQL database 9.x version, the monitoring user needs read permission to the following tables:
- pg_database
- pg_tablespace
- pg_locks
- pg_stat_user_tables
- pg_stat_activity
- pg_stat_database
If you are using PostgreSQL database 10.x version and above, use the following commands to assign these permissions:
- GRANT pg_monitor TO <monitoring user>;
- GRANT CONNECT ON DATABASE <database name> TO <monitoring user>;
For more information, see PostgreSQL documentation.
Operating system users
To monitor tablespaces locally, ensure that the PATROL Agent user has read access to the filesystem path and to execute the df -k command on the tablespaces filesystems.
If you are monitoring tablespaces remotely, ensure that the user that you enter while configuring monitoring of your database in TrueSight (in the Remote OS Connection details field) has read access to the filesystem path and to execute the df -k command on the tablespaces filesystems.
Tablespace paths are determine by using the following query:
SELECT CASE WHEN pg_tablespace_location(oid)='' AND spcname='pg_default' THEN current_setting('data_directory')||'/base/' WHEN pg_tablespace_location(oid)='' AND spcname='pg_global' THEN current_setting('data_directory')||'/global/' ELSE pg_tablespace_location(oid) END AS spclocation, spcname FROM pg_tablespace