pgsql-monitor.yml
Rama main · réplica de lectura del repositorio original.
#!/usr/bin/env ansible-playbook
---
#==============================================================#
# File : pgsql-monitor.yml
# Desc : Setup remote PostgreSQL monitoring via local exporters
# Ctime : 2020-11-02
# Mtime : 2025-12-31
# Path : pgsql-monitor.yml
# Docs : https://pigsty.io/docs/pgsql/monitor
# License : Apache-2.0 @ https://pigsty.io/docs/about/license
# Copyright : 2018-2026 Ruohang Feng / Vonng (rh@vonng.com)
#==============================================================#
- name: PGSQL MONITOR
become: true
hosts: infra
gather_facts: no
ignore_errors: true
vars: { clsname: '' } # work on single cluster if specified
roles:
- { role: node_id , tags: id } # get node identity (always)
- { role: pg_exporters , tags: exporter } # setup exporters
#--------------------------------------------------------------#
# Usage
#--------------------------------------------------------------#
# Monitoring remote Postgres instances with local pg_exporter
#
# You have to define a `pg_exporters` on infra group vars first
#
# infra:
# hosts: # a group contains any nodes that have vmetrics enabled (infra nodes)
# 10.10.10.10: # k,v format, where k is distinct local port that is not used,
# 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-foo, pg_seq: 2, pg_host: 10.10.10.11 }
#
# bin/pgmon-add <clsname> # which is: ./pgsql-monitor -e clsname=pg-foo
#
# ------ infra ------
# | |
# | victoria | 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 |
# ------------------- ^------------------^
#
# You can also overwrite following parameters in group/host/instance level
#
# pg_exporter_config: pg_exporter.yml # pg_exporter configuration file name, you can use other filename under files/
# pg_exporter_cache_ttls: '1,10,60,300' # pg_exporter collector ttl stage in seconds, '1,10,60,300' by default
# pg_exporter_port: 9630 # pg_exporter listen port, 9630 by default
# pg_exporter_params: 'sslmode=disable' # extra url parameters for pg_exporter dsn
# pg_exporter_url: '' # overwrite auto-generate pg dsn if specified
# pg_exporter_auto_discovery: true # enable auto database discovery? enabled by default
# pg_exporter_exclude_database: 'template0,template1,postgres' # csv of database that WILL NOT be monitored during auto-discovery
# pg_exporter_include_database: '' # csv of database that WILL BE monitored during auto-discovery
# pg_exporter_connect_timeout: 200 # pg_exporter connect timeout in ms, 200 by default
# pg_exporter_options: '' # overwrite extra options for pg_exporter
# pg_monitor_username: dbuser_monitor # overwrite monitor username
# pg_monitor_password: DBUser.Monitor # overwrite monitor password
#
# Usually, TARGET URL are generated according to several parameters :
#
# postgres://{{ pg_monitor_username }}:{{ pg_monitor_password }}@{{ inventory_hostname }}:{{ pg_port }}/postgres?sslmode=disable
#
# You can overwrite that URL directly by setting pg_exporter url on instance's vars:
#
# pg_exporters:
# 20001: { pg_cluster: pg-foo, pg_seq: 1, pg_host: 10.10.10.10 , pg_monitor_username: monitor, pg_monitor_password: password}
# 20002: { pg_cluster: pg-foo, pg_seq: 2, pg_host: 10.10.10.11 , pg_exporter_url: 'postgres://dbuser_monitor:DBUser.Monitor@:5432/postgres?sslmode=disable' }
#
# Limitation:
# postgres metrics only
# node, pgbouncer, patroni, haproxy metrics not available
#
# How to setup remote postgres monitor ?
#
# \c postgres;
# CREATE USER dbuser_monitor; -- dedicate monitoring user: dbuser_monitor
# COMMENT ON ROLE dbuser_monitor IS 'system monitor user'; -- comment
# ALTER USER dbuser_monitor PASSWORD 'DBUser_Monitor'; -- change the password here!
# GRANT pg_monitor TO dbuser_monitor; -- not available on PolarDB for PostgreSQL!
# CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "monitor";
#--------------------------------------------------------------#
...