-
Notifications
You must be signed in to change notification settings - Fork 256
PGSQL MONITOR
How to use Pigsty to monitor remote (existing) PostgreSQL instances?
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
There are 24 default grafana dashboards about PostgreSQL:
- pgsql-activity
- pgsql-alert
- pgsql-cluster
- pgsql-cluster-remote
- pgsql-database
- pgsql-databases
- pgsql-instance
- pgsql-overview
- pgsql-persist
- pgsql-proxy
- pgsql-queries
- pgsql-query
- pgsql-replication
- pgsql-service
- pgsql-session
- pgsql-shard
- pgsql-table
- pgsql-tables
- pgsql-xacts
- gpsql-overview
- pgcat-database
- pgcat-instance
- pgcat-query
- pgcat-table
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 }
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
: pgbouncer log dir,/pg/log/pgbouncer
by default -
patroni_log_dir
: patroni log dir,/pg/log/patroni
by default -
pgbackrest_log_dir
: 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:
# pg-meta-1 [primary] @ 10.10.10.10
- labels: { cls: pg-meta, ins: pg-meta-1, ip: 10.10.10.10 }
targets:
- 10.10.10.10:9630 # <--- pg_exporter for PostgreSQL metrics
- 10.10.10.10:9631 # <--- pg_exporter for pgbouncer metrics
- 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:
bin/pgmon-rm <ins> # remove prometheus targets from all infra nodes
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
------ infra ------
| |
| prometheus | v---- pg-foo-1 ----v
| ^ | metrics | ^ |
| pg_exporter <-|------------|---- postgres |
| (port: 20001) | | 10.10.10.10:5432 |
| ^ | ^------------------^
| ^ | ^
| ^ | v---- pg-foo-2 ----v
| ^ | metrics | ^ |
| pg_exporter <-|------------|---- postgres |
| (port: 20002) | | 10.10.10.11:5433 |
------------------- ^------------------^
Procedure
-
Create monitoring schema, user and privilege on target.
-
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
andpg-bar
, we can declare them in the inventory as:
infra: # infra cluster for proxy, monitor, alert, etc..
hosts: { 10.10.10.10: { infra_seq: 1 } }
vars: # install pg_exporter for remote postgres RDS on a group 'infra'
pg_exporters: # list all remote instances here, alloc a unique unused local port as k
20001: { pg_cluster: pg-foo, pg_seq: 1, pg_host: 10.10.10.10 }
20002: { pg_cluster: pg-bar, pg_seq: 1, pg_host: 10.10.10.11 , pg_port: 5432 }
20003: { pg_cluster: pg-bar, pg_seq: 2, pg_host: 10.10.10.12 , pg_exporter_url: 'postgres://dbuser_monitor:[email protected]:5432/postgres?sslmode=disable'}
20004: { pg_cluster: pg-bar, pg_seq: 3, pg_host: 10.10.10.13 , pg_monitor_username: dbuser_monitor, pg_monitor_password: DBUser.Monitor }
- Execute the playbook against the cluster:
bin/pgmon-add <clsname>
.
To remove a remote cluster monitoring target:
bin/pgmon-rm <clsname>