PostgreSQL 插件

本文介绍监控数据采集器 Categraf 的 PostgreSQL 指标采集插件

PostgreSQL 监控采集插件，核心原理就是连到 Postgres 实例，执行一些查询，解析输出内容，整理为监控数据上报。

创建只读用户

> create user categraf with password 'categraf';
> alter user categraf set default_transaction_read_only=on;
> grant usage on schema public to categraf;
> grant select on all tables in schema public to categraf ;

配置

配置文件

路径 conf/input.postgresql/postgresql.toml

配置项说明

[[instances]]
# 配置dsn
# 额外要附件的标签
labels = { region="xxxx", zone="xxx" }

## dsn, postgresql 的连接信息
address = "host=1.2.3.4 port=5432 user=postgres password=123456 sslmode=disable"

## outputaddress 相当于是addres的别名 
outputaddress = "db01" 

## 一条连接保持活跃的最大时长， 0s表示永远
## 当查询执行时，到达最大时长的连接不会被立即强制断开
max_lifetime = "0s"

## 忽略哪些db的采集
ignored_databases = ["postgres", "template0", "template1"]

## 显式指定采集哪些db 
databases = ["app_production", "testing"]

## Whether to use prepared statements when connecting to the database.
## This should be set to false when connecting through a PgBouncer instance
## with pool_mode set to transaction.
## 是否使用prepared statements 连接数据库
prepared_statements = true

  [[instances.metrics]]
  mesurement = "sessions"
  #label_fields = [ "status", "type" ]
  metric_fields = [ "active" ]
  timeout = "3s"
  request = '''
  select count(*) as active from pg_stat_activity;
  '''

配置项基本都在注释中了，解释一下最后的metrics生成规则。

命令与输出

# select count(*) as active from pg_stat_activity;
 active
--------
      6
(1 row)

select count(*) as active from pg_stat_activity; 这条语句会查询当前活跃的session数量这条语句会查询当前活跃的session数量
mesurement = "sessions" 定义指标前缀为sessions
metrics_fields = [ "active" ] 定义列名active作为指标名，附加在mesurement后面
label_fields = [ "status", "type" ] 定义列status和列type作为标签, 这个例子中注释了，所以不附加到指标上最终生成的指标为 postgresql_sessions_active{server="db01",region="xxx",zone="xxx"} 6