pgwatch2 Documentation

pgwatch2 is a monitoring solution for PostgreSQL that offers several key capabilities:

• Non-invasive PostgreSQL setup: Base functionality requires no extensions or superuser rights.
• Extensive Metric Coverage: Includes preset configurations for PostgreSQL Statistics Collector data and supports custom business-domain metrics defined in pure SQL.
• Flexible Storage: Supports PostgreSQL, PostgreSQL with TimescaleDB, InfluxDB, Graphite, or Prometheus scraping.
• Deployment Models: Supports "push" and "pull" models using PostgreSQL configuration DB, YAML, or ENV configuration.
• Scalable Monitoring: Can monitor all, single, or a subset (via list or regex) of databases within a PostgreSQL instance.
• Advanced Integrations: Supports PgBouncer, Pgpool2, AWS RDS, and Patroni with automatic member discovery.
• Observability & Security: Includes an internal health-check API (default port 8081), SSL connection support, and password encryption for connection strings.
• Extended Monitoring: Supports log parsing for error detection and OS-level metrics via PL/Python helper stored procedures.
• Connectivity Testing: Includes a Ping mode to verify connectivity to all monitored databases.

pgwatch2 is a monitoring solution designed to glue together proven software for metrics storage and dashboarding. The core components are:

• Metrics gathering daemon (Collector): The mandatory Go-based component. It reads configuration, fetches metrics from target databases via SQL queries, and stores them in a destination (DB or Prometheus endpoint).
• Configuration store: Defines which databases to monitor, the frequency, and the specific metrics. Supported modes include:
  • PostgreSQL: A schema with 5 tables.
  • File-based: YAML config files and SQL metric definition files.
  • ENV-based: Ad-hoc configuration using a connection string (JDBC or Libpq), ideal for containers.
• Metrics storage DB: Where gathered data is kept. Options include InfluxDB, PostgreSQL (requires JSONB, v9.4+), TimescaleDB, Prometheus, Graphite, or plain JSON files.
• Web UI (Optional): Used for administering monitoring configuration and basic data analysis. Note: The Web UI requires the configuration to be stored in a PostgreSQL database.
• Metrics representation: Typically handled by Grafana, using predefined dashboards for Postgres and InfluxDB.

pgwatch2 supports several schema architectures depending on your scale and PostgreSQL version:

• metric: A single/separate table for each distinct metric in the public schema. No partitioning. Works on all PG versions. Suitable for up to ~25 monitored DBs.
• metric-time: A single top-level table per metric in public + weekly partitions in the subpartitions schema. Requires PG 11+. Suitable for up to ~50 monitored DBs. Reduces IO by dropping old partitions instead of deleting rows. This is the default for the pgwatch2-postgres Docker image.
• metric-dbname-time: A single top-level table per metric in public + two levels of subpartitions (dbname + weekly time) in subpartitions. Requires PG 11+. Best for 50+ monitored DBs or when you need fast queries for specific databases on slow disks.
• custom: All data goes into public.metrics. You are responsible for all partition management, table creation, and data cleanup.
• timescale: Uses the TimescaleDB extension (v1.7+). Best for hundreds of databases or long retention periods due to 3x-10x compression. Note: Realtime metrics still use the metric-time schema because TimescaleDB does not support unlogged tables.

When monitoring managed cloud services (Google Cloud SQL, Amazon RDS, Azure, Aiven), certain functionalities like file system access and untrusted PL-languages (e.g., Python) are disallowed. This results in the loss of some metrics and 'helper functions' compared to a standard on-site setup.

To handle these limitations:

• Use 'lighter' dashboards: Instead of standard dashboards like "System Stats" or "DB overview" (which may display errors due to missing permissions), use dashboards specifically tailored for unprivileged environments, such as "DB overview Unprivileged".
• Integrate OS metrics: Since OS-level helpers are unavailable, you can integrate OS metrics into Grafana using the provider's specific data source (e.g., Stackdriver, CloudWatch, or Azure Monitor).
• Add custom metrics: You can still add custom business metrics using plain SQL (e.g., tracking sales orders).

Metric fetching helpers (PL/Python wrappers) are primarily used to provide unprivileged users with access to statistics that otherwise require superuser permissions.

Key considerations:

• PostgreSQL v10+: Most helpers are unnecessary because you can use the built-in pg_monitor role, which provides access to Statistics Collector views without superuser privileges. However, some out-of-the-box metrics still rely on helpers as a first attempt before falling back to direct access.
• OS Statistics: To gather CPU, IO, and disk metrics, helpers based on the psutil Python package are used. This requires a minimum Kernel version of 3.3. Note that SQL helpers are defined for Python 3 (LANGUAGE plpython3u); if using Python 2, you must manually change the language declaration.
• Push Model (Local Gatherer): If running the gatherer locally in a "push" configuration, helpers are mostly unnecessary because the superuser can be used safely. You can also use the --direct-os-stats parameter (available since v1.8.4) to fetch psutil_* metrics directly from OS counters, falling back to PL/Python only if direct fetching fails.
• Upgrades: If you perform a binary upgrade via pg_upgrade on a cluster that has helpers installed, you may encounter errors. To fix this, drop the failing helpers on the old cluster and re-create them after the upgrade.

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

Special Built-in Metrics

• change_events: Tracks DDL and configuration changes. It relies on internal *_hashes metrics which should not be removed.
• recommendations: When enabled (interval > 0), it executes all metrics starting with reco_* to identify performance or security best practices violations.
• server_log_event_counts: Enables Postgres server log

-- 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;