Kaseya Community

How to monitor PostgreSQL

  • Good day!

    Can you help with understanding how to find tests for postgresql database. I tried a search on a fresh install of Postgresql on windows, but I didn't find any tests. Do I need additional configuration or agents on Postgresql server?

    Kind regards,

    Airat.

  • probePostgresTests.pdf

    Airat,

    I created a command line utility to list discovered tests on a postgres databases (much like the supplied probeSnmpTests.pl  for SNMP tests).

    You can try to run it (assuming your Traverse installation is on Linux boxes) and see what it returns. Before running please rename the file to probePostgresTests.pl.

    Here's an example run for us:

    -bash-4.1$ ./probePostgresTests.pl --host 10.109.30.142 --username=monitor --dbname=gt2007 --port=5432  --xml=/usr/local/traverse/etc/typedef/90_sql_pgsql_stats.xml
    MONITOR: pg_db_size|Database Size (template1)|kB|100000000|0.0009765625|0|20000|40000|db_name=template1
    MONITOR: pg_db_size|Database Size (template0)|kB|100000000|0.0009765625|0|20000|40000|db_name=template0
    MONITOR: pg_db_size|Database Size (postgres)|kB|100000000|0.0009765625|0|20000|40000|db_name=postgres
    MONITOR: pg_db_size|Database Size (gt2007)|kB|100000000|0.0009765625|0|20000|40000|db_name=gt2007
    MONITOR: pg_conns|Number of Active Connections|connections|100000000|1|0|250|1000|
    MONITOR: pg_locks|Number of AccessShareLocks|locks|100000000|1|0|5000|20000|modname=AccessShareLock
    MONITOR: pg_locks|Number of ExclusiveLocks|locks|100000000|1|0|5000|20000|modname=ExclusiveLock
    MONITOR: pg_heap_blks|Heap Blocks Read|blocks/sec|100000000|1|3|5000|20000|
    MONITOR: pg_heap_blks|Heap Blocks Hit|blocks/sec|100000000|1|3|5000|20000|
    MONITOR: pg_query_count|Number of Active SQL Queries|queries|100000000|1|0|5000|20000|
    MONITOR: pg_long_query|Longest Running SQL Query|seconds|100000000|1|0|600|2000|
    MONITOR: pg_trans_time|Maximum Active Transaction Time|secs|100000000|1|0|5000|20000|
    MONITOR: pg_idle_trans|Maximum Idle Transaction Time|secs|100000000|1|0|5000|20000|
    MONITOR: pg_idx_scans|Number of Index Scans|scans/sec|100000000|1|3|5000|20000|
    MONITOR: pg_commits|Number of Commits (template1)|commits/sec|100000000|1|3|5000|20000|db_name=template1
    MONITOR: pg_commits|Number of Commits (template0)|commits/sec|100000000|1|3|5000|20000|db_name=template0
    MONITOR: pg_commits|Number of Commits (postgres)|commits/sec|100000000|1|3|5000|20000|db_name=postgres
    MONITOR: pg_commits|Number of Commits (gt2007)|commits/sec|100000000|1|3|5000|20000|db_name=gt2007
    MONITOR: pg_rollbacks|Number of Rollbacks (template1)|rollbacks/sec|100000000|1|3|5000|20000|db_name=template1

    Regards,

    Krisztian

  • Hi Krisztian!

    Thank you for your help!

    We corrected pg_hba.conf file to permit conections from remote servers for specific user, because default behavior was to listen for TCP/IP connections only on the local loopback address localhost. As I remember that was enought in our case to tests appear in traverse.

    Thank you again for your help! Some documentation on postgresql could be find here:

    www.postgresql.org/.../auth-pg-hba-conf.html