DOCSEARCHbyAlgolia
Submit Docs

pgMonitor Documentation

repository·development·Indexed 20 days ago

https://github.com/crunchydata/pgmonitor

An all-in-one monitoring solution for visualizing the health and performance of PostgreSQL clusters. pgMonitor integrates the pgMonitor extension, SQL Exporter, Prometheus, and Grafana to track metrics including connection counts, database size, replication lag, transaction wraparound, bloat, and host system metrics (CPU, Memory, I/O, and uptime). It supports RHEL 8/9 and Ubuntu 20/22, and is compatible with community-supported PostgreSQL versions.

Tokens
17K
Snippets
26
Records
67
Agent score
71%

What's inside pgMonitor

  1. Overview of pgMonitor capabilities

    development

    pgMonitor is a tool designed to visualize the health and performance of a PostgreSQL cluster. It provides a suite of tools to collect and visualize metrics related to both the database and the host environment.

    Key metrics monitored include:

    • Connection counts: Monitoring system access and hanging connections.
    • Database size: Tracking disk usage of the cluster.
    • Replication lag: Monitoring if replicas are falling behind the primary.
    • Transaction wraparound: Preventing database downtime due to wraparound.
    • Bloat: Measuring extra space used by tables and indexes.
    • System metrics: Monitoring CPU, Memory, I/O, and uptime.

  2. Overview of pgMonitor

    development

    pgMonitor is an all-in-one monitoring solution designed to visualize the health and performance of PostgreSQL clusters. It combines several open-source tools to collect and visualize metrics such as:

    • Connection counts: Monitoring system access and hanging connections.
    • Database size: Tracking disk usage by the cluster.
    • Replication lag: Monitoring replica synchronization with the primary.
    • Transaction wraparound: Preventing database service interruptions.
    • Bloat: Measuring extra space used by tables and indexes.
    • System metrics: Tracking CPU, Memory, I/O, and uptime.

    Advanced users can extend pgMonitor by designing custom metrics, visualizations, and alerting mechanisms.

  3. Migrate from postgres_exporter to sql_exporter

    development

    As of pgMonitor version 5.0.0, postgres_exporter is deprecated in favor of sql_exporter.

    • Status: Support for postgres_exporter is available in version 5.0 only for bug fixes and custom queries. No new features will be added.
    • Recommendation: Migrate to sql_exporter as soon as possible.
    • Upgrade Path: For detailed instructions on transitioning to sql_exporter when upgrading to version 5.0, refer to the Upgrading to pgMonitor v5.0.0 guide.

  4. Provision Grafana datasources and dashboards

    development

    Grafana can automatically provision datasources and dashboards using configuration files, avoiding manual imports via the UI or API.

    Important Note: Dashboards managed via provisioning cannot be edited or saved directly through the Grafana web interface. To customize a provisioned dashboard, you should first add it via provisioning, then save it with a new name via the UI. Once saved, you can manage it via the web interface or incorporate the changes back into your provisioning files.

    If you are using the extras package, these files are placed automatically. You must ensure the provisioning path is correctly set in grafana.ini to point to the top-level provisioning directory.

    # In grafana.ini
