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

Sensor Name

pg_stat_database

DATABASE SPECIFIC

Database

postgres

DATA

SQL Query File	pg_stat_database.sql
Use Input Parameter	Use input parameter
Input Parameter	CHANGE_ME_DB
Data Processing	Process data table
Select Channel Value by	Column name
Sensor Channel #1 Name	tup_inserted
Sensor Channel #1 Column Name	tup_inserted
Sensor Channel #1 Mode	Difference
Sensor Channel #1 Unit	Count
Sensor Channel #2 Name	numbackends
Sensor Channel #2 Column Name	numbackends
Sensor Channel #2 Mode	Absolute (recommended)
Sensor Channel #2 Unit	Count

SENSOR DISPLAY

Primary Channel

tup_inserted (#/s)

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