pg_exporter.yml

#==============================================================#
# Desc      :   pg_exporter metrics collector definition (Legacy)
# Ver       :   PostgreSQL 9.1 ~ 9.6 and pgbouncer 1.9~1.25+
# Ctime     :   2019-12-09
# Mtime     :   2026-02-07
# Homepage  :   https://pigsty.io
# Author    :   Ruohang Feng (rh@vonng.com)
# License   :   Apache-2.0 @ https://github.com/pgsty/pg_exporter
# Copyright :   2018-2026  Ruohang Feng / Vonng (rh@vonng.com)
#==============================================================#


#==============================================================#
# 1. Config File
#==============================================================#
# The configuration file for pg_exporter is a YAML file.
# Default configurations are retrieved via following precedence:
#     1. command line args:      --config=<config path>
#     2. environment variables:  PG_EXPORTER_CONFIG=<config path>
#     3. pg_exporter.yml        (Current directory)
#     4. /etc/pg_exporter.yml   (config file)
#     5. /etc/pg_exporter       (config dir)

#==============================================================#
# 2. Config Format
#==============================================================#
# pg_exporter config could be a single YAML file, or a directory containing a series of separated YAML files.
# Each YAML config file consists of one or more metrics Collector definition, which are top-level objects.
# If a directory is provided, all YAML in that directory will be merged in alphabetic order.

#==============================================================#
# 3. Version Compatibility
#==============================================================#
# Each collector has two optional version compatibility parameters: `min_version` and `max_version`.
# These two parameters specify the version compatibility of the collector. If target postgres/pgbouncer's
# version is less than `min_version`, or higher than `max_version`, the collector will not be installed.
#
# These two parameters are using PostgreSQL server version number format, which is a 6-digit integer
# format as <major:2 digit><minor:2 digit>:<release: 2 digit>.
#
# For example:
#   - 90100  stands for 9.1
#   - 90600  stands for 9.6
#   - 100000 stands for 10.0
#
# Version compatibility range is left-inclusive right-exclusive: [min, max)


