DOCSEARCHbyAlgolia
Submit Docs

pgwatch Documentation

repository·master·Indexed 21 days ago

https://github.com/cybertec-postgresql/pgwatch

A scalable, PostgreSQL-specific monitoring solution that provides performance and health metrics through Grafana dashboards. It features a Go-based metrics collector that supports multiple storage strategies, including a standard metric-time schema and TimescaleDB for high-volume data and compression. The tool includes a CLI for configuration and metric management, an administrative WebUI, and support for BRIN indexes to optimize storage overhead.

Tokens
33.3K
Snippets
108
Records
172
Agent score
73%

What's inside pgwatch

  1. Overview of pgwatch monitoring capabilities

    master

    pgwatch is a scalable, PostgreSQL-specific monitoring solution designed to provide a comprehensive view of database performance and health. It monitors core PostgreSQL instances and the surrounding ecosystem, including:

    • PostgreSQL Databases: Core performance and health metrics.
    • Patroni Clusters: Health and performance of high-availability cluster members.
    • Connection Poolers: Insights into PgPool and PgBouncer.
    • Backup solutions: Performance and status tracking for PgBackRest and WAL-G.

    Metrics collected include database health checks, query performance, index usage, disk I/O, CPU/memory consumption, and locks/waits.

  2. Key features of pgwatch

    master

    pgwatch is a PostgreSQL monitoring solution designed for low-impact, high-extensibility observability. Key capabilities include:

    • Non-invasive Setup: Does not require PostgreSQL extensions or superuser rights for base functionality, allowing unprivileged users to monitor database activities.
    • Extensive Metric Coverage: Includes preset configurations for PostgreSQL Statistics Collector data and allows for custom metrics defined in pure SQL (including business-domain metrics).
    • Flexible Storage: Supports multiple backends including PostgreSQL, PostgreSQL with TimescaleDB (for compression), Prometheus scraping, or custom gRPC-based storage.
    • Deployment & Configuration: Supports multiple configuration methods (PostgreSQL DB, YAML, or Environment Variables) and is Kubernetes/OpenShift ready via Helm charts.
    • Broad Ecosystem Support: Includes automatic member discovery for PgBouncer, Pgpool2, AWS RDS, and Patroni.
    • Advanced Monitoring: Beyond standard metrics, it supports log parsing for error detection and OS-level metrics collection via PL/Python helper stored procedures.
    • Security & Management: Features built-in SSL support, password encryption for connection strings, and an internal REST API to monitor metrics gathering status remotely.

  4. Use the pgwatch Web User Interface (WebUI)

    master

    The pgwatch Web User Interface (WebUI) provides a centralized dashboard to manage your monitoring setup. Through the WebUI, you can:

    • Manage Sources: Control and interact with the monitored database sources.
    • Manage Metrics: View and update metric definitions.
    • Manage Presets: Update preset definitions used for monitoring configurations.
    • View Logs: Inspect system and application logs for troubleshooting.

  5. Use the pgwatch Web UI for administration

    master

    The pgwatch Web UI is an optional component used for managing the monitoring setup. It allows you to:

    • Administer monitoring details (which databases to monitor, which metrics to use, and polling intervals).
    • View basic overview tables to analyze gathered data.
    • Delete unneeded metric data (e.g., when decommissioning a test host).

  6. How pgwatch stores data and visualizes metrics

    master

    pgwatch uses a decoupled architecture for data storage and visualization:

    Data Storage (Sinks)

    Users can choose from several sinks (storage backends) to store monitoring data. Supported sinks include:

    • JSON file
    • TimescaleDB
    • Prometheus
    • PostgreSQL
    • Custom gRPC-based backends

    Visualization (User Interface)

    Monitoring data is visualized through Grafana dashboards. These dashboards allow for real-time performance viewing, historical data drill-downs, and custom dashboard configurations.

  7. Understand the pgwatch architecture

    master

    pgwatch is a PostgreSQL monitoring solution composed of four main components:

    1. Metrics collector: The pgwatch daemon that gathers metrics from your databases.
    2. Configuration store: Defines which databases to monitor and their settings. Supported types are a PostgreSQL database or a YAML file.
    3. Metrics storage (Sink): Where collected metrics are stored. Supported types include PostgreSQL, Prometheus, a custom gRPC server, or a JSON file.
    4. Visualization: Grafana dashboards used for analyzing the collected metrics.

  8. What are metrics and presets in pgwatch?

    master

    Metrics

    Metrics are named SQL queries that return a timestamp and other useful data. pgwatch automatically selects the correct version of a metric by checking the target database's PostgreSQL version, recovery state, and whether the monitoring user is a superuser.

    Requirements for metric queries:

    • Timestamp: Every query should include an epoch_ns column (nanoseconds since epoch) to record the exact reading time. If omitted, the daemon's server timestamp is used, which may result in slight precision loss.
    • Data Types: Queries must return only text, integer, boolean, or floating point (double precision) types. Note: Columns with NULL values are not stored.
    • Performance: Queries must execute quickly, ideally within the statement_timeout (default is 5 seconds).
    • Indexing: You can "tag" columns for faster querying and auto-discovery in Grafana by prefixing the column name with tag_ (e.g., tag_my_column).

    Presets

    Presets are named collections of metric_name: time interval pairs. They allow you to define a set of metrics once and reuse them across multiple monitoring targets for consistency.

    -- a sample metric
