Monitoring

How to use Pigsty to monitor remote (existing) PostgreSQL instances?

Overview

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
  • pgsql-alert : Global PGSQL key metrics and alerting events
  • pgsql-shard : Overview of a horizontal sharded PGSQL cluster, e.g. citus / gpsql cluster

Cluster

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

Instance

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

Database

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

Metrics

PostgreSQL’s metrics are defined by collector files: pg_exporter.yml

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

3 labels: cls, 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.

Target Management

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:

  1. # pg-meta-1 [primary] @ 10.10.10.10
  2. - labels: { cls: pg-meta, ins: pg-meta-1, ip: 10.10.10.10 }
  3.   targets:
  4.     - 10.10.10.10:9630    # <--- pg_exporter for PostgreSQL metrics
  5.     - 10.10.10.10:9631    # <--- pg_exporter for pgbouncer metrics
  6.     - 10.10.10.10:8008    # <--- patroni metrics

When the global flag patroni_ssl_enabled is set, patroni target will be moved to a separate file /etc/prometheus/targets/patroni/<ins>.yml. Since https scrape endpoint is used for that.

Prometheus monitoring target will be removed when cluster is removed with bin/pgsql-rm or pgsql-rm.yml. You can also remove it manually, or using playbook subtasks:

  1. bin/pgmon-rm <ins>      # remove prometheus targets from all infra nodes

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

  1. ------ infra ------
  2. |                 |
  3. |   prometheus    |            v---- pg-foo-1 ----v
  4. |       ^         |  metrics   |         ^        |
  5. |   pg_exporter <-|------------|----  postgres    |
  6. |   (port: 20001) |            | 10.10.10.10:5432 |
  7. |       ^         |            ^------------------^
  8. |       ^         |                      ^
  9. |       ^         |            v---- pg-foo-2 ----v
  10. |       ^         |  metrics   |         ^        |
  11. |   pg_exporter <-|------------|----  postgres    |
  12. |   (port: 20002) |            | 10.10.10.11:5433 |
  13. -------------------            ^------------------^

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:

  1. infra:            # infra cluster for proxy, monitor, alert, etc..
  2.   hosts: { 10.10.10.10: { infra_seq: 1 } }
  3.   vars:           # install pg_exporter for remote postgres RDS on a group 'infra'
  4.     pg_exporters: # list all remote instances here, alloc a unique unused local port as k
  6.       20001: { pg_cluster: pg-foo, pg_seq: 1, pg_host: 10.10.10.10 }
  8.       20002: { pg_cluster: pg-bar, pg_seq: 1, pg_host: 10.10.10.11 , pg_port: 5432 }
  9.       20003: { pg_cluster: pg-bar, pg_seq: 2, pg_host: 10.10.10.12 , pg_exporter_url: 'postgres://dbuser_monitor:DBUser.Monitor@10.10.10.12:5432/postgres?sslmode=disable'}
  10.       20004: { pg_cluster: pg-bar, pg_seq: 3, pg_host: 10.10.10.13 , pg_monitor_username: dbuser_monitor, pg_monitor_password: DBUser.Monitor }
  1. Execute the playbook against the cluster: bin/pgmon-add <clsname>.

To remove a remote cluster monitoring target:

  1. bin/pgmon-rm <clsname>

Last modified 2023-02-27: refresh en docs to v2.0 (e82b371)