#==============================================================#
# 0110 pg
#==============================================================#
pg_primary_only:
  name: pg
  desc: PostgreSQL basic information (on primary)
  query: |-
    SELECT 
      extract(EPOCH FROM CURRENT_TIMESTAMP)                  AS timestamp,
      extract(EPOCH FROM now() - pg_postmaster_start_time()) AS uptime,
      extract(EPOCH FROM pg_postmaster_start_time())         AS boot_time,
      (('x' || lpad(split_part(pg_current_xlog_location()::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(pg_current_xlog_location()::text, '/', 2), 8, '0'))::bit(32)::bigint) AS lsn,
      (('x' || lpad(split_part(pg_current_xlog_insert_location()::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(pg_current_xlog_insert_location()::text, '/', 2), 8, '0'))::bit(32)::bigint) AS insert_lsn,
      (('x' || lpad(split_part(pg_current_xlog_location()::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(pg_current_xlog_location()::text, '/', 2), 8, '0'))::bit(32)::bigint) AS write_lsn,
      (('x' || lpad(split_part(pg_current_xlog_location()::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(pg_current_xlog_location()::text, '/', 2), 8, '0'))::bit(32)::bigint) AS flush_lsn,
      NULL::BIGINT                                           AS receive_lsn,
      NULL::BIGINT                                           AS replay_lsn,
      extract(EPOCH FROM pg_conf_load_time())                AS reload_time,
      extract(EPOCH FROM now() - pg_conf_load_time())        AS conf_reload_time,
      NULL::FLOAT                                            AS last_replay_time,
      0::FLOAT                                               AS lag,
      pg_is_in_recovery()                                    AS is_in_recovery,
      FALSE                                                  AS is_wal_replay_paused;
  tags: [ cluster, primary ]
  ttl: 1
  min_version: 90100
  max_version: 100000
  fatal: true
  skip: false
  metrics:
    - timestamp:            { usage: GAUGE   ,description: "current database timestamp in unix epoch" }
    - uptime:               { usage: GAUGE   ,description: "seconds since postmaster start" }
    - boot_time:            { usage: GAUGE   ,description: "postmaster boot timestamp in unix epoch" }
    - lsn:                  { usage: COUNTER ,description: "log sequence number, current write location" }
    - insert_lsn:           { usage: COUNTER ,description: "primary only, location of current wal inserting" }
    - write_lsn:            { usage: COUNTER ,description: "primary only, location of current wal writing" }
    - flush_lsn:            { usage: COUNTER ,description: "primary only, location of current wal syncing" }
    - receive_lsn:          { usage: COUNTER ,description: "replica only, location of wal synced to disk" }
    - replay_lsn:           { usage: COUNTER ,description: "replica only, location of wal applied" }
    - reload_time:          { usage: GAUGE   ,description: "time when configuration was last reloaded" }
    - conf_reload_time:     { usage: GAUGE   ,description: "seconds since last configuration reload" }
    - last_replay_time:     { usage: GAUGE   ,description: "time when last transaction been replayed" }
    - lag:                  { usage: GAUGE   ,description: "replica only, replication lag in seconds" }
    - is_in_recovery:       { usage: GAUGE   ,description: "1 if in recovery mode" }
    - is_wal_replay_paused: { usage: GAUGE   ,description: "1 if wal play is paused" }

pg_replica_only:
  name: pg
  desc: PostgreSQL basic information (on replica, 9.1+)
  query: |-
    SELECT 
      extract(EPOCH FROM CURRENT_TIMESTAMP)                  AS timestamp,
      extract(EPOCH FROM now() - pg_postmaster_start_time()) AS uptime,
      extract(EPOCH FROM pg_postmaster_start_time())         AS boot_time,
      (('x' || lpad(split_part(pg_last_xlog_replay_location()::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(pg_last_xlog_replay_location()::text, '/', 2), 8, '0'))::bit(32)::bigint) AS lsn,
      NULL::BIGINT                                           AS insert_lsn,
      NULL::BIGINT                                           AS write_lsn,
      NULL::BIGINT                                           AS flush_lsn,
      (('x' || lpad(split_part(pg_last_xlog_receive_location()::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(pg_last_xlog_receive_location()::text, '/', 2), 8, '0'))::bit(32)::bigint) AS receive_lsn,
      (('x' || lpad(split_part(pg_last_xlog_replay_location()::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(pg_last_xlog_replay_location()::text, '/', 2), 8, '0'))::bit(32)::bigint) AS replay_lsn,
      extract(EPOCH FROM pg_conf_load_time())                AS reload_time,
      extract(EPOCH FROM now() - pg_conf_load_time())        AS conf_reload_time,
      extract(EPOCH FROM pg_last_xact_replay_timestamp())    AS last_replay_time,
      CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
          ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS lag,
      pg_is_in_recovery() AS is_in_recovery,
      pg_is_xlog_replay_paused() AS is_wal_replay_paused;

  tags: [ cluster, replica ]
  ttl: 1
  min_version: 90100
  max_version: 100000
  fatal: true
  skip: false
  metrics:
    - timestamp:            { usage: GAUGE   ,description: "current database timestamp in unix epoch" }
    - uptime:               { usage: GAUGE   ,description: "seconds since postmaster start" }
    - boot_time:            { usage: GAUGE   ,description: "postmaster boot timestamp in unix epoch" }
    - lsn:                  { usage: COUNTER ,description: "log sequence number, current write location" }
    - insert_lsn:           { usage: COUNTER ,description: "primary only, location of current wal inserting" }
    - write_lsn:            { usage: COUNTER ,description: "primary only, location of current wal writing" }
    - flush_lsn:            { usage: COUNTER ,description: "primary only, location of current wal syncing" }
    - receive_lsn:          { usage: COUNTER ,description: "replica only, location of wal synced to disk" }
    - replay_lsn:           { usage: COUNTER ,description: "replica only, location of wal applied" }
    - reload_time:          { usage: GAUGE   ,description: "time when configuration was last reloaded" }
    - conf_reload_time:     { usage: GAUGE   ,description: "seconds since last configuration reload" }
    - last_replay_time:     { usage: GAUGE   ,description: "time when last transaction been replayed" }
    - lag:                  { usage: GAUGE   ,description: "replica only, replication lag in seconds" }
    - is_in_recovery:       { usage: GAUGE   ,description: "1 if in recovery mode" }
    - is_wal_replay_paused: { usage: GAUGE   ,description: "1 if wal play is paused" }


#==============================================================#
# 0120 pg_meta
#==============================================================#
pg_meta_96:
  name: pg_meta
  desc: PostgreSQL meta info for pg 9.6 (with pg_control_system)
  query: |
    SELECT
      (SELECT system_identifier FROM pg_control_system())          AS cluster_id,
      coalesce((SELECT setting FROM pg_settings WHERE name = 'cluster_name'), 'N/A') AS cluster_name,
      (SELECT setting FROM pg_settings WHERE name = 'port')        AS listen_port,
      (SELECT setting FROM pg_settings WHERE name = 'data_directory') AS data_dir,
      (SELECT setting FROM pg_settings WHERE name = 'config_file') AS conf_path,
      (SELECT setting FROM pg_settings WHERE name = 'hba_file')    AS hba_path,
      (SELECT setting FROM pg_settings WHERE name = 'wal_level')   AS wal_level,
      (SELECT setting FROM pg_settings WHERE name = 'server_encoding') AS encoding,
      (SELECT setting FROM pg_settings WHERE name = 'server_version') AS version,
      (SELECT setting FROM pg_settings WHERE name = 'server_version_num') AS ver_num,
      version()                                                   AS ver_str,
      (SELECT setting FROM pg_settings WHERE name = 'shared_preload_libraries') AS extensions,
      coalesce((SELECT setting FROM pg_settings WHERE name = 'primary_conninfo'), 'N/A') AS primary_conninfo,
      1                                                           AS info;
  ttl: 10
  min_version: 90600
  tags: [ cluster ]
  metrics:
    - cluster_id:        { usage: LABEL ,description: "cluster system identifier" }
    - cluster_name:      { usage: LABEL ,description: "cluster name" }
    - listen_port:       { usage: LABEL ,description: "listen port" }
    - data_dir:          { usage: LABEL ,description: "path to data directory" }
    - conf_path:         { usage: LABEL ,description: "path to postgresql.conf" }
    - hba_path:          { usage: LABEL ,description: "path to pg_hba.conf" }
    - wal_level:         { usage: LABEL ,description: "wal level" }
    - encoding:          { usage: LABEL ,description: "server encoding" }
    - version:           { usage: LABEL ,description: "server version in human-readable format" }
    - ver_num:           { usage: LABEL ,description: "server version number in machine-readable format" }
    - ver_str:           { usage: LABEL ,description: "complete version string" }
    - extensions:        { usage: LABEL ,description: "server installed preload libraries" }
    - primary_conninfo:  { usage: LABEL ,description: "connection string to upstream (do not set password here)" }
    - info:              { usage: GAUGE ,description: "constant 1" }

pg_meta_91:
  name: pg_meta
  desc: PostgreSQL meta info for pg 9.1 - 9.5
  query: |
    SELECT
      'N/A'                                                      AS cluster_id,
      coalesce((SELECT setting FROM pg_settings WHERE name = 'cluster_name'), 'N/A') AS cluster_name,
      (SELECT setting FROM pg_settings WHERE name = 'port')       AS listen_port,
      (SELECT setting FROM pg_settings WHERE name = 'data_directory') AS data_dir,
      (SELECT setting FROM pg_settings WHERE name = 'config_file') AS conf_path,
      (SELECT setting FROM pg_settings WHERE name = 'hba_file')   AS hba_path,
      (SELECT setting FROM pg_settings WHERE name = 'wal_level')  AS wal_level,
      (SELECT setting FROM pg_settings WHERE name = 'server_encoding') AS encoding,
      (SELECT setting FROM pg_settings WHERE name = 'server_version') AS version,
      (SELECT setting FROM pg_settings WHERE name = 'server_version_num') AS ver_num,
      version()                                                  AS ver_str,
      (SELECT setting FROM pg_settings WHERE name = 'shared_preload_libraries') AS extensions,
      coalesce((SELECT setting FROM pg_settings WHERE name = 'primary_conninfo'), 'N/A') AS primary_conninfo,
      1                                                          AS info;
  ttl: 10
  min_version: 90100
  max_version: 90600
  tags: [ cluster ]
  metrics:
    - cluster_id:        { usage: LABEL ,description: "cluster system identifier" }
    - cluster_name:      { usage: LABEL ,description: "cluster name" }
    - listen_port:       { usage: LABEL ,description: "listen port" }
    - data_dir:          { usage: LABEL ,description: "path to data directory" }
    - conf_path:         { usage: LABEL ,description: "path to postgresql.conf" }
    - hba_path:          { usage: LABEL ,description: "path to pg_hba.conf" }
    - wal_level:         { usage: LABEL ,description: "wal level" }
    - encoding:          { usage: LABEL ,description: "server encoding" }
    - version:           { usage: LABEL ,description: "server version in human-readable format" }
    - ver_num:           { usage: LABEL ,description: "server version number in machine-readable format" }
    - ver_str:           { usage: LABEL ,description: "complete version string" }
    - extensions:        { usage: LABEL ,description: "server installed preload libraries" }
    - primary_conninfo:  { usage: LABEL ,description: "connection string to upstream (do not set password here)" }
    - info:              { usage: GAUGE ,description: "constant 1" }


#==============================================================#
# 0130 pg_setting
#==============================================================#
# Key PostgreSQL configuration parameters for PostgreSQL 9.1 - 9.6
# Use scalar subquery on pg_settings for "missing_ok" semantics (return NULL if not exist)
pg_setting:
  name: pg_setting
  desc: PostgreSQL shared configuration parameters (legacy 9.1-9.6)
  query: |
    SELECT
      (SELECT setting::int FROM pg_settings WHERE name = 'max_connections')                   AS max_connections,
      (SELECT setting::int FROM pg_settings WHERE name = 'max_prepared_transactions')         AS max_prepared_transactions,
      (SELECT setting::int FROM pg_settings WHERE name = 'max_locks_per_transaction')         AS max_locks_per_transaction,
      (SELECT setting::int FROM pg_settings WHERE name = 'max_worker_processes')              AS max_worker_processes,
      (SELECT setting::int FROM pg_settings WHERE name = 'max_parallel_workers')              AS max_parallel_workers,
      (SELECT setting::int FROM pg_settings WHERE name = 'max_parallel_workers_per_gather')   AS max_parallel_workers_per_gather,
      (SELECT setting::int FROM pg_settings WHERE name = 'max_parallel_maintenance_workers')  AS max_parallel_maintenance_workers,
      (SELECT setting::int FROM pg_settings WHERE name = 'max_replication_slots')             AS max_replication_slots,
      (SELECT setting::int FROM pg_settings WHERE name = 'max_wal_senders')                   AS max_wal_senders,
      (SELECT setting::int FROM pg_settings WHERE name = 'block_size')                        AS block_size,
      (SELECT setting::int FROM pg_settings WHERE name = 'wal_block_size')                    AS wal_block_size,
      (SELECT setting::bigint * CASE unit WHEN '8kB' THEN 8192 WHEN 'kB' THEN 1024 WHEN 'MB' THEN 1048576 WHEN 'GB' THEN 1073741824 ELSE 1 END
         FROM pg_settings WHERE name = 'segment_size')                                        AS segment_size,
      (SELECT setting::bigint * CASE unit WHEN '8kB' THEN 8192 WHEN 'kB' THEN 1024 WHEN 'MB' THEN 1048576 WHEN 'GB' THEN 1073741824 ELSE 1 END
         FROM pg_settings WHERE name = 'wal_segment_size')                                    AS wal_segment_size,
      (SELECT CASE setting WHEN 'on' THEN 1 ELSE 0 END FROM pg_settings WHERE name = 'data_checksums') AS data_checksums,
      (SELECT CASE setting WHEN 'on' THEN 1 ELSE 0 END FROM pg_settings WHERE name = 'wal_log_hints')  AS wal_log_hints,
      (SELECT CASE setting WHEN 'on' THEN 1 ELSE 0 END FROM pg_settings WHERE name = 'fsync')          AS fsync,
      (SELECT CASE setting WHEN 'on' THEN 1 ELSE 0 END FROM pg_settings WHERE name = 'full_page_writes') AS full_page_writes,
      (SELECT CASE setting WHEN 'minimal' THEN 1 WHEN 'archive' THEN 2 WHEN 'hot_standby' THEN 3 ELSE 0 END FROM pg_settings WHERE name = 'wal_level') AS wal_level,
      (SELECT setting::int FROM pg_settings WHERE name = 'checkpoint_segments')               AS checkpoint_segments,
      (SELECT setting::bigint * CASE unit WHEN '8kB' THEN 8192 WHEN 'kB' THEN 1024 WHEN 'MB' THEN 1048576 WHEN 'GB' THEN 1073741824 ELSE 1 END
         FROM pg_settings WHERE name = 'min_wal_size')                                        AS min_wal_size,
      (SELECT setting::bigint * CASE unit WHEN '8kB' THEN 8192 WHEN 'kB' THEN 1024 WHEN 'MB' THEN 1048576 WHEN 'GB' THEN 1073741824 ELSE 1 END
         FROM pg_settings WHERE name = 'max_wal_size')                                        AS max_wal_size,
      (SELECT setting::int FROM pg_settings WHERE name = 'wal_keep_segments')                 AS wal_keep_segments,
      (SELECT setting::bigint * CASE unit WHEN '8kB' THEN 8192 WHEN 'kB' THEN 1024 WHEN 'MB' THEN 1048576 WHEN 'GB' THEN 1073741824 ELSE 1 END
         FROM pg_settings WHERE name = 'shared_buffers')                                      AS shared_buffers,
      (SELECT setting::bigint * CASE unit WHEN '8kB' THEN 8192 WHEN 'kB' THEN 1024 WHEN 'MB' THEN 1048576 WHEN 'GB' THEN 1073741824 ELSE 1 END
         FROM pg_settings WHERE name = 'work_mem')                                            AS work_mem,
      (SELECT setting::bigint * CASE unit WHEN '8kB' THEN 8192 WHEN 'kB' THEN 1024 WHEN 'MB' THEN 1048576 WHEN 'GB' THEN 1073741824 ELSE 1 END
         FROM pg_settings WHERE name = 'maintenance_work_mem')                                AS maintenance_work_mem,
      (SELECT setting::bigint * CASE unit WHEN '8kB' THEN 8192 WHEN 'kB' THEN 1024 WHEN 'MB' THEN 1048576 WHEN 'GB' THEN 1073741824 ELSE 1 END
         FROM pg_settings WHERE name = 'effective_cache_size')                                AS effective_cache_size,
      (SELECT CASE setting WHEN 'off' THEN 0 WHEN 'on' THEN 1 WHEN 'always' THEN 2 ELSE -1 END FROM pg_settings WHERE name = 'archive_mode') AS archive_mode,
      (SELECT CASE setting WHEN 'on' THEN 1 ELSE 0 END FROM pg_settings WHERE name = 'autovacuum') AS autovacuum,
      (SELECT setting::int FROM pg_settings WHERE name = 'autovacuum_max_workers')            AS autovacuum_max_workers,
      (SELECT setting::int FROM pg_settings WHERE name = 'checkpoint_timeout')                AS checkpoint_timeout,
      (SELECT setting::float FROM pg_settings WHERE name = 'checkpoint_completion_target')    AS checkpoint_completion_target,
      (SELECT CASE setting WHEN 'on' THEN 1 ELSE 0 END FROM pg_settings WHERE name = 'hot_standby') AS hot_standby,
      (SELECT CASE setting
        WHEN 'off' THEN 0 WHEN 'local' THEN 1 WHEN 'remote_write' THEN 2
        WHEN 'on' THEN 3 WHEN 'remote_apply' THEN 4 ELSE -1 END
        FROM pg_settings WHERE name = 'synchronous_commit')                                   AS synchronous_commit;

  ttl: 10
  min_version: 90100
  tags: [ cluster ]
  metrics:
    - max_connections:                  { usage: GAUGE ,description: "maximum number of concurrent connections to the database server" }
    - max_prepared_transactions:        { usage: GAUGE ,description: "maximum number of transactions that can be in the prepared state simultaneously" }
    - max_locks_per_transaction:        { usage: GAUGE ,description: "maximum number of locks per transaction" }
    - max_worker_processes:             { usage: GAUGE ,description: "maximum number of background processes (9.4+)" }
    - max_parallel_workers:             { usage: GAUGE ,description: "maximum number of parallel workers that can be active at one time (9.6+)" }
    - max_parallel_workers_per_gather:  { usage: GAUGE ,description: "maximum number of parallel workers per Gather node (9.6+)" }
    - max_parallel_maintenance_workers: { usage: GAUGE ,description: "maximum number of parallel maintenance workers (NULL on 9.x)" }
    - max_replication_slots:            { usage: GAUGE ,description: "maximum number of replication slots (9.4+)" }
    - max_wal_senders:                  { usage: GAUGE ,description: "maximum number of concurrent WAL sender connections" }
    - block_size:                       { usage: GAUGE ,description: "database block size in bytes (default 8192)" }
    - wal_block_size:                   { usage: GAUGE ,description: "WAL block size in bytes" }
    - segment_size:                     { usage: GAUGE ,description: "database file segment size in bytes" }
    - wal_segment_size:                 { usage: GAUGE ,description: "WAL segment size in bytes" }
    - data_checksums:                   { usage: GAUGE ,description: "data checksums enabled, 1=on 0=off (9.3+)" }
    - wal_log_hints:                    { usage: GAUGE ,description: "WAL log hints enabled, 1=on 0=off (9.4+)" }
    - fsync:                            { usage: GAUGE ,description: "fsync enabled (CRITICAL for data safety), 1=on 0=off" }
    - full_page_writes:                 { usage: GAUGE ,description: "full page writes enabled, 1=on 0=off" }
    - wal_level:                        { usage: GAUGE ,description: "WAL level, 1=minimal 2=archive 3=hot_standby" }
    - checkpoint_segments:              { usage: GAUGE ,description: "number of checkpoint segments (pre-9.5)" }
    - min_wal_size:                     { usage: GAUGE ,description: "minimum WAL size in bytes (9.5+)" }
    - max_wal_size:                     { usage: GAUGE ,description: "maximum WAL size in bytes (9.5+)" }
    - wal_keep_segments:                { usage: GAUGE ,description: "WAL segments kept for standby replication (pg_basebackup/streaming)" }
    - shared_buffers:                   { usage: GAUGE ,description: "shared buffer size in bytes" }
    - work_mem:                         { usage: GAUGE ,description: "work memory size in bytes" }
    - maintenance_work_mem:             { usage: GAUGE ,description: "maintenance work memory size in bytes" }
    - effective_cache_size:             { usage: GAUGE ,description: "planner's assumption about effective OS cache size in bytes" }
    - archive_mode:                     { usage: GAUGE ,description: "archive mode, 0=off 1=on 2=always" }
    - autovacuum:                       { usage: GAUGE ,description: "autovacuum enabled, 1=on 0=off" }
    - autovacuum_max_workers:           { usage: GAUGE ,description: "maximum number of autovacuum worker processes" }
    - checkpoint_timeout:               { usage: GAUGE ,description: "checkpoint timeout in seconds" }
    - checkpoint_completion_target:     { usage: GAUGE ,description: "checkpoint completion target (0.0-1.0)" }
    - hot_standby:                      { usage: GAUGE ,description: "hot standby mode enabled, 1=on 0=off" }
    - synchronous_commit:               { usage: GAUGE ,description: "synchronous commit level, 0=off 1=local 2=remote_write 3=on 4=remote_apply" }


#==============================================================#
# 0210 pg_repl
#==============================================================#
pg_repl_94:
  name: pg_repl
  desc: PostgreSQL replication stat metrics 9.4 - 9.6 (with backend_xmin)
  query: |-
    SELECT appname, usename, address, pid, client_port, state, sync_state, sync_priority, backend_xmin, lsn,
           lsn - sent_lsn  AS sent_diff, lsn - write_lsn AS write_diff, lsn - flush_lsn AS flush_diff, lsn - replay_lsn AS replay_diff,
           sent_lsn, write_lsn, flush_lsn, replay_lsn,
           0::FLOAT AS write_lag, 0::FLOAT AS flush_lag, 0::FLOAT AS replay_lag,
           extract(EPOCH FROM current_timestamp) AS "time", extract(EPOCH FROM backend_start) AS launch_time
    FROM (
      SELECT application_name AS appname, usename, coalesce(client_addr::TEXT,'localhost') AS address, pid::TEXT, client_port,
             CASE state WHEN 'streaming' THEN 0 WHEN 'startup' THEN 1 WHEN 'catchup' THEN 2 WHEN 'backup' THEN 3 WHEN 'stopping' THEN 4 ELSE -1 END AS state,
             CASE sync_state WHEN 'async' THEN 0 WHEN 'potential' THEN 1 WHEN 'sync' THEN 2 WHEN 'quorum' THEN 3 ELSE -1 END AS sync_state,
             sync_priority, backend_xmin::TEXT::BIGINT AS backend_xmin,
             (('x' || lpad(split_part(current.loc::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(current.loc::text, '/', 2), 8, '0'))::bit(32)::bigint) AS lsn,
             (('x' || lpad(split_part(sent_location::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(sent_location::text, '/', 2), 8, '0'))::bit(32)::bigint) AS sent_lsn,
             (('x' || lpad(split_part(write_location::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(write_location::text, '/', 2), 8, '0'))::bit(32)::bigint) AS write_lsn,
             (('x' || lpad(split_part(flush_location::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(flush_location::text, '/', 2), 8, '0'))::bit(32)::bigint) AS flush_lsn,
             (('x' || lpad(split_part(replay_location::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(replay_location::text, '/', 2), 8, '0'))::bit(32)::bigint) AS replay_lsn,
             backend_start
      FROM pg_stat_replication,
           (SELECT CASE WHEN pg_is_in_recovery() THEN pg_last_xlog_replay_location() ELSE pg_current_xlog_location() END AS loc) current
    ) d;

  ttl: 10
  min_version: 90400
  max_version: 100000
  tags: [ cluster ]
  metrics:
    - appname:           { usage: LABEL   ,description: "Name of the application that is connected to this WAL sender" }
    - usename:           { usage: LABEL   ,description: "Name of the user logged into this WAL sender process" }
    - address:           { usage: LABEL   ,description: "IP address of the client connected to this WAL sender, localhost for unix socket" }
    - pid:               { usage: LABEL   ,description: "Process ID of the WAL sender process" }
    - client_port:       { usage: GAUGE   ,description: "TCP port number that the client is using for communication with this WAL sender, or -1 if a Unix socket is used" }
    - state:             { usage: GAUGE   ,description: "Current WAL sender encoded state 0-4 for streaming|startup|catchup|backup|stopping" }
    - sync_state:        { usage: GAUGE   ,description: "Encoded synchronous state of this standby server, 0-3 for async|potential|sync|quorum" }
    - sync_priority:     { usage: GAUGE   ,description: "Priority of this standby server for being chosen as the synchronous standby" }
    - backend_xmin:      { usage: COUNTER ,description: "This standby's xmin horizon reported by hot_standby_feedback." }
    - lsn:               { usage: COUNTER ,description: "Current log position on this server" }
    - sent_diff:         { usage: GAUGE   ,description: "Last log position sent to this standby server diff with current lsn" }
    - write_diff:        { usage: GAUGE   ,description: "Last log position written to disk by this standby server diff with current lsn" }
    - flush_diff:        { usage: GAUGE   ,description: "Last log position flushed to disk by this standby server diff with current lsn" }
    - replay_diff:       { usage: GAUGE   ,description: "Last log position replayed into the database on this standby server diff with current lsn" }
    - sent_lsn:          { usage: COUNTER ,description: "Last write-ahead log location sent on this connection" }
    - write_lsn:         { usage: COUNTER ,description: "Last write-ahead log location written to disk by this standby server" }
    - flush_lsn:         { usage: COUNTER ,description: "Last write-ahead log location flushed to disk by this standby server" }
    - replay_lsn:        { usage: COUNTER ,description: "Last write-ahead log location replayed into the database on this standby server" }
    - write_lag:         { usage: GAUGE   ,description: "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written it (N/A on 9.x)" }
    - flush_lag:         { usage: GAUGE   ,description: "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written and flushed it (N/A on 9.x)" }
    - replay_lag:        { usage: GAUGE   ,description: "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written, flushed and applied it (N/A on 9.x)" }
    - time:              { usage: COUNTER ,description: "Current timestamp in unix epoch" }
    - launch_time:       { usage: COUNTER ,description: "Time when this process was started, i.e., when the client connected to this WAL sender" }

pg_repl_92:
  name: pg_repl
  desc: PostgreSQL replication stat metrics 9.2 - 9.3
  query: |-
    SELECT appname, usename, address, pid, client_port, state, sync_state, sync_priority, backend_xmin, lsn,
           lsn - sent_lsn  AS sent_diff, lsn - write_lsn AS write_diff, lsn - flush_lsn AS flush_diff, lsn - replay_lsn AS replay_diff,
           sent_lsn, write_lsn, flush_lsn, replay_lsn,
           0::FLOAT AS write_lag, 0::FLOAT AS flush_lag, 0::FLOAT AS replay_lag,
           extract(EPOCH FROM current_timestamp) AS "time", extract(EPOCH FROM backend_start) AS launch_time
    FROM (
      SELECT application_name AS appname, usename, coalesce(client_addr::TEXT,'localhost') AS address, pid::TEXT, client_port,
             CASE state WHEN 'streaming' THEN 0 WHEN 'startup' THEN 1 WHEN 'catchup' THEN 2 WHEN 'backup' THEN 3 WHEN 'stopping' THEN 4 ELSE -1 END AS state,
             CASE sync_state WHEN 'async' THEN 0 WHEN 'potential' THEN 1 WHEN 'sync' THEN 2 WHEN 'quorum' THEN 3 ELSE -1 END AS sync_state,
             sync_priority, 0::BIGINT AS backend_xmin,
             (('x' || lpad(split_part(current.loc::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(current.loc::text, '/', 2), 8, '0'))::bit(32)::bigint) AS lsn,
             (('x' || lpad(split_part(sent_location::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(sent_location::text, '/', 2), 8, '0'))::bit(32)::bigint) AS sent_lsn,
             (('x' || lpad(split_part(write_location::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(write_location::text, '/', 2), 8, '0'))::bit(32)::bigint) AS write_lsn,
             (('x' || lpad(split_part(flush_location::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(flush_location::text, '/', 2), 8, '0'))::bit(32)::bigint) AS flush_lsn,
             (('x' || lpad(split_part(replay_location::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(replay_location::text, '/', 2), 8, '0'))::bit(32)::bigint) AS replay_lsn,
             backend_start
      FROM pg_stat_replication,
           (SELECT CASE WHEN pg_is_in_recovery() THEN pg_last_xlog_replay_location() ELSE pg_current_xlog_location() END AS loc) current
    ) d;

  ttl: 10
  min_version: 90200
  max_version: 90400
  tags: [ cluster ]
  metrics:
    - appname:           { usage: LABEL   ,description: "Name of the application that is connected to this WAL sender" }
    - usename:           { usage: LABEL   ,description: "Name of the user logged into this WAL sender process" }
    - address:           { usage: LABEL   ,description: "IP address of the client connected to this WAL sender, localhost for unix socket" }
    - pid:               { usage: LABEL   ,description: "Process ID of the WAL sender process" }
    - client_port:       { usage: GAUGE   ,description: "TCP port number that the client is using for communication with this WAL sender, or -1 if a Unix socket is used" }
    - state:             { usage: GAUGE   ,description: "Current WAL sender encoded state 0-4 for streaming|startup|catchup|backup|stopping" }
    - sync_state:        { usage: GAUGE   ,description: "Encoded synchronous state of this standby server, 0-3 for async|potential|sync|quorum" }
    - sync_priority:     { usage: GAUGE   ,description: "Priority of this standby server for being chosen as the synchronous standby" }
    - backend_xmin:      { usage: COUNTER ,description: "This standby's xmin horizon reported by hot_standby_feedback (N/A before 9.4)" }
    - lsn:               { usage: COUNTER ,description: "Current log position on this server" }
    - sent_diff:         { usage: GAUGE   ,description: "Last log position sent to this standby server diff with current lsn" }
    - write_diff:        { usage: GAUGE   ,description: "Last log position written to disk by this standby server diff with current lsn" }
    - flush_diff:        { usage: GAUGE   ,description: "Last log position flushed to disk by this standby server diff with current lsn" }
    - replay_diff:       { usage: GAUGE   ,description: "Last log position replayed into the database on this standby server diff with current lsn" }
    - sent_lsn:          { usage: COUNTER ,description: "Last write-ahead log location sent on this connection" }
    - write_lsn:         { usage: COUNTER ,description: "Last write-ahead log location written to disk by this standby server" }
    - flush_lsn:         { usage: COUNTER ,description: "Last write-ahead log location flushed to disk by this standby server" }
    - replay_lsn:        { usage: COUNTER ,description: "Last write-ahead log location replayed into the database on this standby server" }
    - write_lag:         { usage: GAUGE   ,description: "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written it (N/A on 9.x)" }
    - flush_lag:         { usage: GAUGE   ,description: "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written and flushed it (N/A on 9.x)" }
    - replay_lag:        { usage: GAUGE   ,description: "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written, flushed and applied it (N/A on 9.x)" }
    - time:              { usage: COUNTER ,description: "Current timestamp in unix epoch" }
    - launch_time:       { usage: COUNTER ,description: "Time when this process was started, i.e., when the client connected to this WAL sender" }

pg_repl_91:
  name: pg_repl
  desc: PostgreSQL replication stat metrics 9.1 (procpid, no state/sync columns)
  query: |-
    SELECT appname, usename, address, pid, client_port, state, sync_state, sync_priority, backend_xmin, lsn,
           lsn - sent_lsn  AS sent_diff, lsn - write_lsn AS write_diff, lsn - flush_lsn AS flush_diff, lsn - replay_lsn AS replay_diff,
           sent_lsn, write_lsn, flush_lsn, replay_lsn,
           0::FLOAT AS write_lag, 0::FLOAT AS flush_lag, 0::FLOAT AS replay_lag,
           extract(EPOCH FROM current_timestamp) AS "time", extract(EPOCH FROM backend_start) AS launch_time
    FROM (
      SELECT application_name AS appname, usename, coalesce(client_addr::TEXT,'localhost') AS address, procpid::TEXT AS pid, client_port,
             0::INT AS state, 0::INT AS sync_state, 0::INT AS sync_priority, 0::BIGINT AS backend_xmin,
             (('x' || lpad(split_part(current.loc::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(current.loc::text, '/', 2), 8, '0'))::bit(32)::bigint) AS lsn,
             (('x' || lpad(split_part(sent_location::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(sent_location::text, '/', 2), 8, '0'))::bit(32)::bigint) AS sent_lsn,
             (('x' || lpad(split_part(write_location::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(write_location::text, '/', 2), 8, '0'))::bit(32)::bigint) AS write_lsn,
             (('x' || lpad(split_part(flush_location::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(flush_location::text, '/', 2), 8, '0'))::bit(32)::bigint) AS flush_lsn,
             (('x' || lpad(split_part(replay_location::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
              + ('x' || lpad(split_part(replay_location::text, '/', 2), 8, '0'))::bit(32)::bigint) AS replay_lsn,
             backend_start
      FROM pg_stat_replication,
           (SELECT CASE WHEN pg_is_in_recovery() THEN pg_last_xlog_replay_location() ELSE pg_current_xlog_location() END AS loc) current
    ) d;

  ttl: 10
  min_version: 90100
  max_version: 90200
  tags: [ cluster ]
  metrics:
    - appname:           { usage: LABEL   ,description: "Name of the application that is connected to this WAL sender" }
    - usename:           { usage: LABEL   ,description: "Name of the user logged into this WAL sender process" }
    - address:           { usage: LABEL   ,description: "IP address of the client connected to this WAL sender, localhost for unix socket" }
    - pid:               { usage: LABEL   ,description: "Process ID of the WAL sender process" }
    - client_port:       { usage: GAUGE   ,description: "TCP port number that the client is using for communication with this WAL sender, or -1 if a Unix socket is used" }
    - state:             { usage: GAUGE   ,description: "Current WAL sender encoded state 0-4 for streaming|startup|catchup|backup|stopping" }
    - sync_state:        { usage: GAUGE   ,description: "Encoded synchronous state of this standby server, 0-3 for async|potential|sync|quorum" }
    - sync_priority:     { usage: GAUGE   ,description: "Priority of this standby server for being chosen as the synchronous standby" }
    - backend_xmin:      { usage: COUNTER ,description: "This standby's xmin horizon reported by hot_standby_feedback (N/A before 9.4)" }
    - lsn:               { usage: COUNTER ,description: "Current log position on this server" }
    - sent_diff:         { usage: GAUGE   ,description: "Last log position sent to this standby server diff with current lsn" }
    - write_diff:        { usage: GAUGE   ,description: "Last log position written to disk by this standby server diff with current lsn" }
    - flush_diff:        { usage: GAUGE   ,description: "Last log position flushed to disk by this standby server diff with current lsn" }
    - replay_diff:       { usage: GAUGE   ,description: "Last log position replayed into the database on this standby server diff with current lsn" }
    - sent_lsn:          { usage: COUNTER ,description: "Last write-ahead log location sent on this connection" }
    - write_lsn:         { usage: COUNTER ,description: "Last write-ahead log location written to disk by this standby server" }
    - flush_lsn:         { usage: COUNTER ,description: "Last write-ahead log location flushed to disk by this standby server" }
    - replay_lsn:        { usage: COUNTER ,description: "Last write-ahead log location replayed into the database on this standby server" }
    - write_lag:         { usage: GAUGE   ,description: "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written it (N/A on 9.x)" }
    - flush_lag:         { usage: GAUGE   ,description: "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written and flushed it (N/A on 9.x)" }
    - replay_lag:        { usage: GAUGE   ,description: "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written, flushed and applied it (N/A on 9.x)" }
    - time:              { usage: COUNTER ,description: "Current timestamp in unix epoch" }
    - launch_time:       { usage: COUNTER ,description: "Time when this process was started, i.e., when the client connected to this WAL sender" }


#==============================================================#
# 0220 pg_sync_standby
#==============================================================#
pg_sync_standby:
  name: pg_sync_standby
  desc: PostgreSQL synchronous standby status and names
  query: |-
    SELECT
      CASE WHEN names <> '' THEN names ELSE '<null>' END AS names,
      CASE WHEN names <> '' THEN 1 ELSE 0 END           AS enabled
    FROM (SELECT current_setting('synchronous_standby_names') AS names) n;
  ttl: 10
  min_version: 90100
  tags: [ cluster ]
  metrics:
    - names:   { usage: LABEL ,description: "List of standby servers that can support synchronous replication" }
    - enabled: { usage: GAUGE ,description: "Synchronous commit enabled, 1 if enabled, 0 if disabled" }


#==============================================================#
# 0230 pg_downstream
#==============================================================#
pg_downstream:
  name: pg_downstream
  desc: PostgreSQL replication client count (no state column on 9.1)
  query: |-
    SELECT 'connected' AS state, count(*) AS count FROM pg_stat_replication;
  ttl: 10
  min_version: 90100
  max_version: 90200
  tags: [ cluster ]
  metrics:
    - state: { usage: LABEL ,description: "Replication client state" }
    - count: { usage: GAUGE ,description: "Count of replication clients by state" }

pg_downstream_92:
  name: pg_downstream
  desc: PostgreSQL replication client count (group by state)
  query: |-
    SELECT state, count(*) AS count FROM pg_stat_replication GROUP BY state;
  ttl: 10
  min_version: 90200
  tags: [ cluster ]
  metrics:
    - state: { usage: LABEL ,description: "Replication client state" }
    - count: { usage: GAUGE ,description: "Count of replication clients by state" }


#==============================================================#
# 0240 pg_slot
#==============================================================#
pg_slot_96:
  name: pg_slot
  desc: PostgreSQL replication slot metrics 9.6 (with active_pid, confirmed_flush_lsn)
  query: |-
    SELECT slot_name, slot_type, plugin, database AS datname, datoid, active_pid,
      active, FALSE AS temporary,
      xmin::TEXT::BIGINT AS xmin, catalog_xmin::TEXT::BIGINT AS catalog_xmin,
      restart_lsn, confirm_lsn, current_lsn - restart_lsn AS retained_bytes
    FROM (
      SELECT slot_name, slot_type, plugin, database, datoid, active_pid, active, xmin, catalog_xmin,
        (('x' || lpad(split_part(restart_lsn::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
         + ('x' || lpad(split_part(restart_lsn::text, '/', 2), 8, '0'))::bit(32)::bigint) AS restart_lsn,
        (('x' || lpad(split_part(confirmed_flush_lsn::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
         + ('x' || lpad(split_part(confirmed_flush_lsn::text, '/', 2), 8, '0'))::bit(32)::bigint) AS confirm_lsn,
        (('x' || lpad(split_part(current.loc::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
         + ('x' || lpad(split_part(current.loc::text, '/', 2), 8, '0'))::bit(32)::bigint) AS current_lsn
      FROM pg_replication_slots,
           (SELECT CASE WHEN pg_is_in_recovery() THEN pg_last_xlog_replay_location() ELSE pg_current_xlog_location() END AS loc) current
    ) d;

  ttl: 10
  min_version: 90600
  max_version: 100000
  tags: [ cluster, primary ]
  metrics:
    - slot_name:      { usage: LABEL    ,description: "A unique, cluster-wide identifier for the replication slot" }
    - slot_type:      { usage: LABEL    ,description: "The slot type, physical or logical" }
    - plugin:         { usage: LABEL    ,description: "The base name of the shared object containing the output plugin this logical slot is using, or null for physical slots." }
    - datname:        { usage: LABEL    ,description: "The name of the database this slot is associated with, logical slots only, null for physical slot" }
    - datoid:         { usage: GAUGE    ,description: "The OID of the database this slot is associated with, logical slots only, null for physical slot" }
    - active_pid:     { usage: GAUGE    ,description: "The process ID of the session streaming data for this slot. NULL if inactive." }
    - active:         { usage: GAUGE    ,description: "True(1) if this slot is currently actively being used" }
    - temporary:      { usage: GAUGE    ,description: "True(1) if this is a temporary replication slot (N/A on 9.x, always 0)" }
    - xmin:           { usage: COUNTER  ,description: "The oldest transaction that this slot needs the database to retain." }
    - catalog_xmin:   { usage: COUNTER  ,description: "The oldest transaction affecting the system catalogs that this slot needs the database to retain." }
    - restart_lsn:    { usage: COUNTER  ,description: "The address (LSN) of oldest WAL which still might be required by the consumer of this slot" }
    - confirm_lsn:    { usage: COUNTER  ,description: "The address (LSN) up to which the logical slot's consumer has confirmed receiving data." }
    - retained_bytes: { usage: GAUGE    ,description: "Size of bytes that retained for this slot" }

pg_slot_95:
  name: pg_slot
  desc: PostgreSQL replication slot metrics 9.5 (no confirmed_flush_lsn)
  query: |-
    SELECT slot_name, slot_type, plugin, database AS datname, datoid, active_pid,
      active, FALSE AS temporary,
      xmin::TEXT::BIGINT AS xmin, catalog_xmin::TEXT::BIGINT AS catalog_xmin,
      restart_lsn, confirm_lsn, current_lsn - restart_lsn AS retained_bytes
    FROM (
      SELECT slot_name, slot_type, plugin, database, datoid, active_pid, active, xmin, catalog_xmin,
        (('x' || lpad(split_part(restart_lsn::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
         + ('x' || lpad(split_part(restart_lsn::text, '/', 2), 8, '0'))::bit(32)::bigint) AS restart_lsn,
        0::BIGINT AS confirm_lsn,
        (('x' || lpad(split_part(current.loc::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
         + ('x' || lpad(split_part(current.loc::text, '/', 2), 8, '0'))::bit(32)::bigint) AS current_lsn
      FROM pg_replication_slots,
           (SELECT CASE WHEN pg_is_in_recovery() THEN pg_last_xlog_replay_location() ELSE pg_current_xlog_location() END AS loc) current
    ) d;

  ttl: 10
  min_version: 90500
  max_version: 90600
  tags: [ cluster, primary ]
  metrics:
    - slot_name:      { usage: LABEL    ,description: "A unique, cluster-wide identifier for the replication slot" }
    - slot_type:      { usage: LABEL    ,description: "The slot type, physical or logical" }
    - plugin:         { usage: LABEL    ,description: "The base name of the shared object containing the output plugin this logical slot is using, or null for physical slots." }
    - datname:        { usage: LABEL    ,description: "The name of the database this slot is associated with, logical slots only, null for physical slot" }
    - datoid:         { usage: GAUGE    ,description: "The OID of the database this slot is associated with, logical slots only, null for physical slot" }
    - active_pid:     { usage: GAUGE    ,description: "The process ID of the session streaming data for this slot. NULL if inactive." }
    - active:         { usage: GAUGE    ,description: "True(1) if this slot is currently actively being used" }
    - temporary:      { usage: GAUGE    ,description: "True(1) if this is a temporary replication slot (N/A on 9.x, always 0)" }
    - xmin:           { usage: COUNTER  ,description: "The oldest transaction that this slot needs the database to retain." }
    - catalog_xmin:   { usage: COUNTER  ,description: "The oldest transaction affecting the system catalogs that this slot needs the database to retain." }
    - restart_lsn:    { usage: COUNTER  ,description: "The address (LSN) of oldest WAL which still might be required by the consumer of this slot" }
    - confirm_lsn:    { usage: COUNTER  ,description: "Confirmed flush lsn (N/A before 9.6, always 0)" }
    - retained_bytes: { usage: GAUGE    ,description: "Size of bytes that retained for this slot" }

pg_slot_94:
  name: pg_slot
  desc: PostgreSQL replication slot metrics 9.4 (no active_pid, confirmed_flush_lsn)
  query: |-
    SELECT slot_name, slot_type, plugin, database AS datname, datoid, active_pid,
      active, FALSE AS temporary,
      xmin::TEXT::BIGINT AS xmin, catalog_xmin::TEXT::BIGINT AS catalog_xmin,
      restart_lsn, confirm_lsn, current_lsn - restart_lsn AS retained_bytes
    FROM (
      SELECT slot_name, slot_type, plugin, database, datoid, NULL::INT AS active_pid, active, xmin, catalog_xmin,
        (('x' || lpad(split_part(restart_lsn::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
         + ('x' || lpad(split_part(restart_lsn::text, '/', 2), 8, '0'))::bit(32)::bigint) AS restart_lsn,
        0::BIGINT AS confirm_lsn,
        (('x' || lpad(split_part(current.loc::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
         + ('x' || lpad(split_part(current.loc::text, '/', 2), 8, '0'))::bit(32)::bigint) AS current_lsn
      FROM pg_replication_slots,
           (SELECT CASE WHEN pg_is_in_recovery() THEN pg_last_xlog_replay_location() ELSE pg_current_xlog_location() END AS loc) current
    ) d;

  ttl: 10
  min_version: 90400
  max_version: 90500
  tags: [ cluster, primary ]
  metrics:
    - slot_name:      { usage: LABEL    ,description: "A unique, cluster-wide identifier for the replication slot" }
    - slot_type:      { usage: LABEL    ,description: "The slot type, physical or logical" }
    - plugin:         { usage: LABEL    ,description: "The base name of the shared object containing the output plugin this logical slot is using, or null for physical slots." }
    - datname:        { usage: LABEL    ,description: "The name of the database this slot is associated with, logical slots only, null for physical slot" }
    - datoid:         { usage: GAUGE    ,description: "The OID of the database this slot is associated with, logical slots only, null for physical slot" }
    - active_pid:     { usage: GAUGE    ,description: "Process ID is not available before 9.5 (NULL)" }
    - active:         { usage: GAUGE    ,description: "True(1) if this slot is currently actively being used" }
    - temporary:      { usage: GAUGE    ,description: "True(1) if this is a temporary replication slot (N/A on 9.x, always 0)" }
    - xmin:           { usage: COUNTER  ,description: "The oldest transaction that this slot needs the database to retain." }
    - catalog_xmin:   { usage: COUNTER  ,description: "The oldest transaction affecting the system catalogs that this slot needs the database to retain." }
    - restart_lsn:    { usage: COUNTER  ,description: "The address (LSN) of oldest WAL which still might be required by the consumer of this slot" }
    - confirm_lsn:    { usage: COUNTER  ,description: "Confirmed flush lsn (N/A before 9.6, always 0)" }
    - retained_bytes: { usage: GAUGE    ,description: "Size of bytes that retained for this slot" }


#==============================================================#
# 0250 pg_recv
#==============================================================#
pg_recv_96:
  name: pg_recv
  desc: PostgreSQL walreceiver metrics (9.6 - 12)
  query: |-
    SELECT 
      (regexp_match(conninfo, '.*host=(\S+).*'))[1] AS sender_host,
      (regexp_match(conninfo, '.*port=(\S+).*'))[1] AS sender_port,
      coalesce(slot_name, 'NULL')                    AS slot_name,
      pid,
      CASE status WHEN 'streaming' THEN 0 WHEN 'startup' THEN 1 WHEN 'catchup' THEN 2 WHEN 'backup' THEN 3 WHEN 'stopping' THEN 4 ELSE -1 END AS state,
      (('x' || lpad(split_part(receive_start_lsn::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(receive_start_lsn::text, '/', 2), 8, '0'))::bit(32)::bigint) AS init_lsn,
      receive_start_tli AS init_tli,
      (('x' || lpad(split_part(received_lsn::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(received_lsn::text, '/', 2), 8, '0'))::bit(32)::bigint)      AS flush_lsn,
      received_tli       AS flush_tli,
      (('x' || lpad(split_part(latest_end_lsn::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(latest_end_lsn::text, '/', 2), 8, '0'))::bit(32)::bigint)    AS reported_lsn,
      last_msg_send_time    AS msg_send_time,
      last_msg_receipt_time AS msg_recv_time,
      latest_end_time       AS reported_time,
      now()                 AS time
    FROM pg_stat_wal_receiver;

  ttl: 10
  min_version: 90600
  max_version: 130000
  tags: [ cluster, replica ]
  metrics:
    - sender_host:         { usage: LABEL   ,description: "Host of the PostgreSQL instance this WAL receiver is connected to" }
    - sender_port:         { usage: LABEL   ,description: "Port number of the PostgreSQL instance this WAL receiver is connected to." }
    - slot_name:           { usage: LABEL   ,description: "Replication slot name used by this WAL receiver" }
    - pid:                 { usage: GAUGE   ,description: "Process ID of the WAL receiver process" }
    - state:               { usage: GAUGE   ,description: "Encoded activity status of the WAL receiver process 0-4 for streaming|startup|catchup|backup|stopping" }
    - init_lsn:            { usage: COUNTER ,description: "First write-ahead log location used when WAL receiver is started" }
    - init_tli:            { usage: COUNTER ,description: "First timeline number used when WAL receiver is started" }
    - flush_lsn:           { usage: COUNTER ,description: "Last write-ahead log location already received and flushed to disk" }
    - flush_tli:           { usage: COUNTER ,description: "Timeline number of last write-ahead log location received and flushed to disk" }
    - reported_lsn:        { usage: COUNTER ,description: "Last write-ahead log location reported to origin WAL sender" }
    - msg_send_time:       { usage: GAUGE   ,description: "Send time of last message received from origin WAL sender" }
    - msg_recv_time:       { usage: GAUGE   ,description: "Receipt time of last message received from origin WAL sender" }
    - reported_time:       { usage: GAUGE   ,description: "Time of last write-ahead log location reported to origin WAL sender" }
    - time:                { usage: GAUGE   ,description: "Time of current snapshot" }


#==============================================================#
# 0270 pg_origin
#==============================================================#
# skip by default, require additional privilege setup
# GRANT SELECT ON pg_replication_origin, pg_replication_origin_status TO pg_monitor;
pg_origin:
  name: pg_origin
  desc: PostgreSQL replay state (approximate) for a certain origin
  query: |-
    SELECT roname,
      (('x' || lpad(split_part(remote_lsn::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(remote_lsn::text, '/', 2), 8, '0'))::bit(32)::bigint) AS remote_lsn,
      (('x' || lpad(split_part(local_lsn::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(local_lsn::text, '/', 2), 8, '0'))::bit(32)::bigint) AS local_lsn
    FROM pg_replication_origin o LEFT JOIN pg_replication_origin_status os ON o.roident = os.local_id;
  ttl: 10
  min_version: 90500
  skip: true
  tags: [ cluster ]
  metrics:
    - roname:              { usage: LABEL     ,description: "The external, user defined, name of a replication origin." }
    - remote_lsn:          { usage: COUNTER   ,description: "The origin node's LSN up to which data has been replicated." }
    - local_lsn:           { usage: COUNTER   ,description: "This node's LSN at which remote_lsn has been replicated." }


#==============================================================#
# 0310 pg_size
#==============================================================#
pg_size:
  name: pg_size
  desc: PostgreSQL database size (legacy 9.1-9.6)
  query: |-
    SELECT datname, pg_database_size(oid) AS bytes FROM pg_database;
  ttl: 60
  timeout: 1
  min_version: 90100
  tags: [ cluster ]
  metrics:
    - datname: { usage: LABEL ,description: "Database name" }
    - bytes:   { usage: GAUGE ,description: "Database size in bytes" }


#==============================================================#
# 0320 pg_archiver
#==============================================================#
pg_archiver:
  name: pg_archiver
  desc: PostgreSQL archiver process statistics
  query: |-
    SELECT archived_count AS finish_count,failed_count,
      extract(epoch FROM last_archived_time) AS finish_time,
      extract(epoch FROM last_failed_time) AS failed_time,
      extract(epoch FROM stats_reset) AS reset_time
    FROM pg_stat_archiver;

  ttl: 60
  min_version: 90400
  tags: [ cluster ]
  metrics:
    - finish_count:        { usage: COUNTER ,description: "Number of WAL files that have been successfully archived" }
    - failed_count:        { usage: COUNTER ,description: "Number of failed attempts for archiving WAL files" }
    - finish_time:         { usage: GAUGE   ,description: "Time of the last successful archive operation" }
    - failed_time:         { usage: GAUGE   ,description: "Time of the last failed archival operation" }
    - reset_time:          { usage: GAUGE   ,description: "Time at which archive statistics were last reset" }


#==============================================================#
# 0330 pg_bgwriter
#==============================================================#
# https://pgpedia.info/p/pg_stat_bgwriter.html
pg_bgwriter_94:
  name: pg_bgwriter
  desc: "PostgreSQL background writer metrics (PG 9.4-16)"
  query: SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, buffers_clean, buffers_backend, maxwritten_clean, buffers_backend_fsync, buffers_alloc, extract(EPOCH FROM stats_reset) AS reset_time FROM pg_stat_bgwriter;
  ttl: 10
  min_version: 90400
  max_version: 170000
  tags: [ cluster ]
  metrics:
    - checkpoints_timed:     { usage: COUNTER              ,description: "Number of scheduled checkpoints that have been performed" }
    - checkpoints_req:       { usage: COUNTER              ,description: "Number of requested checkpoints that have been performed" }
    - checkpoint_write_time: { usage: COUNTER ,scale: 1e-3 ,description: "Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in seconds" }
    - checkpoint_sync_time:  { usage: COUNTER ,scale: 1e-3 ,description: "Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk, in seconds" }
    - buffers_checkpoint:    { usage: COUNTER              ,description: "Number of buffers written during checkpoints" }
    - buffers_clean:         { usage: COUNTER              ,description: "Number of buffers written by the background writer" }
    - buffers_backend:       { usage: COUNTER              ,description: "Number of buffers written directly by a backend" }
    - maxwritten_clean:      { usage: COUNTER              ,description: "Number of times the background writer stopped a cleaning scan because it had written too many buffers" }
    - buffers_backend_fsync: { usage: COUNTER              ,description: "Number of times a backend had to execute its own fsync call" }
    - buffers_alloc:         { usage: COUNTER              ,description: "Number of buffers allocated" }
    - reset_time:            { usage: GAUGE                ,description: "Time at which bgwriter statistics were last reset" }

pg_bgwriter_91:
  name: pg_bgwriter
  desc: "PostgreSQL background writer metrics (PG 9.1-9.3)"
  query: SELECT checkpoints_timed, checkpoints_req, 0::BIGINT AS checkpoint_write_time, 0::BIGINT AS checkpoint_sync_time, buffers_checkpoint, buffers_clean, buffers_backend, maxwritten_clean, buffers_backend_fsync, buffers_alloc, extract(EPOCH FROM stats_reset) AS reset_time FROM pg_stat_bgwriter;
  ttl: 10
  min_version: 90100
  max_version: 90400
  tags: [ cluster ]
  metrics:
    - checkpoints_timed:     { usage: COUNTER              ,description: "Number of scheduled checkpoints that have been performed" }
    - checkpoints_req:       { usage: COUNTER              ,description: "Number of requested checkpoints that have been performed" }
    - checkpoint_write_time: { usage: COUNTER ,scale: 1e-3 ,description: "Total amount of time spent writing checkpoint files, in seconds (N/A on 9.1-9.3, always 0)" }
    - checkpoint_sync_time:  { usage: COUNTER ,scale: 1e-3 ,description: "Total amount of time spent syncing checkpoint files, in seconds (N/A on 9.1-9.3, always 0)" }
    - buffers_checkpoint:    { usage: COUNTER              ,description: "Number of buffers written during checkpoints" }
    - buffers_clean:         { usage: COUNTER              ,description: "Number of buffers written by the background writer" }
    - buffers_backend:       { usage: COUNTER              ,description: "Number of buffers written directly by a backend" }
    - maxwritten_clean:      { usage: COUNTER              ,description: "Number of times the background writer stopped a cleaning scan because it had written too many buffers" }
    - buffers_backend_fsync: { usage: COUNTER              ,description: "Number of times a backend had to execute its own fsync call" }
    - buffers_alloc:         { usage: COUNTER              ,description: "Number of buffers allocated" }
    - reset_time:            { usage: GAUGE                ,description: "Time at which bgwriter statistics were last reset" }


#==============================================================#
# 0331 pg_checkpointer
#==============================================================#
pg_checkpointer_94:
  name: pg_checkpointer
  desc: "PostgreSQL checkpointer stat metrics for pg 9.4-16"
  query: SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, extract(EPOCH FROM stats_reset) AS reset_time FROM pg_stat_bgwriter;
  ttl: 10
  min_version: 90400
  max_version: 170000
  tags: [ cluster ]
  metrics:
    - checkpoints_timed:     { usage: COUNTER ,rename: timed                   ,description: "Number of scheduled checkpoints that have been performed" }
    - checkpoints_req:       { usage: COUNTER ,rename: req                     ,description: "Number of requested checkpoints that have been performed" }
    - checkpoint_write_time: { usage: COUNTER ,rename: write_time ,scale: 1e-3 ,description: "Total amount of time that has been spent writing checkpoint files, in seconds" }
    - checkpoint_sync_time:  { usage: COUNTER ,rename: sync_time  ,scale: 1e-3 ,description: "Total amount of time that has been spent synchronizing checkpoint files to disk, in seconds" }
    - buffers_checkpoint:    { usage: COUNTER ,rename: buffers_written         ,description: "Number of buffers written during checkpoints and restartpoints" }
    - reset_time:            { usage: GAUGE                                    ,description: "Time at which checkpointer statistics were last reset" }

pg_checkpointer_91:
  name: pg_checkpointer
  desc: "PostgreSQL checkpointer stat metrics for pg 9.1-9.3"
  query: SELECT checkpoints_timed, checkpoints_req, 0::BIGINT AS checkpoint_write_time, 0::BIGINT AS checkpoint_sync_time, buffers_checkpoint, extract(EPOCH FROM stats_reset) AS reset_time FROM pg_stat_bgwriter;
  ttl: 10
  min_version: 90100
  max_version: 90400
  tags: [ cluster ]
  metrics:
    - checkpoints_timed:     { usage: COUNTER ,rename: timed                   ,description: "Number of scheduled checkpoints that have been performed" }
    - checkpoints_req:       { usage: COUNTER ,rename: req                     ,description: "Number of requested checkpoints that have been performed" }
    - checkpoint_write_time: { usage: COUNTER ,rename: write_time ,scale: 1e-3 ,description: "Total amount of time that has been spent writing checkpoint files, in seconds (N/A on 9.1-9.3, always 0)" }
    - checkpoint_sync_time:  { usage: COUNTER ,rename: sync_time  ,scale: 1e-3 ,description: "Total amount of time that has been spent synchronizing checkpoint files to disk, in seconds (N/A on 9.1-9.3, always 0)" }
    - buffers_checkpoint:    { usage: COUNTER ,rename: buffers_written         ,description: "Number of buffers written during checkpoints and restartpoints" }
    - reset_time:            { usage: GAUGE                                    ,description: "Time at which checkpointer statistics were last reset" }


#==============================================================#
# 0340 pg_ssl
#==============================================================#
pg_ssl:
  name: pg_ssl
  desc: PostgreSQL SSL client connection count
  query: |
    SELECT count(*) FILTER (WHERE ssl) AS enabled, count(*) FILTER ( WHERE NOT ssl) AS disabled FROM pg_stat_ssl;
  ttl: 10
  min_version: 90500
  tags: [ cluster ]
  metrics:
    - enabled:            { usage: GAUGE   ,description: "Number of client connection that use ssl" }
    - disabled:           { usage: GAUGE   ,description: "Number of client connection that does not use ssl" }


#==============================================================#
# 0350 pg_checkpoint
#==============================================================#
pg_checkpoint:
  name: pg_checkpoint
  desc: checkpoint information from pg_control_checkpoint (9.6)
  query: |-
    SELECT 
      (('x' || lpad(split_part(checkpoint_location::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(checkpoint_location::text, '/', 2), 8, '0'))::bit(32)::bigint) AS checkpoint_lsn,
      (('x' || lpad(split_part(redo_location::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(redo_location::text, '/', 2), 8, '0'))::bit(32)::bigint)       AS redo_lsn,
      timeline_id AS tli,
      prev_timeline_id AS prev_tli,
      full_page_writes,
      split_part(next_xid, ':', 1) AS next_xid_epoch,
      split_part(next_xid, ':', 2) AS next_xid,
      next_oid::BIGINT,
      next_multixact_id::text::BIGINT,
      next_multi_offset::text::BIGINT,
      oldest_xid::text::BIGINT,
      oldest_xid_dbid::text::BIGINT,
      oldest_active_xid::text::BIGINT,
      oldest_multi_xid::text::BIGINT,
      oldest_multi_dbid::BIGINT,
      oldest_commit_ts_xid::text::BIGINT,
      newest_commit_ts_xid::text::BIGINT,
      checkpoint_time                             AS time,
      extract(epoch from now() - checkpoint_time) AS elapse
    FROM pg_control_checkpoint();

  ttl: 60
  min_version: 90600
  tags: [ cluster ]
  metrics:
    - checkpoint_lsn:       { usage: COUNTER ,description: "Latest checkpoint location" }
    - redo_lsn:             { usage: COUNTER ,description: "Latest checkpoint's REDO location" }
    - tli:                  { usage: COUNTER ,description: "Latest checkpoint's TimeLineID" }
    - prev_tli:             { usage: COUNTER ,description: "Latest checkpoint's PrevTimeLineID" }
    - full_page_writes:     { usage: GAUGE   ,description: "Latest checkpoint's full_page_writes enabled" }
    - next_xid_epoch:       { usage: COUNTER ,description: "Latest checkpoint's NextXID epoch" }
    - next_xid:             { usage: COUNTER ,description: "Latest checkpoint's NextXID xid" }
    - next_oid:             { usage: COUNTER ,description: "Latest checkpoint's NextOID" }
    - next_multixact_id:    { usage: COUNTER ,description: "Latest checkpoint's NextMultiXactId" }
    - next_multi_offset:    { usage: COUNTER ,description: "Latest checkpoint's NextMultiOffset" }
    - oldest_xid:           { usage: COUNTER ,description: "Latest checkpoint's oldestXID" }
    - oldest_xid_dbid:      { usage: GAUGE   ,description: "Latest checkpoint's oldestXID's DB OID" }
    - oldest_active_xid:    { usage: COUNTER ,description: "Latest checkpoint's oldestActiveXID" }
    - oldest_multi_xid:     { usage: COUNTER ,description: "Latest checkpoint's oldestMultiXid" }
    - oldest_multi_dbid:    { usage: GAUGE   ,description: "Latest checkpoint's oldestMulti's DB OID" }
    - oldest_commit_ts_xid: { usage: COUNTER ,description: "Latest checkpoint's oldestCommitTsXid" }
    - newest_commit_ts_xid: { usage: COUNTER ,description: "Latest checkpoint's newestCommitTsXid" }
    - time:                 { usage: COUNTER ,description: "Time of latest checkpoint" }
    - elapse:               { usage: GAUGE   ,description: "Seconds elapsed since latest checkpoint in seconds" }


#==============================================================#
# 0355 pg_timeline
#==============================================================#
pg_timeline:
  name: pg_timeline
  desc: Current timeline ID from primary or replica
  query: |
    SELECT COALESCE(
      (SELECT received_tli FROM pg_stat_wal_receiver),
      (SELECT timeline_id FROM pg_control_checkpoint())
    ) AS id;
  ttl: 10
  min_version: 90600
  tags: [ cluster ]
  metrics:
    - id: { usage: GAUGE ,description: "Current timeline ID" }


#==============================================================#
# 0360 pg_recovery
#==============================================================#
pg_recovery:
  name: pg_recovery
  desc: PostgreSQL control recovery metrics (9.6)
  query: |
    SELECT min_recovery_end_timeline AS min_timeline,
      (('x' || lpad(split_part(min_recovery_end_lsn::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(min_recovery_end_lsn::text, '/', 2), 8, '0'))::bit(32)::bigint) AS min_lsn,
      (('x' || lpad(split_part(backup_start_lsn::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(backup_start_lsn::text, '/', 2), 8, '0'))::bit(32)::bigint)     AS backup_start_lsn,
      (('x' || lpad(split_part(backup_end_lsn::text, '/', 1), 8, '0'))::bit(32)::bigint * 4294967296
       + ('x' || lpad(split_part(backup_end_lsn::text, '/', 2), 8, '0'))::bit(32)::bigint)       AS backup_end_lsn,
      end_of_backup_record_required AS require_record
    FROM pg_control_recovery();
  ttl: 10
  min_version: 90600
  tags: [ cluster, replica ]
  metrics:
    - min_timeline:      { usage: COUNTER ,description: "Min recovery ending loc's timeline" }
    - min_lsn:           { usage: COUNTER ,description: "Minimum recovery ending location" }
    - backup_start_lsn:  { usage: COUNTER ,description: "Backup start location" }
    - backup_end_lsn:    { usage: COUNTER ,description: "Backup end location" }
    - require_record:    { usage: GAUGE   ,description: "End-of-backup record required" }


#==============================================================#
# 0410 pg_activity
#==============================================================#
pg_activity_92:
  name: pg_activity
  desc: PostgreSQL backend activity group by database and state (9.2+)
  query: |-
    SELECT datname, state, coalesce(count, 0) AS count, coalesce(max_duration, 0) AS max_duration, coalesce(max_tx_duration, 0) AS max_tx_duration, coalesce(max_conn_duration, 0) AS max_conn_duration FROM
        (SELECT d.datname, a.state FROM pg_database d, unnest(ARRAY ['active','idle','idle in transaction','idle in transaction (aborted)','fastpath function call','disabled']) a(state) WHERE d.datallowconn AND NOT d.datistemplate) base
          LEFT JOIN (SELECT datname, state, count(*) AS count, max(extract(epoch from now() - state_change)) AS max_duration, max(extract(epoch from now() - xact_start))
          AS max_tx_duration, max(extract(epoch from now() - backend_start)) AS max_conn_duration FROM pg_stat_activity WHERE pid <> pg_backend_pid() GROUP BY 1,2) data USING (datname,state);
  ttl: 10
  min_version: 90200
  tags: [ cluster ]
  metrics:
    - datname:           { usage: LABEL ,description: "Name of the database this backend is connected to" }
    - state:             { usage: LABEL ,description: "Current overall state of this backend." }
    - count:             { usage: GAUGE ,description: "Count of connection among (datname,state)" }
    - max_duration:      { usage: GAUGE ,description: "Max duration since last state change among (datname, state)" }
    - max_tx_duration:   { usage: GAUGE ,description: "Max transaction duration since state change among (datname, state)" }
    - max_conn_duration: { usage: GAUGE ,description: "Max backend session duration since state change among (datname, state)" }

pg_activity_91:
  name: pg_activity
  desc: PostgreSQL backend activity group by database (9.1)
  query: |
    SELECT
      datname,
      'active' AS state,
      count(*) AS count,
      max(extract(epoch from now() - query_start))   AS max_duration,
      max(extract(epoch from now() - xact_start))    AS max_tx_duration,
      max(extract(epoch from now() - backend_start)) AS max_conn_duration
    FROM pg_stat_activity
    WHERE procpid <> pg_backend_pid()
      AND datname IS NOT NULL
    GROUP BY datname;
  ttl: 10
  min_version: 90100
  max_version: 90200
  tags: [ cluster ]
  metrics:
    - datname:           { usage: LABEL ,description: "Name of the database this backend is connected to" }
    - state:             { usage: LABEL ,description: "Current overall state of this backend (always active on 9.1)" }
    - count:             { usage: GAUGE ,description: "Count of connection among (datname,state)" }
    - max_duration:      { usage: GAUGE ,description: "Max duration since query start among (datname)" }
    - max_tx_duration:   { usage: GAUGE ,description: "Max transaction duration among (datname)" }
    - max_conn_duration: { usage: GAUGE ,description: "Max backend session duration among (datname)" }


#==============================================================#
# 0420 pg_wait
#==============================================================#
pg_wait_96:
  name: pg_wait
  desc: PostgreSQL backend client count group by wait event type (9.6)
  query: |
    SELECT coalesce(datname, '_system') AS datname, coalesce(wait_event_type, 'Running') AS event, count(*) AS count FROM pg_stat_activity GROUP BY 1, 2;
  ttl: 10
  min_version: 90600
  tags: [ cluster ]
  metrics:
    - datname: { usage: LABEL ,description: "Name of the database, _system for global process" }
    - event:   { usage: LABEL ,description: "Wait event type" }
    - count:   { usage: GAUGE ,description: "Count of WaitEvent on target database" }

pg_wait_91:
  name: pg_wait
  desc: PostgreSQL backend client count group by waiting flag (9.1-9.5)
  query: |
    SELECT coalesce(datname, '_system') AS datname, CASE WHEN waiting THEN 'Waiting' ELSE 'Running' END AS event, count(*) AS count FROM pg_stat_activity GROUP BY 1, 2;
  ttl: 10
  min_version: 90100
  max_version: 90600
  tags: [ cluster ]
  metrics:
    - datname: { usage: LABEL ,description: "Name of the database, _system for global process" }
    - event:   { usage: LABEL ,description: "Waiting flag, Waiting or Running" }
    - count:   { usage: GAUGE ,description: "Backend count group by waiting flag" }


#==============================================================#
# 0440 pg_xact
#==============================================================#
pg_xact:
  name: pg_xact
  desc: PostgreSQL transaction identifier metrics
  query: |
    WITH snap(v) AS (SELECT txid_current_snapshot()),
         xset(v) AS (SELECT txid_snapshot_xip(v) FROM snap),
         xnum(v) AS (SELECT count(*) FROM xset),
         xmin(v) AS (SELECT txid_snapshot_xmin(v) FROM snap),
         xmax(v) AS (SELECT txid_snapshot_xmax(v) FROM snap)
    SELECT xmin.v AS xmin, xmax.v AS xmax, xnum.v AS xnum
    FROM xmin, xmax, xnum;
  ttl: 10
  min_version: 90100
  tags: [ cluster ]
  metrics:
    - xmin: { usage: COUNTER ,description: "Earliest txid that is still active" }
    - xmax: { usage: COUNTER ,description: "First as-yet-unassigned txid" }
    - xnum: { usage: GAUGE   ,description: "Current active transaction count" }


#==============================================================#
# 0450 pg_lock
#==============================================================#
pg_lock:
  name: pg_lock
  desc: PostgreSQL lock distribution by mode and database
  query: |
    SELECT datname, mode, coalesce(count, 0) AS count
      FROM (SELECT d.oid AS database, d.datname, l.mode FROM pg_database d, unnest(ARRAY ['AccessShareLock','RowShareLock','RowExclusiveLock','ShareUpdateExclusiveLock', 'ShareLock','ShareRowExclusiveLock','ExclusiveLock','AccessExclusiveLock']) l(mode) WHERE d.datallowconn AND NOT d.datistemplate) base
      LEFT JOIN (SELECT database, mode, count(*) AS count FROM pg_locks WHERE database IS NOT NULL GROUP BY 1, 2) cnt USING (database, mode);
  ttl: 10
  min_version: 90100
  tags: [ cluster ]
  metrics:
    - datname: { usage: LABEL ,description: "Name of the database this backend is connected to" }
    - mode:    { usage: LABEL ,description: "Name of the lock mode held or desired by this process" }
    - count:   { usage: GAUGE ,description: "Number of locks of corresponding mode and database" }


#==============================================================#
# 0460 pg_query
#==============================================================#
pg_query_94:
  name: pg_query
  desc: PostgreSQL query statement metrics, require pg_stat_statements installed, 9.4 - 12
  query: |-
    SELECT datname, queryid AS query, sum(calls) AS calls, sum(rows) AS rows, sum(total_time) AS exec_time, sum(blk_read_time) + sum(blk_write_time) AS io_time,
      sum(shared_blks_hit) AS sblk_hit, sum(shared_blks_read) AS sblk_read, sum(shared_blks_dirtied) AS sblk_dirtied, sum(shared_blks_written) AS sblk_written
    FROM pg_stat_statements(false) s JOIN pg_database d ON s.dbid = d.oid WHERE userid != 10 AND calls > 4 GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 128;

  ttl: 10
  timeout: 2
  min_version: 90400
  max_version: 130000
  tags: [ cluster, "extension:pg_stat_statements" ]
  metrics:
    - datname:      { usage: LABEL   ,description: "Name of database" }
    - query:        { usage: LABEL   ,description: "QueryID generated from internal hash code, computed from the statement's parse tree" }
    - calls:        { usage: COUNTER ,description: "Number of times the statement was executed" }
    - rows:         { usage: COUNTER ,description: "Total number of rows retrieved or affected by the statement" }
    - exec_time:    { usage: COUNTER ,scale: 1e-3 ,description: "Total time spent executing the statement, in seconds" }
    - io_time:      { usage: COUNTER ,scale: 1e-3 ,description: "Total time the statement spent reading and writing blocks, in seconds" }
    - sblk_hit:     { usage: COUNTER ,description: "Total number of shared block cache hits by the statement" }
    - sblk_read:    { usage: COUNTER ,description: "Total number of shared blocks read by the statement" }
    - sblk_dirtied: { usage: COUNTER ,description: "Total number of shared blocks dirtied by the statement" }
    - sblk_written: { usage: COUNTER ,description: "Total number of shared blocks written by the statement" }

pg_query_91:
  name: pg_query
  desc: PostgreSQL query statement metrics, require pg_stat_statements installed, 9.1 - 9.3 (no queryid)
  query: |-
    SELECT datname, md5(query) AS query, sum(calls) AS calls, sum(rows) AS rows, sum(total_time) AS exec_time, 0::FLOAT AS io_time,
      sum(shared_blks_hit) AS sblk_hit, sum(shared_blks_read) AS sblk_read, 0::BIGINT AS sblk_dirtied, sum(shared_blks_written) AS sblk_written
    FROM pg_stat_statements s JOIN pg_database d ON s.dbid = d.oid WHERE userid != 10 AND calls > 4 GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 128;

  ttl: 10
  timeout: 2
  min_version: 90100
  max_version: 90400
  tags: [ cluster, "extension:pg_stat_statements" ]
  metrics:
    - datname:      { usage: LABEL   ,description: "Name of database" }
    - query:        { usage: LABEL   ,description: "MD5 hash of query text (no queryid before 9.4)" }
    - calls:        { usage: COUNTER ,description: "Number of times the statement was executed" }
    - rows:         { usage: COUNTER ,description: "Total number of rows retrieved or affected by the statement" }
    - exec_time:    { usage: COUNTER ,scale: 1e-3 ,description: "Total time spent executing the statement, in seconds" }
    - io_time:      { usage: COUNTER ,scale: 1e-3 ,description: "Total time the statement spent reading and writing blocks, in seconds (N/A before 9.4, always 0)" }
    - sblk_hit:     { usage: COUNTER ,description: "Total number of shared block cache hits by the statement" }
    - sblk_read:    { usage: COUNTER ,description: "Total number of shared blocks read by the statement" }
    - sblk_dirtied: { usage: COUNTER ,description: "Total number of shared blocks dirtied by the statement (N/A before 9.4, always 0)" }
    - sblk_written: { usage: COUNTER ,description: "Total number of shared blocks written by the statement" }


#==============================================================#
# 0610 pg_db
#==============================================================#
pg_db_92:
  name: pg_db
  desc: PostgreSQL database stats from pg_stat_database (9.2 - 9.6)
  query: |-
    SELECT d.datname, datid,age(datfrozenxid) AS age, datistemplate AS is_template, datallowconn AS allow_conn, datconnlimit AS conn_limit, datfrozenxid::TEXT::BIGINT as frozen_xid,
      numbackends,xact_commit,xact_rollback,xact_rollback + xact_commit AS xact_total, blks_read,blks_hit,blks_read + blks_hit AS blks_access,tup_returned,tup_fetched,tup_inserted,tup_updated,tup_deleted,tup_inserted + tup_updated + tup_deleted AS tup_modified,
      conflicts,temp_files,temp_bytes,deadlocks,blk_read_time,blk_write_time, extract(EPOCH FROM stats_reset) AS reset_time
    FROM pg_database d JOIN pg_stat_database sd ON d.oid = sd.datid;
  ttl: 10
  min_version: 90200
  tags: [ cluster ]
  metrics:
    - datname:             { usage: LABEL   ,description: "Name of the database" }
    - datid:               { usage: GAUGE   ,description: "OID of the database" }
    - age:                 { usage: GAUGE   ,description: "Age of database calculated from datfrozenxid" }
    - is_template:         { usage: GAUGE   ,description: "If true(1), then this database can be cloned by any user with CREATEDB privileges" }
    - allow_conn:          { usage: GAUGE   ,description: "If false(0) then no one can connect to this database." }
    - conn_limit:          { usage: GAUGE   ,description: "Sets maximum number of concurrent connections that can be made to this database. -1 means no limit." }
    - frozen_xid:          { usage: GAUGE   ,description: "All transaction IDs before this one have been frozen" }
    - numbackends:         { usage: GAUGE   ,description: "Number of backends currently connected to this database" }
    - xact_commit:         { usage: COUNTER ,description: "Number of transactions in this database that have been committed" }
    - xact_rollback:       { usage: COUNTER ,description: "Number of transactions in this database that have been rolled back" }
    - xact_total:          { usage: COUNTER ,description: "Number of transactions in this database" }
    - blks_read:           { usage: COUNTER ,description: "Number of disk blocks read in this database" }
    - blks_hit:            { usage: COUNTER ,description: "Number of times disk blocks were found already in the buffer cache" }
    - blks_access:         { usage: COUNTER ,description: "Number of times disk blocks that accessed read+hit" }
    - tup_returned:        { usage: COUNTER ,description: "Number of rows returned by queries in this database" }
    - tup_fetched:         { usage: COUNTER ,description: "Number of rows fetched by queries in this database" }
    - tup_inserted:        { usage: COUNTER ,description: "Number of rows inserted by queries in this database" }
    - tup_updated:         { usage: COUNTER ,description: "Number of rows updated by queries in this database" }
    - tup_deleted:         { usage: COUNTER ,description: "Number of rows deleted by queries in this database" }
    - tup_modified:        { usage: COUNTER ,description: "Number of rows modified by queries in this database" }
    - conflicts:           { usage: COUNTER ,description: "Number of queries canceled due to conflicts with recovery in this database" }
    - temp_files:          { usage: COUNTER ,description: "Number of temporary files created by queries in this database" }
    - temp_bytes:          { usage: COUNTER ,description: "Total amount of data written to temporary files by queries in this database." }
    - deadlocks:           { usage: COUNTER ,description: "Number of deadlocks detected in this database" }
    - blk_read_time:       { usage: COUNTER ,scale: 1e-3 ,description: "Time spent reading data file blocks by backends in this database, in seconds" }
    - blk_write_time:      { usage: COUNTER ,scale: 1e-3 ,description: "Time spent writing data file blocks by backends in this database, in seconds" }
    - reset_time:          { usage: GAUGE   ,description: "Time at which database statistics were last reset" }

pg_db_91:
  name: pg_db
  desc: PostgreSQL database stats from pg_stat_database (9.1, fewer columns)
  query: |-
    SELECT d.datname, datid,age(datfrozenxid) AS age, datistemplate AS is_template, datallowconn AS allow_conn, datconnlimit AS conn_limit, datfrozenxid::TEXT::BIGINT as frozen_xid,
      numbackends,xact_commit,xact_rollback,xact_rollback + xact_commit AS xact_total, blks_read,blks_hit,blks_read + blks_hit AS blks_access,tup_returned,tup_fetched,tup_inserted,tup_updated,tup_deleted,tup_inserted + tup_updated + tup_deleted AS tup_modified,
      conflicts, 0::BIGINT AS temp_files, 0::BIGINT AS temp_bytes, 0::BIGINT AS deadlocks, 0::BIGINT AS blk_read_time, 0::BIGINT AS blk_write_time,
      extract(EPOCH FROM stats_reset) AS reset_time
    FROM pg_database d JOIN pg_stat_database sd ON d.oid = sd.datid;
  ttl: 10
  min_version: 90100
  max_version: 90200
  tags: [ cluster ]
  metrics:
    - datname:             { usage: LABEL   ,description: "Name of the database" }
    - datid:               { usage: GAUGE   ,description: "OID of the database" }
    - age:                 { usage: GAUGE   ,description: "Age of database calculated from datfrozenxid" }
    - is_template:         { usage: GAUGE   ,description: "If true(1), then this database can be cloned by any user with CREATEDB privileges" }
    - allow_conn:          { usage: GAUGE   ,description: "If false(0) then no one can connect to this database." }
    - conn_limit:          { usage: GAUGE   ,description: "Sets maximum number of concurrent connections that can be made to this database. -1 means no limit." }
    - frozen_xid:          { usage: GAUGE   ,description: "All transaction IDs before this one have been frozen" }
    - numbackends:         { usage: GAUGE   ,description: "Number of backends currently connected to this database" }
    - xact_commit:         { usage: COUNTER ,description: "Number of transactions in this database that have been committed" }
    - xact_rollback:       { usage: COUNTER ,description: "Number of transactions in this database that have been rolled back" }
    - xact_total:          { usage: COUNTER ,description: "Number of transactions in this database" }
    - blks_read:           { usage: COUNTER ,description: "Number of disk blocks read in this database" }
    - blks_hit:            { usage: COUNTER ,description: "Number of times disk blocks were found already in the buffer cache" }
    - blks_access:         { usage: COUNTER ,description: "Number of times disk blocks that accessed read+hit" }
    - tup_returned:        { usage: COUNTER ,description: "Number of rows returned by queries in this database" }
    - tup_fetched:         { usage: COUNTER ,description: "Number of rows fetched by queries in this database" }
    - tup_inserted:        { usage: COUNTER ,description: "Number of rows inserted by queries in this database" }
    - tup_updated:         { usage: COUNTER ,description: "Number of rows updated by queries in this database" }
    - tup_deleted:         { usage: COUNTER ,description: "Number of rows deleted by queries in this database" }
    - tup_modified:        { usage: COUNTER ,description: "Number of rows modified by queries in this database" }
    - conflicts:           { usage: COUNTER ,description: "Number of queries canceled due to conflicts with recovery in this database" }
    - temp_files:          { usage: COUNTER ,description: "Number of temporary files created by queries in this database (N/A on 9.1, always 0)" }
    - temp_bytes:          { usage: COUNTER ,description: "Total amount of data written to temporary files by queries in this database (N/A on 9.1, always 0)" }
    - deadlocks:           { usage: COUNTER ,description: "Number of deadlocks detected in this database (N/A on 9.1, always 0)" }
    - blk_read_time:       { usage: COUNTER ,scale: 1e-3 ,description: "Time spent reading data file blocks by backends in this database, in seconds (N/A on 9.1, always 0)" }
    - blk_write_time:      { usage: COUNTER ,scale: 1e-3 ,description: "Time spent writing data file blocks by backends in this database, in seconds (N/A on 9.1, always 0)" }
    - reset_time:          { usage: GAUGE   ,description: "Time at which database statistics were last reset" }


#==============================================================#
# 0620 pg_db_confl
#==============================================================#
# https://pgpedia.info/p/pg_stat_database_conflicts.html
pg_db_confl:
  name: pg_db_confl
  desc: PostgreSQL database conflicts metrics for pg 9.1 - 9.6
  query: SELECT datid,datname,confl_tablespace,confl_lock,confl_snapshot,confl_bufferpin,confl_deadlock FROM pg_stat_database_conflicts;
  ttl: 10
  min_version: 90100
  tags: [ cluster, replica ]
  metrics:
    - datid:                { usage: DISCARD }
    - datname:              { usage: LABEL   ,description: "Name of this database" }
    - confl_tablespace:     { usage: COUNTER ,description: "Number of queries in this database that have been canceled due to dropped tablespaces" }
    - confl_lock:           { usage: COUNTER ,description: "Number of queries in this database that have been canceled due to lock timeouts" }
    - confl_snapshot:       { usage: COUNTER ,description: "Number of queries in this database that have been canceled due to old snapshots" }
    - confl_bufferpin:      { usage: COUNTER ,description: "Number of queries in this database that have been canceled due to pinned buffers" }
    - confl_deadlock:       { usage: COUNTER ,description: "Number of queries in this database that have been canceled due to deadlocks" }


#==============================================================#
# 0700 pg_table
#==============================================================#
pg_table_94:
  name: pg_table
  desc: PostgreSQL table metrics 9.4-9.6
  query: |-
    SELECT CURRENT_CATALOG AS datname, nsp.nspname || '.' || c.relname AS relname, c.oid AS relid, ascii(c.relkind) AS kind,
       c.relpages AS pages, c.reltuples AS tuples, c.relfrozenxid AS frozenxid, age(c.relfrozenxid) AS age, c.relnatts AS ncols,
       psut.seq_scan,psut.seq_tup_read,psut.idx_scan,psut.idx_tup_fetch,psut.seq_scan + psut.idx_scan AS tbl_scan, psut.seq_tup_read + psut.idx_tup_fetch AS tup_read,
       psut.n_tup_ins,psut.n_tup_upd,psut.n_tup_del,(psut.n_tup_ins + psut.n_tup_upd + psut.n_tup_del) AS n_tup_mod,psut.n_tup_hot_upd,psut.n_live_tup,psut.n_dead_tup,
       psut.n_mod_since_analyze,psut.last_vacuum,psut.last_autovacuum,psut.last_analyze,psut.last_autoanalyze,
       psut.vacuum_count,psut.autovacuum_count,psut.analyze_count,psut.autoanalyze_count,
       psio.heap_blks_read,psio.heap_blks_hit,psio.idx_blks_read,psio.idx_blks_hit,psio.toast_blks_read,psio.toast_blks_hit,psio.tidx_blks_read,psio.tidx_blks_hit
    FROM pg_class c JOIN pg_namespace nsp ON c.relnamespace = nsp.oid
        LEFT JOIN pg_stat_user_tables psut ON psut.relid = c.oid LEFT JOIN pg_statio_user_tables psio ON psio.relid = c.oid
    WHERE nsp.nspname !~ '^pg_' AND nsp.nspname !~ '^_' AND nsp.nspname !~ '^timescaledb' AND nsp.nspname !~ '^citus' AND nsp.nspname !~ '^columnar'
        AND nsp.nspname NOT IN ('pg_catalog','information_schema','pg_toast','repack','monitor') AND c.relkind = ANY (ARRAY ['r','m','t','p'])
    ORDER BY c.relpages DESC LIMIT 256;

  ttl: 10
  timeout: 2
  min_version: 90400
  metrics:
    - datname:             { usage: LABEL                 ,description: "Database name of this table" }
    - relname:             { usage: LABEL                 ,description: "Relation name of this table" }
    - relid:               { usage: GAUGE                 ,description: "Relation oid of this table" }
    - kind:                { usage: GAUGE                 ,description: "Relation kind r/table/114,m/mview/109,t/toast/116" }
    - pages:               { usage: GAUGE                 ,description: "Size of the on-disk representation of this table in pages" }
    - tuples:              { usage: GAUGE                 ,description: "Estimated number of rows in this table" }
    - frozenxid:           { usage: GAUGE                 ,description: "All txid before this have been frozen on this table" }
    - age:                 { usage: GAUGE                 ,description: "Age of this table in vacuum cycles" }
    - ncols:               { usage: GAUGE                 ,description: "Number of columns in the table" }
    - seq_scan:            { usage: COUNTER  ,default: 0  ,description: "Number of sequential scans initiated on this table" }
    - seq_tup_read:        { usage: COUNTER  ,default: 0  ,description: "Number of live rows fetched by sequential scans" }
    - idx_scan:            { usage: COUNTER  ,default: 0  ,description: "Number of index scans initiated on this table" }
    - idx_tup_fetch:       { usage: COUNTER  ,default: 0  ,description: "Number of live rows fetched by index scans" }
    - tbl_scan:            { usage: COUNTER  ,default: 0  ,description: "Number of scans initiated on this table" }
    - tup_read:            { usage: COUNTER  ,default: 0  ,description: "Number of live rows fetched by scans" }
    - n_tup_ins:           { usage: COUNTER  ,default: 0  ,description: "Number of rows inserted" }
    - n_tup_upd:           { usage: COUNTER  ,default: 0  ,description: "Number of rows updated (includes HOT updated rows)" }
    - n_tup_del:           { usage: COUNTER  ,default: 0  ,description: "Number of rows deleted" }
    - n_tup_mod:           { usage: COUNTER  ,default: 0  ,description: "Number of rows modified (insert + update + delete)" }
    - n_tup_hot_upd:       { usage: COUNTER  ,default: 0  ,description: "Number of rows HOT updated (i.e with no separate index update required)" }
    - n_live_tup:          { usage: GAUGE                 ,description: "Estimated number of live rows" }
    - n_dead_tup:          { usage: GAUGE                 ,description: "Estimated number of dead rows" }
    - n_mod_since_analyze: { usage: GAUGE                 ,description: "Estimated number of rows modified since this table was last analyzed" }
    - last_vacuum:         { usage: DISCARD               ,description: "Last time at which this table was manually vacuumed (not counting VACUUM FULL)" }
    - last_autovacuum:     { usage: DISCARD               ,description: "Last time at which this table was vacuumed by the autovacuum daemon" }
    - last_analyze:        { usage: DISCARD               ,description: "Last time at which this table was manually analyzed" }
    - last_autoanalyze:    { usage: DISCARD               ,description: "Last time at which this table was analyzed by the autovacuum daemon" }
    - vacuum_count:        { usage: COUNTER  ,default: 0  ,description: "Number of times this table has been manually vacuumed (not counting VACUUM FULL)" }
    - autovacuum_count:    { usage: COUNTER  ,default: 0  ,description: "Number of times this table has been vacuumed by the autovacuum daemon" }
    - analyze_count:       { usage: COUNTER  ,default: 0  ,description: "Number of times this table has been manually analyzed" }
    - autoanalyze_count:   { usage: COUNTER  ,default: 0  ,description: "Number of times this table has been analyzed by the autovacuum daemon" }
    - heap_blks_read:      { usage: COUNTER  ,default: 0  ,description: "Number of disk blocks read from this table" }
    - heap_blks_hit:       { usage: COUNTER  ,default: 0  ,description: "Number of buffer hits in this table" }
    - idx_blks_read:       { usage: COUNTER  ,default: 0  ,description: "Number of disk blocks read from all indexes on this table" }
    - idx_blks_hit:        { usage: COUNTER  ,default: 0  ,description: "Number of buffer hits in all indexes on this table" }
    - toast_blks_read:     { usage: DISCARD  ,default: 0  ,description: "Number of disk blocks read from this table's TOAST table (if any)" }
    - toast_blks_hit:      { usage: DISCARD  ,default: 0  ,description: "Number of buffer hits in this table's TOAST table (if any)" }
    - tidx_blks_read:      { usage: DISCARD  ,default: 0  ,description: "Number of disk blocks read from this table's TOAST table indexes (if any)" }
    - tidx_blks_hit:       { usage: DISCARD  ,default: 0  ,description: "Number of buffer hits in this table's TOAST table indexes (if any)" }

pg_table_91:
  name: pg_table
  desc: PostgreSQL table metrics 9.1-9.3 (no n_mod_since_analyze)
  query: |-
    SELECT CURRENT_CATALOG AS datname, nsp.nspname || '.' || c.relname AS relname, c.oid AS relid, ascii(c.relkind) AS kind,
       c.relpages AS pages, c.reltuples AS tuples, c.relfrozenxid AS frozenxid, age(c.relfrozenxid) AS age, c.relnatts AS ncols,
       psut.seq_scan,psut.seq_tup_read,psut.idx_scan,psut.idx_tup_fetch,psut.seq_scan + psut.idx_scan AS tbl_scan, psut.seq_tup_read + psut.idx_tup_fetch AS tup_read,
       psut.n_tup_ins,psut.n_tup_upd,psut.n_tup_del,(psut.n_tup_ins + psut.n_tup_upd + psut.n_tup_del) AS n_tup_mod,psut.n_tup_hot_upd,psut.n_live_tup,psut.n_dead_tup,
       NULL::BIGINT AS n_mod_since_analyze, psut.last_vacuum,psut.last_autovacuum,psut.last_analyze,psut.last_autoanalyze,
       psut.vacuum_count,psut.autovacuum_count,psut.analyze_count,psut.autoanalyze_count,
       psio.heap_blks_read,psio.heap_blks_hit,psio.idx_blks_read,psio.idx_blks_hit,psio.toast_blks_read,psio.toast_blks_hit,psio.tidx_blks_read,psio.tidx_blks_hit
    FROM pg_class c JOIN pg_namespace nsp ON c.relnamespace = nsp.oid
        LEFT JOIN pg_stat_user_tables psut ON psut.relid = c.oid LEFT JOIN pg_statio_user_tables psio ON psio.relid = c.oid
    WHERE nsp.nspname !~ '^pg_' AND nsp.nspname !~ '^_' AND nsp.nspname !~ '^timescaledb' AND nsp.nspname !~ '^citus' AND nsp.nspname !~ '^columnar'
        AND nsp.nspname NOT IN ('pg_catalog','information_schema','pg_toast','repack','monitor') AND c.relkind = ANY (ARRAY ['r','m','t','p'])
    ORDER BY c.relpages DESC LIMIT 256;

  ttl: 10
  timeout: 2
  min_version: 90100
  max_version: 90400
  metrics:
    - datname:             { usage: LABEL                 ,description: "Database name of this table" }
    - relname:             { usage: LABEL                 ,description: "Relation name of this table" }
    - relid:               { usage: GAUGE                 ,description: "Relation oid of this table" }
    - kind:                { usage: GAUGE                 ,description: "Relation kind r/table/114,t/toast/116" }
    - pages:               { usage: GAUGE                 ,description: "Size of the on-disk representation of this table in pages" }
    - tuples:              { usage: GAUGE                 ,description: "Estimated number of rows in this table" }
    - frozenxid:           { usage: GAUGE                 ,description: "All txid before this have been frozen on this table" }
    - age:                 { usage: GAUGE                 ,description: "Age of this table in vacuum cycles" }
    - ncols:               { usage: GAUGE                 ,description: "Number of columns in the table" }
    - seq_scan:            { usage: COUNTER  ,default: 0  ,description: "Number of sequential scans initiated on this table" }
    - seq_tup_read:        { usage: COUNTER  ,default: 0  ,description: "Number of live rows fetched by sequential scans" }
    - idx_scan:            { usage: COUNTER  ,default: 0  ,description: "Number of index scans initiated on this table" }
    - idx_tup_fetch:       { usage: COUNTER  ,default: 0  ,description: "Number of live rows fetched by index scans" }
    - tbl_scan:            { usage: COUNTER  ,default: 0  ,description: "Number of scans initiated on this table" }
    - tup_read:            { usage: COUNTER  ,default: 0  ,description: "Number of live rows fetched by scans" }
    - n_tup_ins:           { usage: COUNTER  ,default: 0  ,description: "Number of rows inserted" }
    - n_tup_upd:           { usage: COUNTER  ,default: 0  ,description: "Number of rows updated (includes HOT updated rows)" }
    - n_tup_del:           { usage: COUNTER  ,default: 0  ,description: "Number of rows deleted" }
    - n_tup_mod:           { usage: COUNTER  ,default: 0  ,description: "Number of rows modified (insert + update + delete)" }
    - n_tup_hot_upd:       { usage: COUNTER  ,default: 0  ,description: "Number of rows HOT updated (i.e with no separate index update required)" }
    - n_live_tup:          { usage: GAUGE                 ,description: "Estimated number of live rows" }
    - n_dead_tup:          { usage: GAUGE                 ,description: "Estimated number of dead rows" }
    - n_mod_since_analyze: { usage: GAUGE                 ,description: "Estimated number of rows modified since this table was last analyzed (N/A on 9.1-9.3, NULL)" }
    - last_vacuum:         { usage: DISCARD               ,description: "Last time at which this table was manually vacuumed (not counting VACUUM FULL)" }
    - last_autovacuum:     { usage: DISCARD               ,description: "Last time at which this table was vacuumed by the autovacuum daemon" }
    - last_analyze:        { usage: DISCARD               ,description: "Last time at which this table was manually analyzed" }
    - last_autoanalyze:    { usage: DISCARD               ,description: "Last time at which this table was analyzed by the autovacuum daemon" }
    - vacuum_count:        { usage: COUNTER  ,default: 0  ,description: "Number of times this table has been manually vacuumed (not counting VACUUM FULL)" }
    - autovacuum_count:    { usage: COUNTER  ,default: 0  ,description: "Number of times this table has been vacuumed by the autovacuum daemon" }
    - analyze_count:       { usage: COUNTER  ,default: 0  ,description: "Number of times this table has been manually analyzed" }
    - autoanalyze_count:   { usage: COUNTER  ,default: 0  ,description: "Number of times this table has been analyzed by the autovacuum daemon" }
    - heap_blks_read:      { usage: COUNTER  ,default: 0  ,description: "Number of disk blocks read from this table" }
    - heap_blks_hit:       { usage: COUNTER  ,default: 0  ,description: "Number of buffer hits in this table" }
    - idx_blks_read:       { usage: COUNTER  ,default: 0  ,description: "Number of disk blocks read from all indexes on this table" }
    - idx_blks_hit:        { usage: COUNTER  ,default: 0  ,description: "Number of buffer hits in all indexes on this table" }
    - toast_blks_read:     { usage: DISCARD  ,default: 0  ,description: "Number of disk blocks read from this table's TOAST table (if any)" }
    - toast_blks_hit:      { usage: DISCARD  ,default: 0  ,description: "Number of buffer hits in this table's TOAST table (if any)" }
    - tidx_blks_read:      { usage: DISCARD  ,default: 0  ,description: "Number of disk blocks read from this table's TOAST table indexes (if any)" }
    - tidx_blks_hit:       { usage: DISCARD  ,default: 0  ,description: "Number of buffer hits in this table's TOAST table indexes (if any)" }


#==============================================================#
# 0710 pg_index
#==============================================================#
pg_index:
  name: pg_index
  desc: PostgreSQL index metrics (legacy 9.1-9.6)
  query: |-
    SELECT CURRENT_CATALOG AS datname,
      psui.schemaname || '.' || psui.indexrelname AS idxname,
      psui.schemaname || '.' || psui.relname      AS relname,
      psui.indexrelid AS relid,
      c.relpages, c.reltuples,
      psui.idx_scan, psui.idx_tup_read, psui.idx_tup_fetch,
      psio.idx_blks_read, psio.idx_blks_hit
    FROM pg_stat_user_indexes psui
      JOIN pg_statio_user_indexes psio ON psio.indexrelid = psui.indexrelid
      JOIN pg_class c ON c.oid = psui.indexrelid
    WHERE psui.schemaname !~ '^pg_' AND psui.schemaname !~ '^_' AND psui.schemaname !~ '^timescaledb' AND psui.schemaname !~ '^citus' AND psui.schemaname !~ '^columnar'
      AND psui.schemaname NOT IN ('pg_catalog','information_schema','pg_toast','repack','monitor')
    ORDER BY psui.idx_tup_read DESC LIMIT 512;

  ttl: 10
  timeout: 1
  min_version: 90100
  metrics:
    - datname:             { usage: LABEL    ,description: "Database name of this index" }
    - idxname:             { usage: LABEL    ,description: "Name of this index (full-qualified schema name)" }
    - relname:             { usage: LABEL    ,description: "Name of the table for this index (full-qualified schema name)" }
    - relid:               { usage: LABEL    ,description: "Relation oid of this index" }
    - relpages:            { usage: GAUGE    ,description: "Size of the on-disk representation of this index in pages" }
    - reltuples:           { usage: GAUGE    ,description: "Estimate relation tuples" }
    - idx_scan:            { usage: COUNTER  ,description: "Number of index scans initiated on this index" }
    - idx_tup_read:        { usage: COUNTER  ,description: "Number of index entries returned by scans on this index" }
    - idx_tup_fetch:       { usage: COUNTER  ,description: "Number of live table rows fetched by simple index scans using this index" }
    - idx_blks_read:       { usage: COUNTER  ,description: "Number of disk blocks read from this index" }
    - idx_blks_hit:        { usage: COUNTER  ,description: "Number of buffer hits in this index" }


#==============================================================#
# 0720 pg_func
#==============================================================#
pg_func:
  desc: PostgreSQL function metrics
  query: SELECT CURRENT_CATALOG AS datname, schemaname || '.' || funcname AS funcname, sum(calls) AS calls, sum(total_time) AS total_time, sum(self_time) AS self_time FROM pg_stat_user_functions GROUP BY 2 ORDER BY 4 DESC LIMIT 128;
  ttl: 10
  min_version: 90100
  metrics:
    - datname:             { usage: LABEL    ,description: "Name of belonged database" }
    - funcname:            { usage: LABEL    ,description: "Name of this function, may have multiple override" }
    - calls:               { usage: COUNTER  ,description: "Number of times this function has been called" }
    - total_time:          { usage: COUNTER  ,scale: 1e-3 ,description: "Total time spent in this function and all other functions called by it, in seconds" }
    - self_time:           { usage: COUNTER  ,scale: 1e-3 ,description: "Total time spent in this function itself, not including other functions called by it, in seconds" }


#==============================================================#
# 0740 pg_relkind
#==============================================================#
pg_relkind:
  name: pg_relkind
  desc: Postgres relation count by kind
  query: |
    SELECT CURRENT_CATALOG AS datname, relkind, count(*) AS count
    FROM pg_class
    GROUP BY relkind;
  ttl: 60
  timeout: 1
  min_version: 90100
  metrics:
    - datname: { usage: LABEL ,description: "Database name" }
    - relkind: { usage: LABEL ,description: "Relation kind (r,i,S,t,v,c,...)" }
    - count:   { usage: GAUGE ,description: "Number of relations" }


#==============================================================#
# 0810 pg_table_size
#==============================================================#
pg_table_size:
  desc: PostgreSQL table size metrics, quite slow
  query: |-
    SELECT CURRENT_CATALOG AS datname, nsp.nspname || '.' || rel.relname AS relname,
       pg_total_relation_size(rel.oid)       AS bytes,
       pg_relation_size(rel.oid)             AS relsize,
       pg_indexes_size(rel.oid)              AS indexsize,
       pg_total_relation_size(reltoastrelid) AS toastsize
    FROM pg_namespace nsp JOIN pg_class rel ON nsp.oid = rel.relnamespace
    WHERE nspname <> ALL(ARRAY['pg_catalog', 'information_schema']) AND rel.relkind = 'r'
    ORDER BY 3 DESC NULLS LAST LIMIT 256;

  ttl: 300
  timeout: 2
  min_version: 90100
  metrics:
    - datname:             { usage: LABEL    ,description: "Database name of this table" }
    - relname:             { usage: LABEL    ,description: "Schema qualified table name" }
    - bytes:               { usage: GAUGE    ,default: 0  ,description: "Total bytes of this table (including toast, index, toast index)" }
    - relsize:             { usage: GAUGE    ,default: 0  ,description: "Bytes of this table itself (main, vm, fsm)" }
    - indexsize:           { usage: GAUGE    ,default: 0  ,description: "Bytes of all related indexes of this table" }
    - toastsize:           { usage: GAUGE    ,default: 0  ,description: "Bytes of toast tables of this table" }


#==============================================================#
# 0820 pg_table_bloat
#==============================================================#
# pg_table_bloat require auxiliary view to work. Disable it or create auxiliary view before use:
pg_table_bloat:
  name: pg_table_bloat
  desc: PostgreSQL table bloat metrics, require auxiliary view pg_table_bloat to work
  query: SELECT datname, nspname || '.' || relname AS relname, size, ratio FROM pg_table_bloat ORDER BY size DESC LIMIT 64;
  ttl: 300
  timeout: 2
  min_version: 90400
  skip: true
  metrics:
    - datname:             { usage: LABEL    ,description: "Database name of this table" }
    - relname:             { usage: LABEL    ,description: "Schema qualified name of this table" }
    - size:                { usage: GAUGE    ,description: "Total bytes of this table" }
    - ratio:               { usage: GAUGE    ,description: "Estimated bloat ratio of this table from 0 to 1" }


#==============================================================#
# 0830 pg_index_bloat
#==============================================================#
# pg_index_bloat require auxiliary view to work. Disable it or create auxiliary view before use:
pg_index_bloat:
  name: pg_index_bloat
  desc: PostgreSQL index bloat metrics, require auxiliary view pg_index_bloat to work
  query: SELECT datname, nspname || '.' || idxname AS idxname, size, ratio FROM pg_index_bloat ORDER BY size DESC LIMIT 64;
  ttl: 300
  timeout: 2
  min_version: 90400
  skip: true
  metrics:
    - datname:             { usage: LABEL    ,description: "Database name of this index" }
    - idxname:             { usage: LABEL    ,description: "Schema qualified name of this index" }
    - size:                { usage: GAUGE    ,description: "Total bytes of this index" }
    - ratio:               { usage: GAUGE    ,description: "Estimated bloat ratio of this index from 0 to 1" }


#==============================================================#
# 0910 pgbouncer_list
#==============================================================#
# http://www.pgbouncer.org/usage.html#show-lists
pgbouncer_list:
  name: pgbouncer_list
  desc: Pgbouncer entry list
  query: SHOW LISTS;
  ttl: 10
  min_version: 10800
  fatal: true
  tags: [ pgbouncer ]
  metrics:
    - list:                { usage: LABEL                 ,description: "Pgbouncer internal list name" }
    - items:               { usage: GAUGE                 ,description: "Number of corresponding pgbouncer object" }


#==============================================================#
# 0920 pgbouncer_database
#==============================================================#
# http://www.pgbouncer.org/usage.html#show-databases
pgbouncer_database_124:
  name: pgbouncer_database
  desc: Pgbouncer database stats (since 1.24)
  query: SHOW DATABASES;
  ttl: 10
  min_version: 12400
  tags: [ pgbouncer ]
  metrics:
    - name:                       { usage: LABEL  ,rename: datname       ,description: "Name of configured database entry" }
    - host:                       { usage: LABEL                         ,description: "Host that pgbouncer will connects to" }
    - port:                       { usage: LABEL                         ,description: "Port that pgbouncer will connects to" }
    - database:                   { usage: LABEL  ,rename: real_datname  ,description: "The real database name pgbouncer connects to" }
    - force_user:                 { usage: DISCARD }
    - pool_size:                  { usage: GAUGE                         ,description: "Maximum number of server connections" }
    - min_pool_size:              { usage: GAUGE                         ,description: "Minimum number of server connections" }
    - reserve_pool_size:          { usage: GAUGE  ,rename: reserve_pool  ,description: "Maximum number of additional connections for this database" }
    - server_lifetime:            { usage: GAUGE                         ,description: "The maximum lifetime of a server connection for this database" }
    - pool_mode:                  { usage: DISCARD }
    - load_balance_hosts:         { usage: DISCARD }
    - max_connections:            { usage: GAUGE                         ,description: "Maximum number of allowed connections for this database" }
    - current_connections:        { usage: GAUGE                         ,description: "Current number of connections for this database" }
    - max_client_connections:     { usage: GAUGE                         ,description: "Maximum number of allowed client connections for this pgbouncer instance" }
    - current_client_connections: { usage: GAUGE                         ,description: "Current number of client connections for this database" }
    - paused:                     { usage: GAUGE                         ,description: "True(1) if this database is currently paused, else 0" }
    - disabled:                   { usage: GAUGE                         ,description: "True(1) if this database is currently disabled, else 0" }

pgbouncer_database_123:
  name: pgbouncer_database
  desc: Pgbouncer database stats 1.23
  query: SHOW DATABASES;
  ttl: 10
  min_version: 12300
  max_version: 12400
  tags: [ pgbouncer ]
  metrics:
    - name:                { usage: LABEL ,rename: datname      ,description: "Name of configured database entry" }
    - host:                { usage: LABEL                       ,description: "Host that pgbouncer will connects to" }
    - port:                { usage: LABEL                       ,description: "Port that pgbouncer will connects to" }
    - database:            { usage: LABEL ,rename: real_datname ,description: "The real database name pgbouncer connects to" }
    - force_user:          { usage: DISCARD }
    - pool_size:           { usage: GAUGE                       ,description: "Maximum number of server connections" }
    - min_pool_size:       { usage: GAUGE                       ,description: "Minimum number of server connections" }
    - reserve_pool:        { usage: GAUGE                       ,description: "Maximum number of additional connections for this database" }
    - server_lifetime:     { usage: GAUGE                       ,description: "The maximum lifetime of a server connection for this database" }
    - pool_mode:           { usage: DISCARD }
    - max_connections:     { usage: GAUGE                       ,description: "Maximum number of allowed connections for this database" }
    - current_connections: { usage: GAUGE                       ,description: "Current number of connections for this database" }
    - paused:              { usage: GAUGE                       ,description: "True(1) if this database is currently paused, else 0" }
    - disabled:            { usage: GAUGE                       ,description: "True(1) if this database is currently disabled, else 0" }

pgbouncer_database_116:
  name: pgbouncer_database
  desc: Pgbouncer database stats (1.16-1.22)
  query: SHOW DATABASES;
  ttl: 10
  min_version: 11600
  max_version: 12300
  tags: [ pgbouncer ]
  metrics:
    - name:                { usage: LABEL ,rename: datname      ,description: "Name of configured database entry" }
    - host:                { usage: LABEL                       ,description: "Host that pgbouncer will connects to" }
    - port:                { usage: LABEL                       ,description: "Port that pgbouncer will connects to" }
    - database:            { usage: LABEL ,rename: real_datname ,description: "The real database name pgbouncer connects to" }
    - force_user:          { usage: DISCARD }
    - pool_size:           { usage: GAUGE                       ,description: "Maximum number of server connections" }
    - min_pool_size:       { usage: GAUGE                       ,description: "Minimum number of server connections" }
    - reserve_pool:        { usage: GAUGE                       ,description: "Maximum number of additional connections for this database" }
    - pool_mode:           { usage: DISCARD }
    - max_connections:     { usage: GAUGE                       ,description: "Maximum number of allowed connections for this database" }
    - current_connections: { usage: GAUGE                       ,description: "Current number of connections for this database" }
    - paused:              { usage: GAUGE                       ,description: "True(1) if this database is currently paused, else 0" }
    - disabled:            { usage: GAUGE                       ,description: "True(1) if this database is currently disabled, else 0" }

pgbouncer_database_108:
  name: pgbouncer_database
  desc: Pgbouncer database stats (1.08-1.15)
  query: SHOW DATABASES;
  ttl: 10
  min_version: 10800
  max_version: 11600
  tags: [ pgbouncer ]
  metrics:
    - name:                { usage: LABEL ,rename: datname      ,description: "Name of configured database entry" }
    - host:                { usage: LABEL                       ,description: "Host that pgbouncer will connects to" }
    - port:                { usage: LABEL                       ,description: "Port that pgbouncer will connects to" }
    - database:            { usage: LABEL ,rename: real_datname ,description: "The real database name pgbouncer connects to" }
    - force_user:          { usage: DISCARD }
    - pool_size:           { usage: GAUGE                       ,description: "Maximum number of server connections" }
    - reserve_pool:        { usage: GAUGE                       ,description: "Maximum number of additional connections for this database" }
    - pool_mode:           { usage: DISCARD }
    - max_connections:     { usage: GAUGE                       ,description: "Maximum number of allowed connections for this database" }
    - current_connections: { usage: GAUGE                       ,description: "Current number of connections for this database" }
    - paused:              { usage: GAUGE                       ,description: "True(1) if this database is currently paused, else 0" }
    - disabled:            { usage: GAUGE                       ,description: "True(1) if this database is currently disabled, else 0" }


#==============================================================#
# 0930 pgbouncer_stat
#==============================================================#
# http://www.pgbouncer.org/usage.html#show-stats
pgbouncer_stat_124:
  name: pgbouncer_stat
  desc: Pgbouncer stats per database (since 1.24)
  query: SHOW STATS;
  ttl: 10
  min_version: 12400
  tags: [ pgbouncer ]
  metrics:
    - database:                      { usage: LABEL   ,rename: datname ,description: "Name of database" }
    - total_xact_count:              { usage: COUNTER                  ,description: "Total number of SQL transactions pooled by pgbouncer" }
    - total_query_count:             { usage: COUNTER                  ,description: "Total number of SQL queries pooled by pgbouncer" }
    - total_server_assignment_count: { usage: COUNTER                  ,description: "Total times a server was assigned to a client" }
    - total_received:                { usage: COUNTER                  ,description: "Total volume in bytes of network traffic received by pgbouncer" }
    - total_sent:                    { usage: COUNTER                  ,description: "Total volume in bytes of network traffic sent by pgbouncer" }
    - total_xact_time:               { usage: COUNTER ,scale: 1e-6     ,description: "Total number of seconds spent when in a transaction" }
    - total_query_time:              { usage: COUNTER ,scale: 1e-6     ,description: "Total number of seconds spent when executing queries" }
    - total_wait_time:               { usage: COUNTER ,scale: 1e-6     ,description: "Time spent by clients waiting for a server, in seconds" }
    - total_client_parse_count:      { usage: COUNTER                  ,description: "Total number of prepared statements created by clients" }
    - total_server_parse_count:      { usage: COUNTER                  ,description: "Total number of prepared statements created on a server." }
    - total_bind_count:              { usage: COUNTER                  ,description: "Total number of prepared statements readied for execution by clients and forwarded to postgres" }
    - avg_xact_count:                { usage: GAUGE                    ,description: "Average transactions per second in last stat period" }
    - avg_query_count:               { usage: GAUGE                    ,description: "Average queries per second in last stat period" }
    - avg_server_assignment_count:   { usage: GAUGE                    ,description: "Average number of times a server as assigned to a client per second in the last stat period." }
    - avg_recv:                      { usage: GAUGE                    ,description: "Average received (from clients) bytes per second" }
    - avg_sent:                      { usage: GAUGE                    ,description: "Average sent (to clients) bytes per second" }
    - avg_xact_time:                 { usage: GAUGE   ,scale: 1e-6     ,description: "Average transaction duration, in seconds" }
    - avg_query_time:                { usage: GAUGE   ,scale: 1e-6     ,description: "Average query duration, in seconds" }
    - avg_wait_time:                 { usage: GAUGE   ,scale: 1e-6     ,description: "Time spent by clients waiting for a server, in seconds (average per second)." }
    - avg_client_parse_count:        { usage: GAUGE                    ,description: "Average number of prepared statements created by clients" }
    - avg_server_parse_count:        { usage: GAUGE                    ,description: "Average number of prepared statements created on a server." }
    - avg_bind_count:                { usage: GAUGE                    ,description: "Average number of prepared statements readied for execution by clients and forwarded to postgres" }

pgbouncer_stat_123:
  name: pgbouncer_stat
  desc: Pgbouncer stats per database (1.23)
  query: SHOW STATS;
  ttl: 10
  min_version: 12300
  max_version: 12400
  tags: [ pgbouncer ]
  metrics:
    - database:                      { usage: LABEL   ,rename: datname ,description: "Name of database" }
    - total_xact_count:              { usage: COUNTER                  ,description: "Total number of SQL transactions pooled by pgbouncer" }
    - total_query_count:             { usage: COUNTER                  ,description: "Total number of SQL queries pooled by pgbouncer" }
    - total_server_assignment_count: { usage: COUNTER                  ,description: "Total times a server was assigned to a client" }
    - total_received:                { usage: COUNTER                  ,description: "Total volume in bytes of network traffic received by pgbouncer" }
    - total_sent:                    { usage: COUNTER                  ,description: "Total volume in bytes of network traffic sent by pgbouncer" }
    - total_xact_time:               { usage: COUNTER ,scale: 1e-6     ,description: "Total number of seconds spent when in a transaction" }
    - total_query_time:              { usage: COUNTER ,scale: 1e-6     ,description: "Total number of seconds spent when executing queries" }
    - total_wait_time:               { usage: COUNTER ,scale: 1e-6     ,description: "Time spent by clients waiting for a server, in seconds" }
    - avg_xact_count:                { usage: GAUGE                    ,description: "Average transactions per second in last stat period" }
    - avg_query_count:               { usage: GAUGE                    ,description: "Average queries per second in last stat period" }
    - avg_server_assignment_count:   { usage: GAUGE                    ,description: "Average number of times a server as assigned to a client per second in the last stat period." }
    - avg_recv:                      { usage: GAUGE                    ,description: "Average received (from clients) bytes per second" }
    - avg_sent:                      { usage: GAUGE                    ,description: "Average sent (to clients) bytes per second" }
    - avg_xact_time:                 { usage: GAUGE   ,scale: 1e-6     ,description: "Average transaction duration, in seconds" }
    - avg_query_time:                { usage: GAUGE   ,scale: 1e-6     ,description: "Average query duration, in seconds" }
    - avg_wait_time:                 { usage: GAUGE   ,scale: 1e-6     ,description: "Time spent by clients waiting for a server, in seconds (average per second)." }

pgbouncer_stat_108:
  name: pgbouncer_stat
  desc: Pgbouncer stats per database (1.08 - 1.22)
  query: SHOW STATS;
  ttl: 10
  min_version: 10800
  max_version: 12300
  tags: [ pgbouncer ]
  metrics:
    - database:                      { usage: LABEL   ,rename: datname ,description: "Name of database" }
    - total_xact_count:              { usage: COUNTER                  ,description: "Total number of SQL transactions pooled by pgbouncer" }
    - total_query_count:             { usage: COUNTER                  ,description: "Total number of SQL queries pooled by pgbouncer" }
    - total_received:                { usage: COUNTER                  ,description: "Total volume in bytes of network traffic received by pgbouncer" }
    - total_sent:                    { usage: COUNTER                  ,description: "Total volume in bytes of network traffic sent by pgbouncer" }
    - total_xact_time:               { usage: COUNTER ,scale: 1e-6     ,description: "Total number of seconds spent when in a transaction" }
    - total_query_time:              { usage: COUNTER ,scale: 1e-6     ,description: "Total number of seconds spent when executing queries" }
    - total_wait_time:               { usage: COUNTER ,scale: 1e-6     ,description: "Time spent by clients waiting for a server, in seconds" }
    - avg_xact_count:                { usage: GAUGE                    ,description: "Average transactions per second in last stat period" }
    - avg_query_count:               { usage: GAUGE                    ,description: "Average queries per second in last stat period" }
    - avg_recv:                      { usage: GAUGE                    ,description: "Average received (from clients) bytes per second" }
    - avg_sent:                      { usage: GAUGE                    ,description: "Average sent (to clients) bytes per second" }
    - avg_xact_time:                 { usage: GAUGE   ,scale: 1e-6     ,description: "Average transaction duration, in seconds" }
    - avg_query_time:                { usage: GAUGE   ,scale: 1e-6     ,description: "Average query duration, in seconds" }
    - avg_wait_time:                 { usage: GAUGE   ,scale: 1e-6     ,description: "Time spent by clients waiting for a server, in seconds (average per second)." }


#==============================================================#
# 0940 pgbouncer_pool
#==============================================================#
# http://www.pgbouncer.org/usage.html#show-pools
pgbouncer_pool_124:
  name: pgbouncer_pool
  desc: Pgbouncer pool stats (1.24+)
  query: SHOW POOLS;
  ttl: 10
  min_version: 12400
  tags: [ pgbouncer ]
  metrics:
    - database:              { usage: LABEL, rename: datname,                description: "Database name of this pool" }
    - user:                  { usage: LABEL,                                 description: "User name of this pool" }
    - cl_active:             { usage: GAUGE, rename: active_clients,         description: "Client connections that are linked to server connection and can process queries" }
    - cl_waiting:            { usage: GAUGE, rename: waiting_clients,        description: "Client connections that have sent queries but have not yet got a server connection" }
    - cl_active_cancel_req:  { usage: GAUGE, rename: active_cancel_clients,  description: "Client connections that have forwarded query cancellations to the server and are waiting for the server response." }
    - cl_waiting_cancel_req: { usage: GAUGE, rename: cancel_clients,         description: "Client connections that have not forwarded query cancellations to the server yet." }
    - sv_active:             { usage: GAUGE, rename: active_servers,         description: "Server connections that are linked to a client" }
    - sv_active_cancel:      { usage: GAUGE, rename: active_cancel_servers,  description: "Server connections that are currently forwarding a cancel request" }
    - sv_being_canceled:     { usage: GAUGE, rename: cancel_servers,         description: "cancel requests have completed that were sent to cancel a query on this server" }
    - sv_idle:               { usage: GAUGE, rename: idle_servers,           description: "Server connections that are unused and immediately usable for client queries" }
    - sv_used:               { usage: GAUGE, rename: used_servers,           description: "Server connections that have been idle for more than server_check_delay (means have to run check query)" }
    - sv_tested:             { usage: GAUGE, rename: tested_servers,         description: "Server connections that are currently running reset or check query" }
    - sv_login:              { usage: GAUGE, rename: login_servers,          description: "Server connections currently in the process of logging in" }
    - maxwait:               { usage: GAUGE,                                 description: "How long the first(oldest) client in the queue has waited, in seconds, key metric" }
    - maxwait_us:            { usage: GAUGE,                                 description: "Microsecond part of the maximum waiting time." }
    - pool_mode:             { usage: LABEL,                                 description: "Pooling mode in use" }
    - load_balance_hosts:    { usage: LABEL,                                 description: "The load_balance_hosts in use" }

pgbouncer_pool_118:
  name: pgbouncer_pool
  desc: Pgbouncer pool stats (1.18-1.23)
  query: SHOW POOLS;
  ttl: 10
  min_version: 11800
  max_version: 12400
  tags: [ pgbouncer ]
  metrics:
    - database:              { usage: LABEL, rename: datname,                description: "Database name of this pool" }
    - user:                  { usage: LABEL,                                 description: "User name of this pool" }
    - cl_active:             { usage: GAUGE, rename: active_clients,         description: "Client connections that are linked to server connection and can process queries" }
    - cl_waiting:            { usage: GAUGE, rename: waiting_clients,        description: "Client connections that have sent queries but have not yet got a server connection" }
    - cl_active_cancel_req:  { usage: GAUGE, rename: active_cancel_clients,  description: "Client connections that have forwarded query cancellations to the server and are waiting for the server response." }
    - cl_waiting_cancel_req: { usage: GAUGE, rename: cancel_clients,         description: "Client connections that have not forwarded query cancellations to the server yet." }
    - sv_active:             { usage: GAUGE, rename: active_servers,         description: "Server connections that are linked to a client" }
    - sv_active_cancel:      { usage: GAUGE, rename: active_cancel_servers,  description: "Server connections that are currently forwarding a cancel request" }
    - sv_being_canceled:     { usage: GAUGE, rename: cancel_servers,         description: "cancel requests have completed that were sent to cancel a query on this server" }
    - sv_idle:               { usage: GAUGE, rename: idle_servers,           description: "Server connections that are unused and immediately usable for client queries" }
    - sv_used:               { usage: GAUGE, rename: used_servers,           description: "Server connections that have been idle for more than server_check_delay (means have to run check query)" }
    - sv_tested:             { usage: GAUGE, rename: tested_servers,         description: "Server connections that are currently running reset or check query" }
    - sv_login:              { usage: GAUGE, rename: login_servers,          description: "Server connections currently in the process of logging in" }
    - maxwait:               { usage: GAUGE,                                 description: "How long the first(oldest) client in the queue has waited, in seconds, key metric" }
    - maxwait_us:            { usage: GAUGE,                                 description: "Microsecond part of the maximum waiting time." }
    - pool_mode:             { usage: LABEL,                                 description: "Pooling mode in use" }

pgbouncer_pool_116:
  name: pgbouncer_pool
  desc: Pgbouncer pool stats (1.16-1.17)
  query: SHOW POOLS;
  ttl: 10
  min_version: 11600
  max_version: 11800
  tags: [ pgbouncer ]
  metrics:
    - database:              { usage: LABEL, rename: datname,          description: "Database name of this pool" }
    - user:                  { usage: LABEL,                           description: "User name of this pool" }
    - cl_active:             { usage: GAUGE, rename: active_clients,   description: "Client connections that are linked to server connection and can process queries" }
    - cl_waiting:            { usage: GAUGE, rename: waiting_clients,  description: "Client connections that have sent queries but have not yet got a server connection" }
    - cl_cancel_req:         { usage: GAUGE, rename: cancel_clients,   description: "Client connections that have not forwarded query cancellations to the server yet." }
    - sv_active:             { usage: GAUGE, rename: active_servers,   description: "Server connections that are linked to a client" }
    - sv_idle:               { usage: GAUGE, rename: idle_servers,     description: "Server connections that are unused and immediately usable for client queries" }
    - sv_used:               { usage: GAUGE, rename: used_servers,     description: "Server connections that have been idle for more than server_check_delay (means have to run check query)" }
    - sv_tested:             { usage: GAUGE, rename: tested_servers,   description: "Server connections that are currently running reset or check query" }
    - sv_login:              { usage: GAUGE, rename: login_servers,    description: "Server connections currently in the process of logging in" }
    - maxwait:               { usage: GAUGE,                           description: "How long the first(oldest) client in the queue has waited, in seconds, key metric" }
    - maxwait_us:            { usage: GAUGE,                           description: "Microsecond part of the maximum waiting time." }
    - pool_mode:             { usage: LABEL,                           description: "Pooling mode in use" }

pgbouncer_pool_108:
  name: pgbouncer_pool
  desc: Pgbouncer pool stats (1.08-1.15)
  query: SHOW POOLS;
  ttl: 10
  min_version: 10800
  max_version: 11600
  tags: [ pgbouncer ]
  metrics:
    - database:              { usage: LABEL, rename: datname,          description: "Database name of this pool" }
    - user:                  { usage: LABEL,                           description: "User name of this pool" }
    - cl_active:             { usage: GAUGE, rename: active_clients,   description: "Client connections that are linked to server connection and can process queries" }
    - cl_waiting:            { usage: GAUGE, rename: waiting_clients,  description: "Client connections that have sent queries but have not yet got a server connection" }
    - sv_active:             { usage: GAUGE, rename: active_servers,   description: "Server connections that are linked to a client" }
    - sv_idle:               { usage: GAUGE, rename: idle_servers,     description: "Server connections that are unused and immediately usable for client queries" }
    - sv_used:               { usage: GAUGE, rename: used_servers,     description: "Server connections that have been idle for more than server_check_delay (means have to run check query)" }
    - sv_tested:             { usage: GAUGE, rename: tested_servers,   description: "Server connections that are currently running reset or check query" }
    - sv_login:              { usage: GAUGE, rename: login_servers,    description: "Server connections currently in the process of logging in" }
    - maxwait:               { usage: GAUGE,                           description: "How long the first(oldest) client in the queue has waited, in seconds, key metric" }
    - maxwait_us:            { usage: GAUGE,                           description: "Microsecond part of the maximum waiting time." }
    - pool_mode:             { usage: LABEL,                           description: "Pooling mode in use" }


#==============================================================#
# 1800 pg_tsdb_hypertable
#==============================================================#
# this collector reqires timescaledb extension to be installed
pg_tsdb_hypertable:
  name: pg_tsdb_hypertable
  desc: TimescaleDB hypertable overview
  query: |-
    SELECT 
      current_database() AS datname,
      format('%I.%I', hypertable_schema, hypertable_name) AS relname,
      num_dimensions AS dimensions, num_chunks AS chunks,
      compression_enabled::BOOLEAN::int AS compressed,
      hypertable_size(format('"%I"."%I"', hypertable_schema, hypertable_name)::RegClass) AS bytes
    FROM timescaledb_information.hypertables;

  ttl: 60
  timeout: 2
  min_version: 90600
  skip: true
  tags: [ "extension:timescaledb", "schema:timescaledb_information" ]
  metrics:
    - datname:         { usage: LABEL ,description: "database name" }
    - relname:         { usage: LABEL ,description: "Hypertable relation name" }
    - dimensions:      { usage: GAUGE ,description: "Number of partitioning dimensions" }
    - chunks:          { usage: GAUGE ,description: "Total chunks of this hypertable" }
    - compressed:      { usage: GAUGE ,description: "1 if compression enabled" }
    - bytes:           { usage: GAUGE ,description: "Total size of hypertable in bytes" }


#==============================================================#
# 1900 pg_citus_node
#==============================================================#
# https://docs.citusdata.com/en/latest/develop/api_metadata.html#worker-node-table
pg_citus_node:
  name: pg_citus_node
  desc: Citus worker coordinator node inventory
  query: |-
    SELECT
      CONCAT(nodename, ':', nodeport) AS node,
      current_database() AS datname,
      nodeid AS id,
      groupid AS group,
      hasmetadata::BOOLEAN::INT AS has_meta,
      isactive::BOOLEAN::INT AS is_active,
      metadatasynced::BOOLEAN::INT AS meta_synced,
      shouldhaveshards::BOOLEAN::INT AS have_shards
    FROM pg_dist_node;
  ttl: 60
  min_version: 90600
  tags: [ "extension:citus" ]
  metrics:
    - node:             { usage: LABEL ,description: "nodename:port of the PostgreSQL instance" }
    - datname:          { usage: LABEL ,description: "database name" }
    - id:               { usage: GAUGE ,description: "auto‑generated node identifier" }
    - group:            { usage: GAUGE ,description: "replication group id (primary + secondaries)" }
    - has_meta:         { usage: GAUGE ,description: "1 = internal use flag set" }
    - is_active:        { usage: GAUGE ,description: "1 = node currently accepts shards" }
    - meta_synced:      { usage: GAUGE ,description: "1 = metadata fully synced to node" }
    - have_shards:      { usage: GAUGE ,description: "1 = rebalancer may place shards here" }


#==============================================================#
# 2000 heartbeat
#==============================================================#
# this is a example of application monitoring and predicate queries
pg_heartbeat:
  name: pg_heartbeat
  desc: monitoring heartbeat in monitor.heartbeat table
  predicate_queries:
    - name: if heartbeat table exists
      predicate_query: |
        SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'monitor' AND table_name = 'heartbeat');
  query: |-
    SELECT id AS cluster_name, extract(EPOCH FROM ts) AS ts, lsn, txid FROM monitor.heartbeat;

  ttl: 10
  min_version: 90100
  tags: [ "dbname:postgres", "schema:monitor" ]
  skip: true
  metrics:
    - cluster_name:     { usage: LABEL   ,description: "cluster_name param of this database cluster" }
    - ts:               { usage: GAUGE   ,description: "unix timestamp of the heartbeat" }
    - lsn:              { usage: COUNTER ,description: "lsn of the heartbeat" }
    - txid:             { usage: GAUGE   ,description: "txid of the heartbeat" }