Monitoring

    Pigsty use modern observability stack for PostgreSQL monitoring:

    • Grafana for metrics visualization and PostgreSQL datasource.
    • Prometheus for PostgreSQL / Pgbouncer / Patroni / HAProxy / Node metrics
    • Loki for PostgreSQL / Pgbouncer / Patroni / pgBackRest logs

    Dashboards

    There are 23 default grafana dashboards about PostgreSQL and categorized into 4 levels:

    Overview

    • pgsql-overview : The main dashboard for PGSQL module
    • : Global PGSQL key metrics and alerting events
    • pgsql-shard : Overview of a horizontal sharded PGSQL cluster, e.g. citus / gpsql cluster

    Cluster

    • : Trimmed version of PGSQL Cluster, for remote PGSQL cluster
    • pgsql-activity: Cares about the Session/Load/QPS/TPS/Locks of a PGSQL cluster
    • : Cares about PGSQL cluster replication, slots, and pub/sub.
    • pgsql-service: Cares about PGSQL cluster services, proxies, routes, and load balancers.
    • : Cares about database CRUD, slow queries, and table statistics cross all instances.

    Instance

    • pgsql-instance: The main dashboard for a single PGSQL instance
    • : Instance information from database catalog directly
    • pgsql-persist: Metrics about persistence: WAL, XID, Checkpoint, Archive, IO
    • : Metrics about haproxy the service provider
    • pgsql-queries: Overview of all queries in a single instance
    • : Metrics about sessions and active/idle time in a single instance
    • pgsql-xacts: Metrics about transactions, locks, queries, etc…

    Database

    • : Database information from database catalog directly
    • pgsql-tables : Table/Index access metrics inside a single database
    • : Detailed information (QPS/RT/Index/Seq…) about a single table
    • pgcat-table: Detailed information (Stats/Bloat/…) about a single table from database catalog directly
    • : Detailed information (QPS/RT) about a single query
    • pgcat-query: Detailed information (SQL/Stats) about a single query from database catalog directly

    And it will further be processed by Prometheus record rules & Alert evaluation: files/prometheus/rules/pgsql.yml

    3 labels: , ins, ip will be attached to all metrics & logs, such as { cls: pg-meta, ins: pg-meta-1, ip: 10.10.10.10 }

    Logs

    PostgreSQL related logs are collected by promtail and sending to loki on infra nodes by default.

    • pg_log_dir : postgres log dir, /pg/log/postgres by default
    • : pgbouncer log dir, by default
    • patroni_log_dir : patroni log dir, /pg/log/patroni by default
    • : pgbackrest log dir, /pg/log/pgbackrest by default

    Prometheus monitoring targets are defined in static files under /etc/prometheus/targets/pgsql/, each instance will have a corresponding file.

    Take pg-meta-1 as an example:

    When the global flag is set, patroni target will be moved to a separate file . Since https scrape endpoint is used for that.

    Remote Postgres

    For existing PostgreSQL instances, such as RDS, or homemade PostgreSQL that is not managed by Pigsty, some additional configuration is required if you wish to monitoring them with Pigsty

    Procedure

    1. Create monitoring schema, user and privilege on target.

    2. Declare the cluster in the inventory. For example, assume we want to monitor ‘remote’ pg-meta & pg-test cluster With the name of pg-foo and pg-bar, we can declare them in the inventory as:

    To remove a remote cluster monitoring target: