How-to monitor PostgreSQL

Owned by Matthew Hierholzer
Last updated: Dec 11, 2023

This article helps explain how to configure PostgreSQL with Checkmk via the Agent Bakery.

LAST TESTED ON CHECKMK 2.1.0P1


Table of Contents



Deploy Plugin to PostgreSQL-Server


Single instance

  1. Create a .env file for instance-settings (name should always be the same as the instance)

    root@myhost:~# vi /etc/check_mk/main.env

    .

    Supply the following
    • PGDATABASE
    • PGPORT
    • PGVERSION

    e.g.,

    /etc/check_mk/main.env
    PGDATABASE=postgres
PGPORT=5432
PGVERSION=15

    .

  2. Create a .pgpass file with the password of the User configured in the .env-file (PGDATABASE)

    root@myhost:~# vi /etc/check_mk/main.pgpass

    .

    Example .pgpass file
    /etc/check_mk/main.pgpass
    hostname:port:database:username:password

    .

    Besides the password, you can use wildcards ("*") for every option.

    The agent looks for all databases, so create a line for every database in your instance or use * instead of a database name.


    e.g.,

    /etc/check_mk/main.pgpass
    *:*:*:*:password

    .

    .pgpass-file should only have read/write rights for the root user: no group or others.

    .

  3. Configure the GUI

    Setup → Agents → Windows, Linux, Solaris, AIX → Agent rules → PostgreSQL database and sessions (Linux, Windows) → Add rule
    Screenshot of new rule added for PostgresSQL.
    .

    Provide the following
    • DB username
    • Path to .env file
    • Instance username
    • Path to .pgpass file

    .

  4. Click Save
    .
  5. Activate changes
    .
  6. Bake and sign agents

Multiple Instances

  1. Create a .env file for instance-settings 

    root@myhost:~# vi /etc/check_mk/main.env

    The name should always be the same as the instance.

    .

    Supply the following
    • PGDATABASE
    • PGPORT
    • PGVERSION

    e.g.,

    /etc/check_mk/main.env
    PGDATABASE=postgres
PGPORT=5432
PGVERSION=15

    .

  2. Create a .env file for any additional instances

    root@myhost:~# vi /etc/check_mk/secondinst.env

    .

    Supply the following
    • PGDATABASE
    • PGPORT
    • PGVERSION

    e.g.,

    /etc/check_mk/secondinst.env
    PGDATABASE=postgres_second
PGPORT=5433
PGVERSION=15

    Ensure that the port numbers and names are unique for each instance.

    .

  3. Create a .pgpass file with the password of the User configured in the .env-file (PGDATABASE) for each instance

    root@myhost:~# vi /etc/check_mk/main.pgpass

root@myhost:~# vi /etc/check_mk/secondinst.pgpass
    Example .pgpass file
    /etc/check_mk/main.pgpass
    hostname:port:database:username:password

    .

    Besides the password, you can use wildcards ("*") for every option.

    The agent looks for all databases, so create a line for every database in your instance or use * instead of a database name.


    e.g.,

    /etc/check_mk/main.pgpass
    *:*:*:*:password

    .

    .pgpass-file should only have read/write rights for the root user: no group or others.

    .

  4. Configure the GUI

    Setup → Agents → Windows, Linux, Solaris, AIX → Agent rules → PostgreSQL database and sessions (Linux, Windows) → Add rule
    Screenshot of new rule added for PostgresSQL.
    .

    Provide the following for each instance
    • DB username
    • Path to .env file
    • Instance username
    • Path to .pgpass file

    .

  5. Click Save
    .
  6. Activate changes
    .
  7. Bake and sign agents


Debugging

  • A simple test if the connection is up (The Script is using your settings, saved in /etc/check_mk/postgres.cfg):

    Linux
    root@myhost:~# /usr/lib/check_mk_agent/plugins/mk_postgres.py -t
    Windows
    C:\ProgramData\checkmk\agent\modules\python-3\.venv\Scripts\python.exe C:\ProgramData\checkmk\agent\plugins\mk_postgres.py -t

    .

    Only the first one is tested if you have configured more than one instance.

    .

  • Run /usr/lib/check_mk_agent/plugins/mk_postgres.py on Agent Host

    /usr/lib/check_mk_agent/plugins/mk_postgres.py
    root@myhost:~# /usr/lib/check_mk_agent/plugins/mk_postgres.py -vv
    Windows
    C:\ProgramData\checkmk\agent\modules\python-3\.venv\Scripts\python.exe C:\ProgramData\checkmk\agent\plugins\mk_postgres.py -vv

    .

    Errors

    Errors are in the first lines before the output.


    You will get a DEBUG-Message if paths to conf, .env, or .pgpass-file are wrong or access to files isn't possible.


    psql-errors are displayed even without verbose-option. e.g.:

    • connection to server on socket " failed: No such file or directory"  → if the wrong port is configured or port is not accessible
    • connection to server on socket " failed: FATAL: database does not exist"  → if the wrong User is configured