SELECT
  (extract(epoch from now()) * 1e9)::int8 as epoch_ns,
  extract(epoch from (now() - pg_postmaster_start_time()))::int8 as postmaster_uptime_s,
  case when pg_is_in_recovery() then 1 else 0 end as in_recovery_int;

  9. Protect sensitive query data in dashboards

    master

    Dashboards utilizing the stat_statements metric (such as 'Stat Statement Overview' or 'Top') may expose actual SQL queries. While PostgreSQL attempts to strip details and replace them with placeholders, users with high security requirements should consider the following:

    • Delete sensitive panels: Remove dashboards or specific panels that expose query text.
    • Use alternative metrics: Use stat_statements_no_query_text or pg_stat_statements_calls instead, as these metrics do not store query text in the first place.

  10. Understand pgwatch source types

    master

    When configuring a new monitoring entry, choose the appropriate source type based on what you want to monitor:

    • postgres: Monitors a single specific database on a single instance. Monitoring is performed per-database.
    • postgres-continuous-discovery: Scans a cluster/instance and automatically adds new databases found. Supports regex for including/excluding specific database names.
    • pgbouncer: Tracks metrics from PgBouncer using the SHOW STATS command.
    • pgpool: Tracks metrics from Pgpool2 using SHOW POOL_NODES and SHOW POOL_PROCESSES.
    • patroni: Monitors dynamic HA clusters by reading state from a Distributed Consensus Store (DCS) like etcd, Zookeeper, or Consul.

    Note for Continuous Discovery: All continuous modes require access to the template1 or postgres databases of the cluster to discover other database names.

  11. Choose between metric-time and timescale schema types

    master

    pgwatch supports two primary storage strategies for metrics depending on your database capabilities and scale requirements:

    metric-time

    • Mechanism: Uses a single top-level table for each metric in the public schema, with time-based partitioning managed in the subpartitions schema.
    • Best for: Long retention intervals, large volumes of metric data, slow disks, or environments where you are primarily accessing metrics for a single database at a time.
    • Configuration Note: If monitoring a very large number of hosts, you may need to increase the max_locks_per_transaction parameter in postgresql.conf to allow automatic partition dropping. Alternatively, you can manage partition dropping via custom scripts/Cron.

    timescale

    • Mechanism: Leverages the TimescaleDB extension (v1.7+) to manage partitions and compression.
    • Best for: Long retention periods or monitoring hundreds of databases. It offers 3x to 10x compression ratios and faster historical queries.
    • Tuning: You can tune chunking and compression intervals using these functions:
      • admin.timescale_change_chunk_interval()
      • admin.timescale_change_compress_interval() (Default: 2 days for chunking, 1 day for compression).
    • Recommendation: For very deep history (6+ months), consider using TimescaleDB Continuous Aggregates, though this requires manual Grafana dashboard adjustments.

  12. Configure pgwatch using YAML files

    master

    For decentralized deployments, you can configure the gatherer daemon(s) using YAML files instead of a central database. This mode allows you to define sources to be monitored and metrics and presets definitions directly in files.

    Key features:

    • Folder support: You can provide a folder of YAML files instead of a single file, which is useful for configuration management tools like Ansible.
    • Environment Variables: You can use environment variables within your sources YAML files for sensitive data like connection strings.

    Example of using an environment variable in a sources YAML file:

    - name: ...
  conn_str: $MY_VERY_SECRET_CONN_STR
  ...