How do a monitor PostgreSQL database performance

How do a monitor PostgreSQL database performance using the PostgreSQL sensor.

Note Verify you have enabled PostgreSQL's statistics collector on your database server!

postgresql.conf

track_activities = on
track_counts = on
track_io_timing = off
track_functions = none
track_activity_query_size = 1024

mor information on enabling the stat collection is available at the following URL: https://www.postgresql.org/docs/current/static/monitoring-stats.html

1. Create your SQL file

C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\postgresql\pg_stat_database.sql

-- pulls statistics for database passed via PRTG parameter
-- SELECT * FROM pg_stat_database WHERE datname = 'DB' ;
-- datid | datname | numbackends | xact_commit | xact_rollback | blks_read  |   blks_hit    |  tup_returned   | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes  | deadlocks | blk_read_time | blk_write_time |          stats_reset
---------+---------+-------------+-------------+---------------+------------+---------------+-----------------+-------------+--------------+-------------+-------------+-----------+------------+-------------+-----------+---------------+----------------+-------------------------------
-- 16384 | DB      |          11 |  4383088207 |     489151237 | 2567416980 | 4052249441946 | 191586241749080 |   262087158 |   2136300959 |      236563 |       71018 |         0 |        168 | 11429036032 |         0 |             0 |              0 | 2016-05-05 20:31:38.397496+00
SELECT * FROM pg_stat_database WHERE datname = @prtg

2. Restart the PRTG service so that you can choose your new custom sql code

3. Choose the parameters you would like to monitor and create your sensor

BASIC SENSOR SETTINGS

DATABASE SPECIFIC

DATA

SENSOR DISPLAY

Further

I choose to monitor the number of inserts, you can add or modify the "Sensor Channel" to watch any value returned by pg_stat_database.

A description of the stats available from pg_stat_database is available from the following URL https://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW

performance postgres postgresql sql