#!/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;     -- 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";
#--------------------------------------------------------------#
...