[paths]
provisioning = /etc/grafana/provisioning

  5. Configure Blackbox Exporter for pgMonitor

    development

    pgMonitor includes configuration for the Blackbox exporter to probe TCP ports.

    1. Enable Probes: By default, probes are commented out in /etc/prometheus/crunchy-prometheus.yml. Uncomment them to enable.
    2. Configure Targets: For the default IPv4 TCP port targets, configure your desired monitoring targets under the static_configs: targets section of the blackbox_tcp_services job.
    3. Custom Probes: If you create additional probes, you must create a unique Prometheus job_name for them, specifying the correct params: module name.
    4. Alerting: An example rules file for monitoring Blackbox probes (crunchy-alert-rules-blackbox.yml.example) is provided in the alert-rules.d folder.

  6. Understand the scope of metrics in pgMonitor

    development

    pgMonitor collects metrics from three primary sources depending on what you need to monitor:

    1. PostgreSQL Metrics: Collected via sql_exporter using custom queries. These are split into Global (instance-wide, e.g., connection stats, replication lag, WAL activity), Per-Database (e.g., table sizes, user table stats), and Version-Specific (e.g., checksum failures in PG 12+).
    2. System Metrics:
      • ** NIX Operating System metrics* (Linux, BSD, etc.) are collected using the node_exporter provided by the Prometheus team. pgMonitor uses the default metrics provided by node_exporter.
    3. External Tool Metrics:
      • pgBackRest: Collected for backup monitoring (Full, Diff, Incremental).
      • pgBouncer: Collected via sql_exporter by connecting to pgBouncer directly using specific configuration options.

  7. How pgMonitor works

    development

    pgMonitor functions by integrating a suite of specialized open-source services to create a complete monitoring pipeline:

    1. pgMonitor extension: A PostgreSQL extension that collects metrics from within the database.
    2. SQL Exporter: An exporter for Prometheus that collects metrics from database systems (including PostgreSQL).
    3. Prometheus: A highly customizable metrics collector that scrapes data from exporters.
    4. Grafana: A data visualizer used to generate charts and graphs from the Prometheus data.

  8. Configure multiple databases for postgres_exporter

    development

    Starting with version 4.0, postgres_exporter (minimum version 0.5.1) supports connecting to multiple databases from a single exporter instance.

    Constraints & Best Practices:

    • One query file per service: Although you can connect to multiple databases, only one query file can be set per exporter service via QUERY_FILE_LIST.
    • Per-database metrics: If you need per-database statistics for more than one database, it is recommended to run a second exporter instance. Use a naming convention like sysconfig.postgres_exporter_pg##_per_db and assign a separate custom query file to that instance.
    • Global metrics: Leave the main exporter service to collect global metrics from a single database (preferably postgres).
    • Warning: Do not use the --auto-database-discovery feature, as it may attempt to connect to template databases.

  9. Monitor archive_command status

    development

    To monitor for failing archive_command calls:

    1. Update queries: Add the ccp_archive_command_status metric (located in exporter/postgres/queries_common.yml) to your exporter's main queries file.
    2. Restart exporter: Restart the postgres_exporter.

    Alerting: A new alert rule PGArchiveCommandStatus is available in prometheus/crunchy-alert-rules.yml.

  10. Configure Grafana to use PostgreSQL for storage

    development

    By default, Grafana uses SQLite. For better scalability, it is recommended to use a PostgreSQL database to store configuration and dashboard information.

    To set this up:

    1. Create a dedicated PostgreSQL user and database.
    2. Update your grafana.ini file with the [database] section to point to your PostgreSQL instance.
    3. Ensure pg_hba.conf allows the Grafana user to connect.
    4. Enable and start the grafana-server service.
    5. Access the web interface at https://<ip-address>:3000 (default credentials: admin/admin) and verify the settings.
    -- Run in psql to prepare the database
CREATE ROLE grafana WITH LOGIN;
CREATE DATABASE grafana;
ALTER DATABASE grafana OWNER TO grafana;
\password grafana
    # In grafana.ini
[database]
type = postgres
host = 127.0.0.1:5432
name = grafana
user = grafana
password = """mypassword"""
    # Enable and start the service
sudo systemctl enable grafana-server
sudo systemctl start grafana-server
sudo systemctl status grafana-server

  11. Install pgMonitor exporters manually (Non-Package Install)

    development

    For non-package installations on Linux, download the applications from their respective source repositories:

    ApplicationSource Repository
    blackbox_exporterhttps://github.com/prometheus/blackbox_exporter
    node_exporterhttps://github.com/prometheus/node_exporter
    pg_bloat_checkhttps://github.com/keithf4/pg_bloat_check
    pgmonitor-extensionhttps://github.com/CrunchyData/pgmonitor-extension
    sql_exporterhttps://github.com/burningalchemist/sql_exporter

    User Setup

    You must create a ccp_monitoring user with a home directory at /var/lib/ccp_monitoring:

    sudo useradd -m -d /var/lib/ccp_monitoring ccp_monitoring

    Executable Requirements

    All executables are expected to be installed in the /usr/bin directory. You must also ensure base systemd files are in place for node_exporter and blackbox_exporter.

  12. Install Prometheus and Alertmanager manually (Non-Package Install)

    development

    If you are not using Crunchy Data RPMs, you can install the base services using one of the following methods:

    1. Use the lest/prometheus-rpm repository: This sets up the base service, but you must still manually configure the pgMonitor files as described in the Setup section.
    2. Download binaries directly: Download Prometheus and Alertmanager from prometheus.io/download. Note that this method provides only the binaries and does not include any base service setup.

    Minimum Version Requirements

    • Prometheus 2.49.1
    • Alertmanager 0.26.0

    Manual User and Directory Setup

    1. Create the monitoring user

    Create a system user named ccp_monitoring:

    sudo useradd -m -d /var/lib/ccp_monitoring ccp_monitoring

    2. Set up Prometheus directories

    Create the Prometheus data directory and assign ownership to ccp_monitoring:

    sudo install -d -m 0700 -u ccp_monitoring -g ccp_monitoring /var/lib/ccp_monitoring/prometheus

    3. Set up Alertmanager directories

    Create the Alertmanager data directory and assign ownership to ccp_monitoring:

    sudo install -d -m 0700 -o ccp_monitoring -g ccp_monitoring /var/lib/ccp_monitoring/alertmanager
    sudo useradd -m -d /var/lib/ccp_monitoring ccp_monitoring
sudo install -d -m 0700 -u ccp_monitoring -g ccp_monitoring /var/lib/ccp_monitoring/prometheus
sudo install -d -m 0700 -o ccp_monitoring -g ccp_monitoring /var/lib/ccp_monitoring/alertmanager