/
pgsql-monitor.yml
executable file
·89 lines (87 loc) · 4.49 KB
/
pgsql-monitor.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
#!/usr/bin/env ansible-playbook
---
#==============================================================#
# File : pgsql-monitor.yml
# Mtime : 2020-11-02
# Mtime : 2023-09-11
# Desc : add remote pgsql instance to pigsty monitor
# Path : pgsql-monitor.yml
# Author : Ruohang Feng (rh@vonng.com)
# License : AGPLv3
#==============================================================#
- name: PGSQL MONITOR
become: yes
hosts: infra
gather_facts: no
ignore_errors: yes
vars: { clsname: '' } # work on single cluster if specified
roles: [ { role: pg_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 prometheus 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 ------
# | |
# | 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 |
# ------------------- ^------------------^
#
# 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_username: 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; -- 创建专用监控用户 dbuser_monitor
# COMMENT ON ROLE dbuser_monitor IS 'system monitor user'; -- 监控用户备注
# ALTER USER dbuser_monitor PASSWORD 'DBUser_Monitor'; -- 按需修改监控用户密码！
# GRANT pg_monitor TO dbuser_monitor; -- PolarDB for PostgreSQL 里不可用！
# CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "monitor"; -- 查询监控
#--------------------------------------------------------------#
...