pg_exporter.yml

#==============================================================#
# Desc      :   pg_exporter metrics collector definition
# Ver       :   PostgreSQL 10 ~ 18+ and pgbouncer 1.9~1.25+
# Ctime     :   2019-12-09
# Mtime     :   2026-03-21
# 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.
# Collector definition examples are shown below.

#==============================================================#
# 3. Collector Example
#==============================================================#
#  # Here is an example of a metrics collector definition
#  pg_primary_only:       # Collector branch name. Must be UNIQUE among the entire configuration
#    name: pg             # Collector namespace, used as METRIC PREFIX, set to branch name by default, can be override
#                         # the same namespace may contain multiple collector branches. It`s the user`s responsibility
#                         # to make sure that AT MOST ONE collector is picked for each namespace.
#
#    desc: PostgreSQL basic information (on primary)                 # Collector description
#    query: |                                                        # Metrics Query SQL
#
#      SELECT extract(EPOCH FROM CURRENT_TIMESTAMP)                  AS timestamp,
#             pg_current_wal_lsn() - '0/0'                           AS lsn,
#             pg_current_wal_insert_lsn() - '0/0'                    AS insert_lsn,
#             pg_current_wal_lsn() - '0/0'                           AS write_lsn,
#             pg_current_wal_flush_lsn() - '0/0'                     AS flush_lsn,
#             extract(EPOCH FROM now() - pg_postmaster_start_time()) AS uptime,
#             extract(EPOCH FROM now() - pg_conf_load_time())        AS conf_reload_time,
#             pg_is_in_backup()                                      AS is_in_backup,
#             extract(EPOCH FROM now() - pg_backup_start_time())     AS backup_time;
#
#                             # [OPTIONAL] metadata fields, control collector behavior
#    ttl: 10                  # Cache TTL: in seconds, how long will pg_exporter cache this collector`s query result.
#    timeout: 0.1             # Query Timeout: in seconds, queries that exceed this limit will be canceled.
#    min_version: 100000      # minimal supported version, boundary IS included. In server version number format,
#    max_version: 130000      # maximal supported version, boundary NOT included, In server version number format
#    fatal: false             # Collector marked `fatal` fails, the entire scrape will abort immediately and marked as failed
#    skip: false              # Collector marked `skip` will not be installed during the planning procedure
#
#    tags: [cluster, primary] # Collector tags, used for planning and scheduling
#
#    # tags are list of strings, which could be:
#    #   * `cluster` marks this query as cluster level, so it will only execute once for the same PostgreSQL Server
#    #   * `primary` or `master`  mark this query can only run on a primary instance (WILL NOT execute if pg_is_in_recovery())
#    #   * `standby` or `replica` mark this query can only run on a replica instance (WILL execute if pg_is_in_recovery())
#    # some special tag prefix have special interpretation:
#    #   * `dbname:<dbname>` means this query will ONLY be executed on database with name `<dbname>`
#    #   * `username:<user>` means this query will only be executed when connect with user `<user>`
#    #   * `extension:<extname>` means this query will only be executed when extension `<extname>` is installed
#    #   * `schema:<nspname>` means this query will only by executed when schema `<nspname>` exist
#    #   * `not:<negtag>` means this query WILL NOT be executed when exporter is tagged with `<negtag>`
#    #   * `<tag>` means this query WILL be executed when exporter is tagged with `<tag>`
#    #   ( <tag> could not be cluster,primary,standby,master,replica,etc...)
#
#    # One or more "predicate queries" may be defined for a metric query. These
#    # are run before the main metric query (after any cache hit check). If all
#    # of them, when run sequentially, return a single row with a single column
#    # boolean true result, the main metric query is executed. If any of them
#    # return false or return zero rows, the main query is skipped. If any
#    # predicate query returns more than one row, a non-boolean result, or fails
#    # with an error, the whole query is marked failed. Predicate queries can be
#    # used to check for the presence of specific functions, tables, extensions,
#    # settings, and vendor-specific pg features before running the main query.
#
#    predicate_queries:
#      - name: predicate query name
#        predicate_query: |
#          SELECT EXISTS (SELECT 1 FROM information_schema.routines WHERE routine_schema = 'pg_catalog' AND routine_name = 'pg_backup_start_time');
#
#    metrics:                 # List of returned columns, each column must have a `name` and `usage`, `rename` and `description` are optional
#      - timestamp:           # Column name, should be exactly the same as returned column name
#          usage: GAUGE       # Metric type, `usage` could be
#                                  * DISCARD: completely ignoring this field
#                                  * LABEL:   use columnName=columnValue as a label in metric
#                                  * GAUGE:   Mark column as a gauge metric, full name will be `<query.name>_<column.name>`
#                                  * COUNTER: Same as above, except it is a counter rather than a gauge.
#          rename: ts         # [OPTIONAL] Alias, optional, the alias will be used instead of the column name
#          description: xxxx  # [OPTIONAL] Description of the column, will be used as a metric description
#          default: 0         # [OPTIONAL] Default value, will be used when column is NULL
#          scale:   1000      # [OPTIONAL] Scale the value by this factor
#      - lsn:
#          usage: COUNTER
#          description: log sequence number, current write location (on primary)
#      - 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
#      - uptime:
#          usage: GAUGE
#          description: seconds since postmaster start
#      - conf_reload_time:
#          usage: GAUGE
#          description: seconds since last configuration reload
#      - is_in_backup:
#          usage: GAUGE
#          description: 1 if backup is in progress
#      - backup_time:
#          usage: GAUGE
#          description: seconds since the current backup start. null if don`t have one
#
#      .... # you can also use rename & scale to customize the metric name and value:
#      - checkpoint_write_time:
#          rename: 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

#==============================================================#
# 4. Collector Presets
#==============================================================#
# pg_exporter is shipped with a series of preset collectors (already numbered and ordered by filename)
#
# 1xx  Basic metrics:        basic info, metadata, settings
# 2xx  Replication metrics:  replication, walreceiver, downstream, sync standby, slots, subscription
# 3xx  Persist metrics:      size, wal, background writer, checkpointer, ssl, checkpoint, recovery, slru cache, shmem usage
# 4xx  Activity metrics:     backend count group by state, wait event, locks, xacts, queries
# 5xx  Progress metrics:     clustering, vacuuming, indexing, basebackup, copy
# 6xx  Database metrics:     pg_database, publication, subscription
# 7xx  Object metrics:       pg_class, table, index, function, sequence, default partition
# 8xx  Optional metrics:     optional metrics collector (disable by default, slow queries)
# 9xx  Pgbouncer metrics:    metrics from pgbouncer admin database `pgbouncer`
#
# 100-599 Metrics for entire database cluster  (scrape once)
# 600-899 Metrics for single database instance (scrape for each database ,except for pg_db itself)

#==============================================================#
# 5. Cache TTL
#==============================================================#
# Cache can be used for reducing query overhead, it can be enabled by setting a non-zero value for `ttl`
# It is highly recommended to use cache to avoid duplicate scrapes. Especially when you got multiple Prometheus
# scraping the same instance with slow monitoring queries. Setting `ttl` to zero or leaving blank will disable
# result caching, which is the default behavior
#
# TTL has to be smaller than your scrape interval. 15s scrape interval and 10s TTL is a good start for
# production environment. Some expensive monitoring queries (such as size/bloat check) will have longer `ttl`
# which can also be used as a mechanism to achieve `different scrape frequency`

#==============================================================#
# 6. Query Timeout
#==============================================================#
# Collectors can be configured with an optional Timeout. If the collector's query executes more than that
# timeout, it will be canceled immediately. Setting the `timeout` to 0 or leaving blank will reset it to
# default timeout 0.1 (100ms). Setting it to any negative number will disable the query timeout feature.
# All queries have a default timeout of 100ms, if exceeded, the query will be canceled immediately to avoid
# avalanche. You can explicitly overwrite that option. but beware: in some extreme cases, if all your
# timeouts sum up greater your scrape/cache interval (usually 15s), the queries may still be jammed.
# or, you can just disable potential slow queries.

#==============================================================#
# 7. 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, 090600 stands for 9.6, and 120100 stands for 12.1
# And beware that version compatibility range is left-inclusive right exclusive: [min, max), set to zero or
# leaving blank will affect as -inf or +inf

#==============================================================#
# 8. Fatality
#==============================================================#
# If a collector is marked with `fatal` falls, the entire scrape operation will be marked as fail and key metrics
# `pg_up` / `pgbouncer_up` will be reset to 0. It is always a good practice to set up AT LEAST ONE fatal
# collector for pg_exporter. `pg.pg_primary_only` and `pgbouncer_list` are the default fatal collector.
#
# If a collector without `fatal` flag fails, it will increase global fail counters. But the scrape operation
# will carry on. The entire scrape result will not be marked as faile, thus will not affect the `<xx>_up` metric.

#==============================================================#
# 9. Skip
#==============================================================#
# Collector with `skip` flag set to true will NOT be installed.
# This could be a handy option to disable collectors

#==============================================================#
# 10. Tags and Planning
#==============================================================#
# Tags are designed for collector planning & schedule. It can be handy to customize which queries run
# on which instances. And thus you can use one-single monolith config for multiple environments
#
#  Tags are a list of strings, each string could be:
#  Pre-defined special tags
#    * `cluster` marks this collector as cluster level, so it will ONLY BE EXECUTED ONCE for the same PostgreSQL Server
#    * `primary` or `master` mark this collector as primary-only, so it WILL NOT work iff pg_is_in_recovery()
#    * `standby` or `replica` mark this collector as replica-only, so it WILL work iff pg_is_in_recovery()
#  Special tag prefix which have different interpretation:
#    * `dbname:<dbname>` means this collector will ONLY work on database with name `<dbname>`
#    * `username:<user>` means this collector will ONLY work when connect with user `<user>`
#    * `extension:<extname>` means this collector will ONLY work when extension `<extname>` is installed
#    * `schema:<nspname>` means this collector will only work when schema `<nspname>` exists
#  Customized positive tags (filter) and negative tags (taint)
#    * `not:<negtag>` means this collector WILL NOT work when exporter is tagged with `<negtag>`
#    * `<tag>` means this query WILL work if exporter is tagged with `<tag>` (special tags not included)
#
#  pg_exporter will trigger the Planning procedure after connecting to the target. It will gather database facts
#  and match them with tags and other metadata (such as supported version range). Collector will only
#  be installed if and only if it is compatible with the target server.


#==============================================================#
# 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,
      pg_current_wal_lsn() - '0/0'                           AS lsn,
      pg_current_wal_insert_lsn() - '0/0'                    AS insert_lsn,
      pg_current_wal_lsn() - '0/0'                           AS write_lsn,
      pg_current_wal_flush_lsn() - '0/0'                     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: 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)
  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,
      pg_last_wal_replay_lsn() - '0/0'                       AS lsn,
      NULL::BIGINT                                           AS insert_lsn,
      NULL::BIGINT                                           AS write_lsn,
      NULL::BIGINT                                           AS flush_lsn,
      pg_last_wal_receive_lsn() - '0/0'                      AS receive_lsn,
      pg_last_wal_replay_lsn() - '0/0'                       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_wal_receive_lsn() = pg_last_wal_replay_lsn() 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_wal_replay_paused() AS is_wal_replay_paused;

  tags: [ cluster, replica ]
  ttl: 1
  min_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_13:
  name: pg_meta
  desc: PostgreSQL meta info for pg 13+, with extra primary conninfo
  query: |
    SELECT 
      (SELECT system_identifier FROM pg_control_system()) AS cluster_id,
      current_setting('cluster_name')                     AS cluster_name,
      current_setting('port')                             AS listen_port,
      current_setting('data_directory', true)             AS data_dir,
      current_setting('config_file', true)                AS conf_path,
      current_setting('hba_file', true)                   AS hba_path,
      current_setting('wal_level')                        AS wal_level,
      current_setting('server_encoding')                  AS encoding,
      current_setting('server_version')                   AS version,
      current_setting('server_version_num')               AS ver_num,
      version()                                           AS ver_str,
      current_setting('shared_preload_libraries', true)   AS extensions,
      current_setting('primary_conninfo', true)           AS primary_conninfo,
      1                                                   AS info
  ttl: 10
  min_version: 130000
  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_10:
  name: pg_meta
  desc: PostgreSQL meta info
  query: |
    SELECT 
      (SELECT system_identifier FROM pg_control_system()) AS cluster_id,
      current_setting('cluster_name')                     AS cluster_name,
      current_setting('port')                             AS listen_port,
      current_setting('data_directory', true)             AS data_dir,
      current_setting('config_file', true)                AS conf_path,
      current_setting('hba_file', true)                   AS hba_path,
      current_setting('wal_level')                        AS wal_level,
      current_setting('server_encoding')                  AS encoding,
      current_setting('server_version')                   AS version,
      current_setting('server_version_num')               AS ver_num,
      version()                                           AS ver_str,
      current_setting('shared_preload_libraries', true)   AS extensions,
      'N/A'                                               AS primary_conninfo,
      1                                                   AS info
  ttl: 10
  min_version: 090600
  max_version: 130000
  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
# All parameters use current_setting(name, missing_ok) for version safety
# Parameters introduced after PG10 use missing_ok=true to return NULL on older versions
pg_setting:
  name: pg_setting
  desc: PostgreSQL shared configuration parameters (shared across all databases)
  query: |
    SELECT
      current_setting('max_connections')::int                          AS max_connections,
      current_setting('max_prepared_transactions')::int                AS max_prepared_transactions,
      current_setting('max_locks_per_transaction')::int                AS max_locks_per_transaction,
      current_setting('max_worker_processes')::int                     AS max_worker_processes,
      current_setting('max_parallel_workers')::int                     AS max_parallel_workers,
      current_setting('max_parallel_workers_per_gather')::int          AS max_parallel_workers_per_gather,
      current_setting('max_parallel_maintenance_workers', true)::int   AS max_parallel_maintenance_workers,
      current_setting('max_replication_slots')::int                    AS max_replication_slots,
      current_setting('max_wal_senders')::int                          AS max_wal_senders,
      current_setting('block_size')::int                               AS block_size,
      current_setting('wal_block_size')::int                           AS wal_block_size,
      pg_size_bytes(current_setting('segment_size'))                   AS segment_size,
      pg_size_bytes(current_setting('wal_segment_size'))               AS wal_segment_size,
      CASE current_setting('data_checksums') WHEN 'on' THEN 1 ELSE 0 END AS data_checksums,
      CASE current_setting('wal_log_hints') WHEN 'on' THEN 1 ELSE 0 END AS wal_log_hints,
      CASE current_setting('fsync') WHEN 'on' THEN 1 ELSE 0 END AS fsync,
      CASE current_setting('full_page_writes') WHEN 'on' THEN 1 ELSE 0 END AS full_page_writes,
      CASE current_setting('wal_level') WHEN 'logical' THEN 3 WHEN 'replica' THEN 2 WHEN 'minimal' THEN 1 ELSE 0 END AS wal_level,
      pg_size_bytes(current_setting('min_wal_size'))                   AS min_wal_size,
      pg_size_bytes(current_setting('max_wal_size'))                   AS max_wal_size,
      pg_size_bytes(current_setting('max_slot_wal_keep_size', true))   AS max_slot_wal_keep_size,
      pg_size_bytes(current_setting('shared_buffers'))                 AS shared_buffers,
      pg_size_bytes(current_setting('work_mem'))                       AS work_mem,
      pg_size_bytes(current_setting('maintenance_work_mem'))           AS maintenance_work_mem,
      pg_size_bytes(current_setting('effective_cache_size'))           AS effective_cache_size,
      pg_size_bytes(current_setting('shared_memory_size', true))       AS shared_memory_size,
      CASE current_setting('huge_pages_status', true) WHEN 'on' THEN 1 WHEN 'off' THEN 0 WHEN 'unknown' THEN -1 ELSE NULL END AS hugepage_status,
      current_setting('shared_memory_size_in_huge_pages', true)::int   AS hugepage_count,
      CASE current_setting('archive_mode') WHEN 'off' THEN 0 WHEN 'on' THEN 1 WHEN 'always' THEN 2 ELSE -1 END AS archive_mode,
      CASE current_setting('autovacuum') WHEN 'on' THEN 1 ELSE 0 END AS autovacuum,
      current_setting('autovacuum_max_workers')::int                   AS autovacuum_max_workers,
      extract(epoch from current_setting('checkpoint_timeout')::interval)::int AS checkpoint_timeout,
      current_setting('checkpoint_completion_target')::float           AS checkpoint_completion_target,
      CASE current_setting('hot_standby') WHEN 'on' THEN 1 ELSE 0 END AS hot_standby,
      CASE current_setting('synchronous_commit')
        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 AS synchronous_commit,
      CASE current_setting('io_method', true)
        WHEN 'sync' THEN 0 WHEN 'worker' THEN 1 WHEN 'io_uring' THEN 2 ELSE NULL END AS io_method;

  ttl: 10
  min_version: 100000
  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" }
    - max_parallel_workers:             { usage: GAUGE ,description: "maximum number of parallel workers that can be active at one time" }
    - max_parallel_workers_per_gather:  { usage: GAUGE ,description: "maximum number of parallel workers per Gather node" }
    - max_parallel_maintenance_workers: { usage: GAUGE ,description: "maximum number of parallel maintenance workers (PG11+, NULL on older)" }
    - max_replication_slots:            { usage: GAUGE ,description: "maximum number of replication slots" }
    - 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" }
    - wal_log_hints:                    { usage: GAUGE ,description: "WAL log hints enabled, 1=on 0=off" }
    - 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=replica 3=logical" }
    - min_wal_size:                     { usage: GAUGE ,description: "minimum WAL size in bytes" }
    - max_wal_size:                     { usage: GAUGE ,description: "maximum WAL size in bytes" }
    - max_slot_wal_keep_size:           { usage: GAUGE ,description: "maximum WAL size retained by replication slots in bytes (PG13+, NULL on older)" }
    - 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" }
    - shared_memory_size:               { usage: GAUGE ,description: "total shared memory size in bytes (PG13+, NULL on older)" }
    - hugepage_status:                  { usage: GAUGE ,description: "huge pages status, 1=on 0=off -1=unknown NULL=unavailable (PG14+)" }
    - hugepage_count:                   { usage: GAUGE ,description: "number of huge pages needed for shared memory (PG14+, NULL on older)" }
    - 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" }
    - io_method:                         { usage: GAUGE ,description: "I/O method (PG18+), 0=sync 1=worker 2=io_uring NULL=unavailable" }



#==============================================================#
# 0210 pg_repl
#==============================================================#
pg_repl_12:
  name: pg_repl
  desc: PostgreSQL replication stat metrics 12+
  query: |
    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, current.lsn - '0/0' AS lsn,
           current.lsn - sent_lsn AS sent_diff, current.lsn - write_lsn AS write_diff, current.lsn - flush_lsn AS flush_diff, current.lsn - replay_lsn AS replay_diff,
           sent_lsn - '0/0' AS sent_lsn, write_lsn - '0/0' AS write_lsn, flush_lsn - '0/0' AS flush_lsn, replay_lsn - '0/0' AS replay_lsn,
           coalesce(extract(EPOCH FROM write_lag), 0)  AS write_lag, coalesce(extract(EPOCH FROM flush_lag), 0)  AS flush_lag, coalesce(extract(EPOCH FROM replay_lag), 0) AS replay_lag,
           extract(EPOCH FROM current_timestamp) AS "time", extract(EPOCH FROM backend_start) AS launch_time, extract(EPOCH FROM reply_time) AS reply_time
    FROM pg_stat_replication, (SELECT CASE WHEN pg_is_in_recovery() THEN pg_last_wal_replay_lsn() ELSE pg_current_wal_lsn() END AS lsn) current;
  ttl: 10
  min_version: 120000
  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" }
    - flush_lag:         { usage: GAUGE   ,description: "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written and flushed it" }
    - 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" }
    - 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" }
    - reply_time:        { usage: GAUGE   ,description: "Send time of last reply message received from standby server" }

pg_repl_10:
  name: pg_repl
  desc: PostgreSQL replication stat metrics v10 v11
  query: |
    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, current.lsn - '0/0' AS lsn,
           current.lsn - sent_lsn AS sent_diff, current.lsn - write_lsn AS write_diff, current.lsn - flush_lsn AS flush_diff, current.lsn - replay_lsn AS replay_diff,
           sent_lsn - '0/0' AS sent_lsn, write_lsn - '0/0' AS write_lsn, flush_lsn - '0/0' AS flush_lsn, replay_lsn - '0/0' AS replay_lsn,
           coalesce(extract(EPOCH FROM write_lag), 0)  AS write_lag, coalesce(extract(EPOCH FROM flush_lag), 0)  AS flush_lag, coalesce(extract(EPOCH FROM replay_lag), 0) AS replay_lag,
           extract(EPOCH FROM current_timestamp) AS "time", extract(EPOCH FROM backend_start) AS launch_time
    FROM pg_stat_replication, (SELECT CASE WHEN pg_is_in_recovery() THEN pg_last_wal_replay_lsn() ELSE pg_current_wal_lsn() END AS lsn) current;
  ttl: 10
  min_version: 100000
  max_version: 120000
  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" }
    - flush_lag:         { usage: GAUGE   ,description: "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written and flushed it" }
    - 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" }
    - 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: 090400
  tags: [ cluster ]
  metrics:
    - names:             { usage: LABEL ,description: "List of standby servers that can support synchronous replication, <null> if not enabled" }
    - 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 group by state
  query: |
    SELECT l.state, coalesce(count, 0 ) AS count FROM unnest(ARRAY ['streaming','startup','catchup', 'backup', 'stopping']) l(state) LEFT JOIN (SELECT state, count(*) AS count FROM pg_stat_replication GROUP BY state)r ON l.state =  r.state;
  ttl: 10
  min_version: 090400
  tags: [ cluster ]
  metrics:
    - state:             { usage: LABEL ,description: "Replication client state, could be one of startup|catchup|streaming|backup|stopping" }
    - count:             { usage: GAUGE ,description: "Count of corresponding state" }


#==============================================================#
# 0240 pg_slot
#==============================================================#
pg_slot_17:
  name: pg_slot
  desc: PostgreSQL replication slot metrics v17, slot also exists on standby
  query: |-
    SELECT s.slot_name, s.slot_type, plugin, database AS datname,datoid,active_pid,
       active,temporary,two_phase,conflicting,failover,synced,
       xmin::TEXT::BIGINT AS xmin,catalog_xmin::TEXT::BIGINT  AS catalog_xmin,
       restart_lsn - '0/0' AS restart_lsn, confirmed_flush_lsn - '0/0' AS confirm_lsn,
       CASE WHEN pg_is_in_recovery() THEN pg_last_wal_replay_lsn() ELSE pg_current_wal_lsn() END - restart_lsn AS retained_bytes,
       safe_wal_size, CASE wal_status WHEN 'reserved' THEN 0 WHEN 'extended' THEN 1 WHEN 'unreserved' THEN 2 WHEN 'lost' THEN 3 ELSE -1 END AS wal_status,
       spill_txns,spill_count,spill_bytes,stream_txns,stream_count,stream_bytes,total_txns,total_bytes,extract(EPOCH FROM stats_reset) AS reset_time,
       extract(EPOCH FROM inactive_since) AS inactive_since, CASE invalidation_reason WHEN 'wal_removed' THEN 1 WHEN 'rows_removed' THEN 2 WHEN 'wal_level_insufficient' THEN 3 ELSE 0 END AS invalidation_reason 
    FROM pg_replication_slots s LEFT OUTER JOIN pg_stat_replication_slots ss ON s.slot_name = ss.slot_name;

  ttl: 10
  min_version: 170000
  tags: [ cluster ]
  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." }
    - two_phase:           { usage: GAUGE    ,description: "True(1) if the slot is enabled for decoding prepared transactions. Always false for physical slots." }
    - conflicting:         { usage: GAUGE    ,description: "True(1) if this logical slot conflicted with recovery. Always NULL for physical slots." }
    - failover:            { usage: GAUGE    ,description: "True(1) if this is a logical slot enabled to be synced to the standbys" }
    - synced:              { usage: GAUGE    ,description: "True(1) if this is a logical slot that was synced from a primary server" }
    - 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" }
    - safe_wal_size:       { usage: GAUGE    ,description: "bytes that can be written to WAL which will not make slot into lost" }
    - wal_status:          { usage: GAUGE    ,description: "WAL reserve status 0-3 means reserved,extended,unreserved,lost, -1 means other" }
    - spill_txns:          { usage: COUNTER  ,description: "Xacts that spilled to disk due to logical decode mem exceeding (subtrans included)" }
    - spill_count:         { usage: COUNTER  ,description: "Xacts that spilled to disk due to logical decode mem exceeding" }
    - spill_bytes:         { usage: COUNTER  ,description: "Bytes that spilled to disk due to logical decode mem exceeding" }
    - stream_txns:         { usage: COUNTER  ,description: "Xacts that streamed to decoding output plugin after mem exceed" }
    - stream_count:        { usage: COUNTER  ,description: "Xacts that streamed to decoding output plugin after mem exceed" }
    - stream_bytes:        { usage: COUNTER  ,description: "Bytes that streamed to decoding output plugin after mem exceed" }
    - total_txns:          { usage: COUNTER  ,description: "Number of decoded xacts sent to the decoding output plugin for this slot" }
    - total_bytes:         { usage: COUNTER  ,description: "Number of decoded bytes sent to the decoding output plugin for this slot" }
    - reset_time:          { usage: GAUGE    ,description: "When statistics were last reset" }
    - invalidation_reason: { usage: GAUGE    ,description: "ok=0, wal_removed=1, rows_removed=2, wal_level_insufficient=3" }
    - inactive_since:      { usage: GAUGE    ,description: "The time when the slot became inactive" }

pg_slot_16:
  name: pg_slot
  desc: PostgreSQL replication slot metrics v16 with conflicting, now slot also exists on standby
  query: |-
    SELECT s.slot_name, s.slot_type, plugin, database AS datname,datoid,active_pid,
      active,temporary,two_phase,conflicting,xmin::TEXT::BIGINT AS xmin,catalog_xmin::TEXT::BIGINT  AS catalog_xmin,
      restart_lsn - '0/0' AS restart_lsn, confirmed_flush_lsn - '0/0' AS confirm_lsn,
      CASE WHEN pg_is_in_recovery() THEN pg_last_wal_replay_lsn() ELSE pg_current_wal_lsn() END - restart_lsn AS retained_bytes,
      safe_wal_size, CASE wal_status WHEN 'reserved' THEN 0 WHEN 'extended' THEN 1 WHEN 'unreserved' THEN 2 WHEN 'lost' THEN 3 ELSE -1 END AS wal_status,
      spill_txns,spill_count,spill_bytes,stream_txns,stream_count,stream_bytes,total_txns,total_bytes,extract(EPOCH FROM stats_reset) AS reset_time
    FROM pg_replication_slots s LEFT OUTER JOIN pg_stat_replication_slots ss ON s.slot_name = ss.slot_name;

  ttl: 10
  min_version: 160000
  max_version: 170000
  tags: [ cluster ]
  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." }
    - two_phase:           { usage: GAUGE    ,description: "True(1) if the slot is enabled for decoding prepared transactions. Always false for physical slots." }
    - conflicting:         { usage: GAUGE    ,description: "True if this logical slot conflicted with recovery. Always NULL for physical slots." }
    - 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" }
    - safe_wal_size:       { usage: GAUGE    ,description: "bytes that can be written to WAL which will not make slot into lost" }
    - wal_status:          { usage: GAUGE    ,description: "WAL reserve status 0-3 means reserved,extended,unreserved,lost, -1 means other" }
    - spill_txns:          { usage: COUNTER  ,description: "Xacts that spilled to disk due to logical decode mem exceeding (subtrans included)" }
    - spill_count:         { usage: COUNTER  ,description: "Xacts that spilled to disk due to logical decode mem exceeding" }
    - spill_bytes:         { usage: COUNTER  ,description: "Bytes that spilled to disk due to logical decode mem exceeding" }
    - stream_txns:         { usage: COUNTER  ,description: "Xacts that streamed to decoding output plugin after mem exceed" }
    - stream_count:        { usage: COUNTER  ,description: "Xacts that streamed to decoding output plugin after mem exceed" }
    - stream_bytes:        { usage: COUNTER  ,description: "Bytes that streamed to decoding output plugin after mem exceed" }
    - total_txns:          { usage: COUNTER  ,description: "Number of decoded xacts sent to the decoding output plugin for this slot" }
    - total_bytes:         { usage: COUNTER  ,description: "Number of decoded bytes sent to the decoding output plugin for this slot" }
    - reset_time:          { usage: GAUGE    ,description: "When statistics were last reset" }

pg_slot_14:
  name: pg_slot
  desc: PostgreSQL replication slot metrics v14 with pg_stat_replication_slots metrics
  query: |-
    SELECT s.slot_name, s.slot_type, plugin, database AS datname,datoid,active_pid,
      active,temporary,two_phase,xmin::TEXT::BIGINT AS xmin,catalog_xmin::TEXT::BIGINT  AS catalog_xmin,
      restart_lsn - '0/0' AS restart_lsn, confirmed_flush_lsn - '0/0' AS confirm_lsn,
      CASE WHEN pg_is_in_recovery() THEN pg_last_wal_replay_lsn() ELSE pg_current_wal_lsn() END - restart_lsn AS retained_bytes,
      safe_wal_size, CASE wal_status WHEN 'reserved' THEN 0 WHEN 'extended' THEN 1 WHEN 'unreserved' THEN 2 WHEN 'lost' THEN 3 ELSE -1 END AS wal_status,
      spill_txns,spill_count,spill_bytes,stream_txns,stream_count,stream_bytes,total_txns,total_bytes,extract(EPOCH FROM stats_reset) AS reset_time
    FROM pg_replication_slots s LEFT OUTER JOIN pg_stat_replication_slots ss ON s.slot_name = ss.slot_name;

  ttl: 10
  min_version: 140000
  max_version: 160000
  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." }
    - two_phase:           { usage: GAUGE    ,description: "True(1) if the slot is enabled for decoding prepared transactions. Always false for physical slots." }
    - 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" }
    - safe_wal_size:       { usage: GAUGE    ,description: "bytes that can be written to WAL which will not make slot into lost" }
    - wal_status:          { usage: GAUGE    ,description: "WAL reserve status 0-3 means reserved,extended,unreserved,lost, -1 means other" }
    - spill_txns:          { usage: COUNTER  ,description: "Xacts that spilled to disk due to logical decode mem exceeding (subtrans included)" }
    - spill_count:         { usage: COUNTER  ,description: "Xacts that spilled to disk due to logical decode mem exceeding" }
    - spill_bytes:         { usage: COUNTER  ,description: "Bytes that spilled to disk due to logical decode mem exceeding" }
    - stream_txns:         { usage: COUNTER  ,description: "Xacts that streamed to decoding output plugin after mem exceed" }
    - stream_count:        { usage: COUNTER  ,description: "Xacts that streamed to decoding output plugin after mem exceed" }
    - stream_bytes:        { usage: COUNTER  ,description: "Bytes that streamed to decoding output plugin after mem exceed" }
    - total_txns:          { usage: COUNTER  ,description: "Number of decoded xacts sent to the decoding output plugin for this slot" }
    - total_bytes:         { usage: COUNTER  ,description: "Number of decoded bytes sent to the decoding output plugin for this slot" }
    - reset_time:          { usage: GAUGE    ,description: "When statistics were last reset" }

pg_slot_13:
  name: pg_slot
  desc: PostgreSQL replication slot metrics v13 (wal safe size and status)
  query: |-
    SELECT slot_name, slot_type, plugin, database AS datname,datoid,active_pid,
      active,temporary,xmin::TEXT::BIGINT AS xmin,catalog_xmin::TEXT::BIGINT  AS catalog_xmin,
      restart_lsn - '0/0' AS restart_lsn, confirmed_flush_lsn - '0/0' AS confirm_lsn,
      CASE WHEN pg_is_in_recovery() THEN pg_last_wal_replay_lsn() ELSE pg_current_wal_lsn() END - restart_lsn AS retained_bytes,
      safe_wal_size, CASE wal_status WHEN 'reserved' THEN 0 WHEN 'extended' THEN 1 WHEN 'unreserved' THEN 2 WHEN 'lost' THEN 3 ELSE -1 END AS wal_status
    FROM pg_replication_slots;

  ttl: 10
  min_version: 130000
  max_version: 140000
  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." }
    - 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" }
    - safe_wal_size:       { usage: GAUGE    ,description: "bytes that can be written to WAL which will not make slot into lost" }
    - wal_status:          { usage: GAUGE    ,description: "WAL reserve status 0-3 means reserved,extended,unreserved,lost, -1 means other" }

pg_slot_10:
  name: pg_slot
  desc: PostgreSQL replication slot metrics 10 ~ 12
  query: |-
    SELECT slot_name, slot_type, plugin, database AS datname,datoid,active_pid,
      active,temporary,xmin::TEXT::BIGINT AS xmin,catalog_xmin::TEXT::BIGINT  AS catalog_xmin,
      restart_lsn - '0/0' AS restart_lsn, confirmed_flush_lsn - '0/0' AS confirm_lsn,
      CASE WHEN pg_is_in_recovery() THEN pg_last_wal_replay_lsn() ELSE pg_current_wal_lsn() END - restart_lsn AS retained_bytes
    FROM pg_replication_slots;

  ttl: 10
  min_version: 100000
  max_version: 130000
  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." }
    - 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" }


#==============================================================#
# 0250 pg_recv
#==============================================================#
pg_recv_13:
  name: pg_recv
  desc: PostgreSQL walreceiver metrics 13+
  query: |-
    SELECT 
      coalesce(sender_host, (regexp_match(conninfo, '.*host=(\S+).*'))[1]) AS sender_host, coalesce(sender_port::TEXT, (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,
      receive_start_lsn - '0/0' AS init_lsn,receive_start_tli AS init_tli,
      flushed_lsn - '0/0' AS flush_lsn,written_lsn - '0/0' AS write_lsn, received_tli AS flush_tli, latest_end_lsn - '0/0' 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: 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" }
    - write_lsn:           { usage: COUNTER ,description: "Last write-ahead log location already received and written to disk, but not flushed." }
    - 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" }

pg_recv_11:
  name: pg_recv
  desc: PostgreSQL walreceiver metrics (11-12)
  query: |-
    SELECT 
      coalesce(sender_host, (regexp_match(conninfo, '.*host=(\S+).*'))[1]) AS sender_host, coalesce(sender_port::TEXT, (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,
      receive_start_lsn - '0/0' AS init_lsn,receive_start_tli AS init_tli,
      received_lsn - '0/0' AS flush_lsn, received_tli AS flush_tli, latest_end_lsn - '0/0' 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
  tags: [ cluster, replica ]
  min_version: 110000
  max_version: 130000
  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" }

pg_recv_10:
  name: pg_recv
  desc: PostgreSQL walreceiver metrics (10)
  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,
      receive_start_lsn - '0/0' AS init_lsn,receive_start_tli AS init_tli,
      received_lsn - '0/0' AS flush_lsn, received_tli AS flush_tli, latest_end_lsn - '0/0' 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
  tags: [ cluster, replica ]
  min_version: 100000
  max_version: 110000
  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" }

#==============================================================#
# 0260 pg_sub
#==============================================================#
pg_sub_16:
  name: pg_sub
  desc: PostgreSQL subscription statistics (16+)
  query: |-
    SELECT 
      s1.subname, subid AS id, pid, received_lsn, reported_lsn,
      msg_send_time, msg_recv_time, reported_time,
      apply_error_count, sync_error_count
    FROM
      (SELECT
        subname, subid, pid,
        received_lsn - '0/0' AS received_lsn, latest_end_lsn - '0/0' AS reported_lsn,
        extract(epoch from last_msg_send_time) AS msg_send_time,
        extract(epoch from last_msg_receipt_time) AS msg_recv_time,
        extract(epoch from latest_end_time) AS reported_time
      FROM pg_stat_subscription 
      WHERE relid IS NULL AND leader_pid IS NULL) s1
    LEFT OUTER JOIN pg_stat_subscription_stats s2 USING(subid);

  ttl: 10
  min_version: 160000
  tags: [ cluster ]
  metrics:
    - subname:             { usage: LABEL   ,description: "Name of this subscription" }
    - id:                  { usage: GAUGE   ,description: "OID of the subscription" }
    - pid:                 { usage: GAUGE   ,description: "Process ID of the subscription leader apply worker" }
    - received_lsn:        { usage: COUNTER ,description: "Last write-ahead log location received" }
    - 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" }
    - apply_error_count:   { usage: COUNTER ,description: "Number of times an error occurred while applying changes" }
    - sync_error_count:    { usage: COUNTER ,description: "Number of times an error occurred during the initial table synchronization" }

pg_sub_15:
  name: pg_sub
  desc: PostgreSQL subscription statistics (15)
  query: |-
    SELECT 
      s1.subname, subid AS id, pid, received_lsn, reported_lsn,
      msg_send_time, msg_recv_time, reported_time,
      apply_error_count, sync_error_count
    FROM
      (SELECT
        subname, subid, pid,
        received_lsn - '0/0' AS received_lsn, latest_end_lsn - '0/0' AS reported_lsn,
        extract(epoch from last_msg_send_time) AS msg_send_time,
        extract(epoch from last_msg_receipt_time) AS msg_recv_time,
        extract(epoch from latest_end_time) AS reported_time
      FROM pg_stat_subscription WHERE relid ISNULL) s1
    LEFT OUTER JOIN pg_stat_subscription_stats s2 USING(subid);

  ttl: 10
  min_version: 150000
  max_version: 160000
  tags: [ cluster ]
  metrics:
    - subname:             { usage: LABEL   ,description: "Name of this subscription" }
    - id:                  { usage: GAUGE   ,description: "OID of the subscription" }
    - pid:                 { usage: GAUGE   ,description: "Process ID of the subscription main apply worker process" }
    - received_lsn:        { usage: COUNTER ,description: "Last write-ahead log location received" }
    - 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" }
    - apply_error_count:   { usage: COUNTER ,description: "Number of times an error occurred while applying changes." }
    - sync_error_count:    { usage: COUNTER ,description: "Number of times an error occurred during the initial table synchronization" }

pg_sub_10:
  name: pg_sub
  desc: PostgreSQL subscription statistics (10-14)
  query: |-
    SELECT 
      subname, subid AS id, pid,
      received_lsn - '0/0' AS received_lsn, latest_end_lsn - '0/0' AS reported_lsn,
      extract(epoch from last_msg_send_time) AS msg_send_time,
      extract(epoch from last_msg_receipt_time) AS msg_recv_time,
      extract(epoch from latest_end_time) AS reported_time
    FROM pg_stat_subscription WHERE relid ISNULL;

  ttl: 10
  min_version: 100000
  max_version: 150000
  tags: [ cluster ]
  metrics:
    - subname:             { usage: LABEL   ,description: "Name of this subscription" }
    - id:                  { usage: GAUGE   ,description: "OID of the subscription" }
    - pid:                 { usage: GAUGE   ,description: "Process ID of the subscription main apply worker process" }
    - received_lsn:        { usage: COUNTER ,description: "Last write-ahead log location received" }
    - 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" }


#==============================================================#
# 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, remote_lsn - '0/0' AS remote_lsn, local_lsn - '0/0' 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: 090500
  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." }


#==============================================================#
# 0300 pg_io
#==============================================================#
pg_io_18:
  name: pg_io
  desc: PostgreSQL I/O stats since v18
  query: |-
    SELECT backend_type AS "type",object,context,reads,read_bytes,read_time,writes,write_bytes,write_time,writebacks,writeback_time,
    extends,extend_bytes,extend_time,hits,evictions,reuses,fsyncs,fsync_time,extract(EPOCH FROM stats_reset) AS reset_time FROM pg_stat_io;

  ttl: 10
  timeout: 1
  min_version: 180000
  tags: [ cluster ]
  metrics:
    - type:               { usage: LABEL                              ,description: "Type of backend" }
    - object:             { usage: LABEL                              ,description: "Target object of an I/O operation, relation or temp" }
    - context:            { usage: LABEL                              ,description: "The context of an I/O operation. normal,vacuum,bulkread,bulkwrite" }
    - reads:              { usage: COUNTER ,default: 0                ,description: "Number of read operations, each of the size specified in op_bytes." }
    - read_bytes:         { usage: COUNTER ,default: 0                ,description: "Number of read bytes" }
    - read_time:          { usage: COUNTER ,default: 0  ,scale: 1e-3  ,description: "Time spent in read operations in seconds" }
    - writes:             { usage: COUNTER ,default: 0                ,description: "Number of write operations, each of the size specified in op_bytes." }
    - write_time:         { usage: COUNTER ,default: 0  ,scale: 1e-3  ,description: "Time spent in write operations in seconds" }
    - write_bytes:        { usage: COUNTER ,default: 0                ,description: "Number of write bytes" }
    - writebacks:         { usage: COUNTER ,default: 0                ,description: "Number of units of size op_bytes which the process requested the kernel write out to permanent storage." }
    - writeback_time:     { usage: COUNTER ,default: 0  ,scale: 1e-3  ,description: "Time spent in writeback operations in seconds" }
    - extends:            { usage: COUNTER ,default: 0                ,description: "Number of relation extend operations, each of the size specified in op_bytes." }
    - extend_bytes:       { usage: COUNTER ,default: 0                ,description: "Number of extend bytes" }
    - extend_time:        { usage: COUNTER ,default: 0  ,scale: 1e-3  ,description: "Time spent in extend operations in seconds" }
    - hits:               { usage: COUNTER ,default: 0                ,description: "The number of times a desired block was found in a shared buffer." }
    - evictions:          { usage: COUNTER ,default: 0                ,description: "Number of times a block has been written out from a shared or local buffer" }
    - reuses:             { usage: COUNTER ,default: 0                ,description: "The number of times an existing buffer is reused" }
    - fsyncs:             { usage: COUNTER ,default: 0                ,description: "Number of fsync calls. These are only tracked in context normal" }
    - fsync_time:         { usage: COUNTER ,default: 0  ,scale: 1e-3  ,description: "Time spent in fsync operations in seconds" }
    - reset_time:         { usage: GAUGE                              ,description: "Timestamp at which these statistics were last reset" }

pg_io_16:
  name: pg_io
  desc: PostgreSQL I/O stats
  query: |-
    SELECT backend_type AS "type", object, context, reads, read_time,writes,write_time,writebacks,writeback_time,extends,
      extend_time,hits,evictions,reuses,fsyncs,fsync_time,extract(EPOCH FROM stats_reset) AS reset_time FROM pg_stat_io;

  ttl: 10
  timeout: 1
  min_version: 160000
  max_version: 180000
  tags: [ cluster ]
  metrics:
    - type:               { usage: LABEL                              ,description: "Type of backend" }
    - object:             { usage: LABEL                              ,description: "Target object of an I/O operation, relation or temp" }
    - context:            { usage: LABEL                              ,description: "The context of an I/O operation. normal,vacuum,bulkread,bulkwrite" }
    - reads:              { usage: COUNTER ,default: 0                ,description: "Number of read operations, each of the size specified in op_bytes." }
    - read_time:          { usage: COUNTER ,default: 0  ,scale: 1e-3  ,description: "Time spent in read operations in seconds" }
    - writes:             { usage: COUNTER ,default: 0                ,description: "Number of write operations, each of the size specified in op_bytes." }
    - write_time:         { usage: COUNTER ,default: 0  ,scale: 1e-3  ,description: "Time spent in write operations in seconds" }
    - writebacks:         { usage: COUNTER ,default: 0                ,description: "Number of units of size op_bytes which the process requested the kernel write out to permanent storage." }
    - writeback_time:     { usage: COUNTER ,default: 0  ,scale: 1e-3  ,description: "Time spent in writeback operations in seconds" }
    - extends:            { usage: COUNTER ,default: 0                ,description: "Number of relation extend operations, each of the size specified in op_bytes." }
    - extend_time:        { usage: COUNTER ,default: 0  ,scale: 1e-3  ,description: "Time spent in extend operations in seconds" }
    - hits:               { usage: COUNTER ,default: 0                ,description: "The number of times a desired block was found in a shared buffer." }
    - evictions:          { usage: COUNTER ,default: 0                ,description: "Number of times a block has been written out from a shared or local buffer" }
    - reuses:             { usage: COUNTER ,default: 0                ,description: "The number of times an existing buffer is reused" }
    - fsyncs:             { usage: COUNTER ,default: 0                ,description: "Number of fsync calls. These are only tracked in context normal" }
    - fsync_time:         { usage: COUNTER ,default: 0  ,scale: 1e-3  ,description: "Time spent in fsync operations in seconds" }
    - reset_time:         { usage: GAUGE                              ,description: "Timestamp at which these statistics were last reset" }


#==============================================================#
# 0310 pg_size
#==============================================================#
pg_size:
  name: pg_size
  desc: PostgreSQL Database, WAL, Log size since v10
  query: |-
    SELECT datname, pg_database_size(oid) AS bytes FROM pg_database
    UNION ALL SELECT 'log', CASE WHEN current_setting('logging_collector') = 'on' THEN COALESCE((SELECT SUM(size) FROM pg_catalog.pg_ls_logdir()), 0) ELSE 0 END
    UNION ALL SELECT 'wal', COALESCE((SELECT SUM(size) FROM pg_catalog.pg_ls_waldir()), 0);

  ttl: 60
  timeout: 1
  min_version: 100000
  tags: [ cluster ]
  metrics:
    - datname:             { usage: LABEL   ,description: "Database name, or special category wal, or log" }
    - bytes:               { usage: GAUGE   ,description: "File 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: 090400
  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_17:
  name: pg_bgwriter
  desc: "PostgreSQL background writer metrics PG 17+"
  query: SELECT buffers_clean, maxwritten_clean, buffers_alloc, extract(EPOCH FROM stats_reset) AS reset_time FROM pg_stat_bgwriter;
  ttl: 10
  min_version: 170000
  tags: [ cluster ]
  metrics:
    - buffers_clean:       { usage: COUNTER ,description: "Number of buffers written by the background writer" }
    - maxwritten_clean:    { usage: COUNTER ,description: "Number of times the background writer stopped a cleaning scan because it had written too many buffers" }
    - buffers_alloc:       { usage: COUNTER ,description: "Number of buffers allocated" }
    - reset_time:          { usage: GAUGE   ,description: "Time at which bgwriter statistics were last reset" }

pg_bgwriter_10:
  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: 090400
  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" }

#==============================================================#
# 0331 pg_checkpointer
#==============================================================#
pg_checkpointer_18:
  name: pg_checkpointer
  desc: "PostgreSQL checkpointer stat metrics for pg 18+"
  query: SELECT num_timed, num_requested, num_done, restartpoints_timed, restartpoints_req, restartpoints_done, write_time, sync_time, buffers_written, slru_written, extract(EPOCH FROM stats_reset) AS reset_time FROM pg_stat_checkpointer;
  ttl: 10
  min_version: 180000
  tags: [ cluster ]
  metrics:
    - num_timed:             { usage: COUNTER ,rename: timed ,description: "Number of scheduled checkpoints that have been performed" }
    - num_requested:         { usage: COUNTER ,rename: req   ,description: "Number of requested checkpoints that have been performed" }
    - num_done:              { usage: COUNTER ,rename: done  ,description: "Number of checkpoints that have been performed" }
    - restartpoints_timed:   { usage: COUNTER                ,description: "Number of scheduled restartpoints due to timeout or after a failed attempt to perform it" }
    - restartpoints_req:     { usage: COUNTER                ,description: "Number of requested restartpoints" }
    - restartpoints_done:    { usage: COUNTER                ,description: "Number of restartpoints that have been performed" }
    - 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" }
    - 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_written:       { usage: COUNTER                ,description: "Number of buffers written during checkpoints and restartpoints" }
    - slru_written:          { usage: COUNTER                ,description: "Number of SLRU buffers written during checkpoints and restartpoints" }
    - reset_time:            { usage: GAUGE                  ,description: "Time at which checkpointer statistics were last reset" }

pg_checkpointer_17:
  name: pg_checkpointer
  desc: "PostgreSQL checkpointer stat metrics for pg 17"
  query: SELECT num_timed, num_requested, restartpoints_timed, restartpoints_req, restartpoints_done, write_time, sync_time, buffers_written, extract(EPOCH FROM stats_reset) AS reset_time FROM pg_stat_checkpointer;
  ttl: 10
  min_version: 170000
  max_version: 180000
  tags: [ cluster ]
  metrics:
    - num_timed:             { usage: COUNTER ,rename: timed ,description: "Number of scheduled checkpoints that have been performed" }
    - num_requested:         { usage: COUNTER ,rename: req   ,description: "Number of requested checkpoints that have been performed" }
    - restartpoints_timed:   { usage: COUNTER                ,description: "Number of scheduled restartpoints due to timeout or after a failed attempt to perform it" }
    - restartpoints_req:     { usage: COUNTER                ,description: "Number of requested restartpoints" }
    - restartpoints_done:    { usage: COUNTER                ,description: "Number of restartpoints that have been performed" }
    - 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" }
    - 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_written:       { usage: COUNTER                ,description: "Number of buffers written during checkpoints and restartpoints" }
    - reset_time:            { usage: GAUGE                  ,description: "Time at which checkpointer statistics were last reset" }

pg_checkpointer_10:
  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: 090400
  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 in the portion of checkpoint processing where files are written to disk, in seconds" }
    - checkpoint_sync_time:  { usage: COUNTER ,rename: sync_time  ,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 ,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: 090500
  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 since 10
  query: |-
    SELECT 
      checkpoint_lsn - '0/0' AS checkpoint_lsn,
      redo_lsn - '0/0' 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: 100000
  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: 100000
  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,
      min_recovery_end_lsn - '0/0' AS min_lsn,
      backup_start_lsn - '0/0'     AS backup_start_lsn,
      backup_end_lsn - '0/0'       AS backup_end_lsn,
      end_of_backup_record_required AS require_record
    FROM pg_control_recovery();
  ttl: 10
  min_version: 090600
  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" }

pg_recovery_prefetch:
  name: pg_recovery_prefetch
  desc: PostgreSQL recovery prefetch metrics (15+)
  query: SELECT prefetch,hit,skip_init,skip_new,skip_fpw,skip_rep,wal_distance,block_distance,io_depth,extract(EPOCH FROM stats_reset) AS reset_time FROM pg_stat_recovery_prefetch;
  ttl: 10
  min_version: 150000
  tags: [ cluster, replica ]
  metrics:
    - prefetch:       { usage: COUNTER ,description: "Number of blocks prefetched because they were not in the buffer pool" }
    - hit:            { usage: COUNTER ,description: "Number of blocks not prefetched because they were already in the buffer pool" }
    - skip_init:      { usage: COUNTER ,description: "Number of blocks not prefetched because they would be zero-initialized" }
    - skip_new:       { usage: COUNTER ,description: "Number of blocks not prefetched because they didn't exist yet" }
    - skip_fpw:       { usage: COUNTER ,description: "Number of blocks not prefetched because a full page image was included in the WAL" }
    - skip_rep:       { usage: COUNTER ,description: "Number of blocks not prefetched because they were already recently prefetched" }
    - wal_distance:   { usage: GAUGE   ,description: "How many bytes ahead the prefetcher is looking" }
    - block_distance: { usage: GAUGE   ,description: "How many blocks ahead the prefetcher is looking" }
    - io_depth:       { usage: GAUGE   ,description: "How many prefetches have been initiated but are not yet known to have completed" }
    - reset_time:     { usage: GAUGE   ,description: "Time at which these recovery prefetch statistics were last reset" }


#==============================================================#
# 0370 pg_slru
#==============================================================#
pg_slru_13:
  name: pg_slru
  desc: PostgreSQL simple-least-recently-used (SLRU) cache statistics v13
  query: SELECT name, blks_zeroed, blks_hit, blks_read, blks_written, blks_exists, flushes, truncates, extract(EPOCH FROM stats_reset) AS reset_time FROM pg_stat_slru;
  ttl: 60
  min_version: 130000
  tags: [ cluster ]
  metrics:
    - name:         { usage: LABEL   ,description: "Name of the SLRU" }
    - blks_zeroed:  { usage: COUNTER ,description: "Number of blocks zeroed during initializations" }
    - blks_hit:     { usage: COUNTER ,description: "Number of times disk blocks were found already in the SLRU, so that a read was not necessary" }
    - blks_read:    { usage: COUNTER ,description: "Number of disk blocks read for this SLRU" }
    - blks_written: { usage: COUNTER ,description: "Number of disk blocks written for this SLRU" }
    - blks_exists:  { usage: COUNTER ,description: "Number of blocks checked for existence for this SLRU" }
    - flushes:      { usage: COUNTER ,description: "Number of flushes of dirty data for this SLRU" }
    - truncates:    { usage: COUNTER ,description: "Number of truncates for this SLRU" }
    - reset_time:   { usage: GAUGE   ,description: "Time at which these statistics were last reset" }


#==============================================================#
# 0380 pg_shmem
#==============================================================#
# pg_shmem require su privilege to work. Disable it or create auxiliary function with su before use:
# CREATE OR REPLACE FUNCTION monitor.pg_shmem() RETURNS SETOF pg_shmem_allocations AS $$ SELECT * FROM pg_shmem_allocations;$$ LANGUAGE SQL SECURITY DEFINER;
pg_shmem:
  name: pg_shmem
  desc: Allocations made from the server's main shared memory segment
  query: SELECT coalesce(name, 'Free') AS name, off AS offset, size, allocated_size FROM monitor.pg_shmem();
  ttl: 60
  min_version: 130000
  skip: true            # disable it by default
  tags: [cluster, "schema:monitor" ]
  metrics:
    - name:            { usage: LABEL ,description: "Name of the shared memory allocation" }
    - offset:          { usage: GAUGE ,description: "The offset at which the allocation starts" }
    - size:            { usage: GAUGE ,description: "Size of the allocation" }
    - allocated_size:  { usage: GAUGE ,description: "Size of the allocation including padding" }


#==============================================================#
# 0390 pg_wal
#==============================================================#
pg_wal_18:
  name: pg_wal
  desc: PostgreSQL WAL statistics since v18 with some col removed
  query: SELECT wal_records AS records, wal_fpi AS fpi, wal_bytes AS bytes, wal_buffers_full AS buffers_full,extract(EPOCH FROM stats_reset) AS reset_time FROM pg_stat_wal;
  ttl: 10
  tags: [ cluster ]
  min_version: 180000
  metrics:
    - records:      { usage: COUNTER              ,description: "Total number of WAL records generated" }
    - fpi:          { usage: COUNTER              ,description: "Total number of WAL full page images generated" }
    - bytes:        { usage: COUNTER              ,description: "Total amount of WAL generated in bytes" }
    - buffers_full: { usage: COUNTER              ,description: "Number of times WAL data was written to disk because WAL buffers became full" }
    - reset_time:   { usage: GAUGE                ,description: "When statistics were last reset" }

pg_wal_14:
  name: pg_wal
  desc: PostgreSQL WAL statistics since v14
  query: SELECT wal_records AS records, wal_fpi AS fpi, wal_bytes AS bytes, wal_buffers_full AS buffers_full, wal_write AS write, wal_sync AS sync, wal_write_time AS write_time, wal_sync_time AS sync_time, extract(EPOCH FROM stats_reset) AS reset_time FROM pg_stat_wal;
  ttl: 10
  tags: [ cluster ]
  min_version: 140000
  max_version: 180000
  metrics:
    - records:      { usage: COUNTER              ,description: "Total number of WAL records generated" }
    - fpi:          { usage: COUNTER              ,description: "Total number of WAL full page images generated" }
    - bytes:        { usage: COUNTER              ,description: "Total amount of WAL generated in bytes" }
    - buffers_full: { usage: COUNTER              ,description: "Number of times WAL data was written to disk because WAL buffers became full" }
    - write:        { usage: COUNTER              ,description: "Number of times WAL buffers were written out to disk via XLogWrite request." }
    - sync:         { usage: COUNTER              ,description: "Number of times WAL files were synced to disk via issue_xlog_fsync request" }
    - write_time:   { usage: COUNTER ,scale: 1e-3 ,description: "Total amount of time spent writing WAL buffers to disk via XLogWrite request in seconds" }
    - sync_time:    { usage: COUNTER ,scale: 1e-3 ,description: "Total amount of time spent syncing WAL files to disk via issue_xlog_fsync request, in seconds" }
    - reset_time:   { usage: GAUGE                ,description: "When statistics were last reset" }


#==============================================================#
# 0410 pg_activity
#==============================================================#
pg_activity:
  name: pg_activity
  desc: PostgreSQL backend activity group by database and state
  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: 090400
  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)" }


#==============================================================#
# 0420 pg_wait
#==============================================================#
pg_wait:
  name: pg_wait
  desc: PostgreSQL backend client count group by wait event type since 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: 090600
  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" }


#==============================================================#
# 0430 pg_backend
#==============================================================#
pg_backend:
  name: pg_backend
  desc: PostgreSQL backend client count group by wait event type since 10
  query: SELECT backend_type AS "type", count(*) AS count FROM pg_stat_activity GROUP BY backend_type;
  ttl: 10
  min_version: 100000
  tags: [ cluster ]
  metrics:
    - type:  { usage: LABEL ,description: "Database backend process type" }
    - count: { usage: GAUGE ,description: "Database backend process count by backend_type" }


#==============================================================#
# 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: 090400
  tags: [ cluster ]
  metrics:
    - xmin: { usage: COUNTER ,description: "Earliest txid that is still active" }
    - xmax: { usage: COUNTER ,description: "First as-yet-unassigned txid. txid >= this are invisible." }
    - 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: 090400
  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_17:
  name: pg_query
  desc: PostgreSQL Query metrics, require pg_stat_statements installed, 17+
  query: |-
    SELECT datname, queryid AS query, sum(calls) AS calls, sum(rows) AS rows, sum(total_exec_time) AS exec_time, sum(shared_blk_read_time) + sum(shared_blk_write_time) AS io_time, sum(wal_bytes) AS wal_bytes
    ,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: 170000
  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" }
    - wal_bytes:    { usage: COUNTER ,description: "Total amount of WAL bytes generated by the statement" }
    - 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_13:
  name: pg_query
  desc: PostgreSQL Query metrics, require pg_stat_statements installed, 13 - 16
  query: |-
    SELECT datname, queryid AS query, sum(calls) AS calls, sum(rows) AS rows, sum(total_exec_time) AS exec_time, sum(blk_read_time) + sum(blk_write_time) AS io_time, sum(wal_bytes) AS wal_bytes
    ,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: 130000
  max_version: 170000
  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" }
    - wal_bytes:    { usage: COUNTER ,description: "Total amount of WAL bytes generated by the statement" }
    - 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_10:
  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: 090400
  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" }


#==============================================================#
# 0510 pg_vacuuming
#==============================================================#
pg_vacuuming_18:
  name: pg_vacuuming
  desc: PostgreSQL vacuum progress 18+
  query: |-
    SELECT datname, pid, relid::RegClass AS relname,
      CASE phase WHEN 'scanning heap' THEN (CASE WHEN heap_blks_total > 0 THEN 1.0 * heap_blks_scanned / heap_blks_total ELSE 0.0 END)
        WHEN 'vacuuming heap' THEN (CASE WHEN heap_blks_total > 0 THEN 1.0 * heap_blks_vacuumed / heap_blks_total ELSE 0 END) ELSE NULL END AS progress,
      indexes_total, indexes_processed, dead_tuple_bytes, delay_time
    FROM pg_stat_progress_vacuum;

  ttl: 10
  min_version: 180000
  tags: [ cluster, primary ]
  metrics:
    - datname:           { usage: LABEL   ,description: "database name" }
    - pid:               { usage: LABEL   ,description: "process id of vacuum worker" }
    - relname:           { usage: LABEL   ,description: "relation name of vacuuming table" }
    - progress:          { usage: GAUGE   ,description: "vacuum progress ratio (0-1) based on heap blocks scanned/vacuumed" }
    - indexes_total:     { usage: GAUGE   ,description: "total number of indexes that will be vacuumed or cleaned up" }
    - indexes_processed: { usage: GAUGE   ,description: "number of indexes that have been vacuumed or cleaned up" }
    - dead_tuple_bytes:  { usage: GAUGE   ,description: "total size of dead tuples collected since the beginning of vacuum in bytes" }
    - delay_time:        { usage: COUNTER ,scale: 1e-3 ,description: "total time spent sleeping due to cost-based delay in seconds" }

pg_vacuuming_17:
  name: pg_vacuuming
  desc: PostgreSQL vacuum progress 17 (with index progress tracking)
  query: |-
    SELECT datname, pid, relid::RegClass AS relname,
      CASE phase WHEN 'scanning heap' THEN (CASE WHEN heap_blks_total > 0 THEN 1.0 * heap_blks_scanned / heap_blks_total ELSE 0.0 END)
        WHEN 'vacuuming heap' THEN (CASE WHEN heap_blks_total > 0 THEN 1.0 * heap_blks_vacuumed / heap_blks_total ELSE 0 END) ELSE NULL END AS progress,
      indexes_total, indexes_processed, dead_tuple_bytes
    FROM pg_stat_progress_vacuum;

  ttl: 10
  min_version: 170000
  max_version: 180000
  tags: [ cluster, primary ]
  metrics:
    - datname:           { usage: LABEL ,description: "database name" }
    - pid:               { usage: LABEL ,description: "process id of vacuum worker" }
    - relname:           { usage: LABEL ,description: "relation name of vacuuming table" }
    - progress:          { usage: GAUGE ,description: "vacuum progress ratio (0-1) based on heap blocks scanned/vacuumed" }
    - indexes_total:     { usage: GAUGE ,description: "total number of indexes that will be vacuumed or cleaned up" }
    - indexes_processed: { usage: GAUGE ,description: "number of indexes that have been vacuumed or cleaned up" }
    - dead_tuple_bytes:  { usage: GAUGE ,description: "total size of dead tuples collected since the beginning of vacuum in bytes" }

pg_vacuuming_12:
  name: pg_vacuuming
  desc: PostgreSQL vacuum progress 12-16
  query: |-
    SELECT datname, pid, relid::RegClass AS relname,
      CASE phase WHEN 'scanning heap' THEN (CASE WHEN heap_blks_total > 0 THEN 1.0 * heap_blks_scanned / heap_blks_total ELSE 0.0 END)
        WHEN 'vacuuming heap' THEN (CASE WHEN heap_blks_total > 0 THEN 1.0 * heap_blks_vacuumed / heap_blks_total ELSE 0 END) ELSE NULL END AS progress
    FROM pg_stat_progress_vacuum;

  ttl: 10
  min_version: 120000
  max_version: 170000
  tags: [ cluster, primary ]
  metrics:
    - datname:   { usage: LABEL ,description: "database name" }
    - pid:       { usage: LABEL ,description: "process id of vacuum worker" }
    - relname:   { usage: LABEL ,description: "relation name of vacuuming table" }
    - progress:  { usage: GAUGE ,description: "vacuum progress ratio (0-1) based on heap blocks scanned/vacuumed" }


#==============================================================#
# 0520 pg_indexing
#==============================================================#
pg_indexing:
  name: pg_indexing
  desc: PostgreSQL index creating progress (v12+)
  query: |-
    SELECT datname, pid, relid::RegClass AS relname,
      (CASE WHEN blocks_total > 0 THEN 1.0 * blocks_done / blocks_total ELSE NULL END) AS blocks,
      (CASE WHEN tuples_total > 0 THEN 1.0 * tuples_done / tuples_total ELSE NULL END) AS tuples,
      (CASE WHEN partitions_total > 0 THEN 1.0 * partitions_done / partitions_total ELSE NULL END) AS partitions,
      (CASE WHEN lockers_total > 0 THEN 1.0 * lockers_done / lockers_total ELSE NULL END) AS lockers
    FROM pg_stat_progress_create_index pspci;

  ttl: 10
  min_version: 120000
  tags: [ cluster, primary ]
  metrics:
    - datname:     { usage: LABEL ,description: "Name of the database" }
    - pid:         { usage: LABEL ,description: "Process id of indexing table" }
    - relname:     { usage: LABEL ,description: "Relation name of indexed table" }
    - blocks:      { usage: GAUGE ,description: "Percent of blocks been proceeded" }
    - tuples:      { usage: GAUGE ,description: "Percent of tuples been proceeded" }
    - partitions:  { usage: GAUGE ,description: "Percent of partitions been proceeded" }
    - lockers:     { usage: GAUGE ,description: "Percent of lockers been proceeded" }


#==============================================================#
# 0530 pg_clustering
#==============================================================#
pg_clustering:
  name: pg_clustering
  desc: PostgreSQL cluster or vacuum full progress (v12+)
  query: SELECT datname, pid, relid::RegClass AS relname, param4 AS tup_scan, CASE WHEN param6 > 0 THEN 1.0 * param7 / param6 ELSE 0 END AS progress FROM pg_stat_get_progress_info('cluster') s LEFT JOIN pg_database d ON s.datid = d.oid;
  ttl: 10
  min_version: 120000
  tags: [ cluster, primary ]
  metrics:
    - datname:     { usage: LABEL ,description: "Name of database been clustering" }
    - pid:         { usage: LABEL ,description: "Process id of indexing table" }
    - relname:     { usage: LABEL ,description: "Relation name of indexed table" }
    - tup_scan:    { usage: GAUGE ,description: "How much tuple been scanned" }
    - progress:    { usage: GAUGE ,description: "Progress of heap been processed" }


#==============================================================#
# 0540 pg_backup
#==============================================================#
pg_backup:
  name: pg_backup
  desc: PostgreSQL basebackup progress since 13
  query: SELECT pid, param1 AS phase, CASE param2 WHEN -1::integer THEN NULL::bigint ELSE param2 END AS total_bytes, param3 AS sent_bytes FROM pg_stat_get_progress_info('BASEBACKUP');
  ttl: 10
  min_version: 130000
  tags: [ cluster ]
  metrics:
    - pid:           { usage: LABEL ,description: "process id of basebackup sender" }
    - phase:         { usage: GAUGE ,description: "Phase encoded in 0~5 initial, wait checkpoint, estimate, streaming, waiting archive, transfer archive" }
    - total_bytes:   { usage: GAUGE ,description: "Total amount of data that will be streamed" }
    - sent_bytes:    { usage: GAUGE ,description: "Amount of data streamed" }


#==============================================================#
# 0610 pg_db
#==============================================================#
pg_db_18:
  name: pg_db
  desc: PostgreSQL database stats from pg_stat_database v18
  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,coalesce(checksum_failures, -1) AS cks_fails, checksum_last_failure AS cks_fail_time,blk_read_time,blk_write_time,
      session_time,active_time,idle_in_transaction_time AS ixact_time,sessions,sessions_abandoned,sessions_fatal,sessions_killed,parallel_workers_to_launch,parallel_workers_launched,
      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: 180000
  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" }
    - cks_fails:           { usage: COUNTER ,description: "Number of data page checksum failures detected in this database, -1 for not enabled" }
    - cks_fail_time:       { usage: GAUGE   ,description: "Time at which the last data page checksum failure was 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" }
    - session_time:        { usage: COUNTER ,scale: 1e-3 ,description: "Time spent by database sessions in this database, in seconds" }
    - active_time:         { usage: COUNTER ,scale: 1e-3 ,description: "Time spent executing SQL statements in this database, in seconds" }
    - ixact_time:          { usage: COUNTER ,scale: 1e-3 ,description: "Time spent idling while in a transaction in this database, in seconds" }
    - sessions:            { usage: COUNTER ,description: "Total number of sessions established to this database" }
    - sessions_abandoned:  { usage: COUNTER ,description: "Number of database sessions to this database that were terminated because connection to the client was lost" }
    - sessions_fatal:      { usage: COUNTER ,description: "Number of database sessions to this database that were terminated by fatal errors" }
    - sessions_killed:     { usage: COUNTER ,description: "Number of database sessions to this database that were terminated by operator intervention" }
    - parallel_workers_to_launch: { usage: COUNTER ,description: "Number of parallel workers planned to be launched by queries on this database" }
    - parallel_workers_launched:  { usage: COUNTER ,description: "Number of parallel workers launched by queries on this database" }
    - reset_time:          { usage: GAUGE   ,description: "Time at which database statistics were last reset" }

pg_db_14:
  name: pg_db
  desc: PostgreSQL database stats from pg_stat_database v14 (with 7 new time & session metrics)
  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,coalesce(checksum_failures, -1) AS cks_fails, checksum_last_failure AS cks_fail_time,blk_read_time,blk_write_time,
      session_time,active_time,idle_in_transaction_time AS ixact_time,sessions,sessions_abandoned,sessions_fatal,sessions_killed,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: 140000
  max_version: 180000
  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" }
    - cks_fails:           { usage: COUNTER ,description: "Number of data page checksum failures detected in this database, -1 for not enabled" }
    - cks_fail_time:       { usage: GAUGE   ,description: "Time at which the last data page checksum failure was 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" }
    - session_time:        { usage: COUNTER ,scale: 1e-3 ,description: "Time spent by database sessions in this database, in seconds" }
    - active_time:         { usage: COUNTER ,scale: 1e-3 ,description: "Time spent executing SQL statements in this database, in seconds" }
    - ixact_time:          { usage: COUNTER ,scale: 1e-3 ,description: "Time spent idling while in a transaction in this database, in seconds" }
    - sessions:            { usage: COUNTER ,description: "Total number of sessions established to this database" }
    - sessions_abandoned:  { usage: COUNTER ,description: "Number of database sessions to this database that were terminated because connection to the client was lost" }
    - sessions_fatal:      { usage: COUNTER ,description: "Number of database sessions to this database that were terminated by fatal errors" }
    - sessions_killed:     { usage: COUNTER ,description: "Number of database sessions to this database that were terminated by operator intervention" }
    - reset_time:          { usage: GAUGE   ,description: "Time at which database statistics were last reset" }


pg_db_12:
  name: pg_db
  desc: PostgreSQL database stats from pg_stat_database v12 v13 (with 2 new checksum metrics)
  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,coalesce(checksum_failures, -1) AS cks_fails, checksum_last_failure AS cks_fail_time,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: 120000
  max_version: 140000
  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" }
    - cks_fails:           { usage: COUNTER ,description: "Number of data page checksum failures detected in this database, -1 for not enabled" }
    - cks_fail_time:       { usage: GAUGE   ,description: "Time at which the last data page checksum failure was 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_10:
  name: pg_db
  desc: PostgreSQL database stats from pg_stat_database v10 v11 (actually since 9.2)
  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: 090200
  max_version: 120000
  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" }


#==============================================================#
# 0620 pg_db_confl
#==============================================================#
# https://pgpedia.info/p/pg_stat_database_conflicts.html

pg_db_confl_16:
  name: pg_db_confl
  desc: PostgreSQL database conflicts metrics for PG16+
  query: SELECT * FROM pg_stat_database_conflicts;
  ttl: 10
  min_version: 160000
  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" }
    - confl_active_logicalslot: { usage: COUNTER ,description: "Number of uses of logical slots in this database that have been canceled due to old snapshots or too low a wal_level on the primary" }

pg_db_confl_15:
  name: pg_db_confl
  desc: PostgreSQL database conflicts metrics for pg 9.1 - 15
  query: SELECT * FROM pg_stat_database_conflicts;
  ttl: 10
  min_version: 90100
  max_version: 160000
  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" }


#==============================================================#
# 0640 pg_pubrel
#==============================================================#
pg_pubrel:
  name: pg_pubrel
  desc: PostgreSQL publication and relation count
  query: SELECT CURRENT_CATALOG AS datname, pubname, count(*) AS count FROM pg_publication p, LATERAL pg_get_publication_tables(pubname) GROUP BY pubname;
  ttl: 10
  min_version: 100000
  metrics:
    - datname:              { usage: LABEL  ,description: "Name of the database which publication belonged" }
    - pubname:              { usage: LABEL  ,description: "Name of the publication" }
    - count:                { usage: GAUGE  ,description: "Count of relation in the publication" }


#==============================================================#
# 0650 pg_subrel
#==============================================================#
pg_subrel:
  name: pg_subrel
  desc: PostgreSQL subscripted relation group by state
  query: SELECT CURRENT_CATALOG AS datname, subname, srsubstate::TEXT AS state, count(*) AS count FROM pg_subscription_rel sr LEFT JOIN pg_stat_subscription  ss ON sr.srsubid = ss.subid GROUP BY 2, 3;
  ttl: 10
  min_version: 100000
  metrics:
    - datname:              { usage: LABEL  ,description: "Name of the database which publication belonged" }
    - subname:              { usage: LABEL  ,description: "Name of the subscription" }
    - state:                { usage: LABEL  ,description: "State of table in subscription, i=initialize, d=data copy, s=sync, r=ready" }
    - count:                { usage: GAUGE  ,description: "Count of relation in this subscription and corresponding state" }


#==============================================================#
# 0700 pg_table
#==============================================================#
pg_table_18:
  name: pg_table
  desc: PostgreSQL table metrics v18+
  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_tup_newpage_upd,psut.n_live_tup,psut.n_dead_tup,
       psut.n_mod_since_analyze,psut.n_ins_since_vacuum,psut.last_vacuum,psut.last_autovacuum,psut.last_analyze,psut.last_autoanalyze,psut.last_seq_scan,
       psut.vacuum_count,psut.autovacuum_count,psut.analyze_count,psut.autoanalyze_count,
       psut.total_vacuum_time AS vacuum_time,psut.total_autovacuum_time AS autovacuum_time,psut.total_analyze_time AS analyze_time,psut.total_autoanalyze_time AS autoanalyze_time,
       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: 180000
  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,p/partitioned/112" }
    - 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_tup_newpage_upd:   { usage: COUNTER  ,default: 0  ,description: "Number of rows updated where the successor version goes onto a new heap page" }
    - 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_ins_since_vacuum:  { usage: GAUGE                 ,description: "Estimated number of rows inserted since this table was last vacuumed" }
    - 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" }
    - last_seq_scan:       { usage: DISCARD               ,description: "The timestamp of the last seq scan on this table" }
    - 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" }
    - vacuum_time:        { usage: COUNTER   ,default: 0  ,scale: 1e-3 ,description: "Total time this table has been manually vacuumed, in seconds" }
    - autovacuum_time:    { usage: COUNTER   ,default: 0  ,scale: 1e-3 ,description: "Total time this table has been vacuumed by the autovacuum daemon, in seconds" }
    - analyze_time:       { usage: COUNTER   ,default: 0  ,scale: 1e-3 ,description: "Total time this table has been manually analyzed, in seconds" }
    - autoanalyze_time:   { usage: COUNTER   ,default: 0  ,scale: 1e-3 ,description: "Total time this table has been analyzed by the autovacuum daemon, in seconds" }
    - 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_16:
  name: pg_table
  desc: PostgreSQL table metrics 16-17
  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_tup_newpage_upd,psut.n_live_tup,psut.n_dead_tup,
       psut.n_mod_since_analyze,psut.n_ins_since_vacuum,psut.last_vacuum,psut.last_autovacuum,psut.last_analyze,psut.last_autoanalyze,psut.last_seq_scan,
       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: 160000
  max_version: 180000
  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,p/partitioned/112" }
    - 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_tup_newpage_upd:   { usage: COUNTER  ,default: 0  ,description: "Number of rows updated where the successor version goes onto a new heap page" }
    - 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_ins_since_vacuum:  { usage: GAUGE                 ,description: "Estimated number of rows inserted since this table was last vacuumed" }
    - 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" }
    - last_seq_scan:       { usage: DISCARD               ,description: "The timestamp of the last seq scan on this table" }
    - 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_13:
  name: pg_table
  desc: PostgreSQL table metrics 13-15 (with n_ins_since_vacuum)
  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.n_ins_since_vacuum,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: 130000
  max_version: 160000
  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,p/partitioned/112" }
    - 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_ins_since_vacuum:  { usage: GAUGE                 ,description: "Estimated number of rows inserted since this table was last vacuumed" }
    - 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_10:
  name: pg_table
  desc: PostgreSQL table metrics 9.4-12
  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: 090400
  max_version: 130000
  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,p/partitioned/112" }
    - 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)" }


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

  ttl: 10
  timeout: 1
  min_version: 090400
  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: 090400
  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" }


#==============================================================#
# 0730 pg_seq
#==============================================================#
pg_seq:
  desc: PostgreSQL sequence metrics
  query: SELECT CURRENT_CATALOG AS datname, schemaname || '.' || sequencename AS seqname, last_value, blks_read, blks_hit FROM pg_sequences s, LATERAL (SELECT relid, blks_read, blks_hit FROM pg_statio_all_sequences sio WHERE s.schemaname = sio.schemaname AND s.sequencename = sio.relname LIMIT 1) d LIMIT 128;
  ttl: 10
  min_version: 100000
  metrics:
    - datname:             { usage: LABEL    ,description: "Database name of this sequence" }
    - seqname:             { usage: LABEL    ,description: "Fully schema qualified sequence name" }
    - last_value:          { usage: COUNTER  ,description: "The last sequence value written to disk" }
    - blks_read:           { usage: COUNTER  ,description: "Number of disk blocks read from this sequence" }
    - blks_hit:            { usage: COUNTER  ,description: "Number of buffer hits in this sequence" }


#==============================================================#
# 0740 pg_relkind
#==============================================================#
pg_relkind:
  name: pg_relkind
  desc: Postgres relation count by kind (category, r,i,m,t,...)
  query: SELECT CURRENT_CATALOG AS datname, relkind, count(*) AS count FROM pg_class GROUP BY relkind;
  ttl: 60
  timeout: 1
  min_version: 090400
  metrics:
    - datname:             { usage: LABEL    ,description: "Name of database" }
    - relkind:             { usage: LABEL    ,description: "Kind of this relation, could be r,i,S,t,v,m,c,f,p,I" }
    - count:               { usage: GAUGE    ,description: "Number of relations of corresponding relkind" }


#==============================================================#
# 0750 pg_defpart
#==============================================================#
pg_defpart:
  name: pg_defpart
  desc: PostgreSQL default partition tuples
  query: SELECT CURRENT_CATALOG AS datname, relnamespace::RegNamespace || '.' || relname AS relname, reltuples AS tuples FROM pg_class WHERE relpartbound IS NOT NULL AND pg_catalog.pg_get_expr(relpartbound, oid) = 'DEFAULT' ORDER BY reltuples DESC LIMIT 64;
  ttl: 60
  timeout: 1
  min_version: 110000
  metrics:
    - datname:             { usage: LABEL    ,description: "Database name of this default partition" }
    - relname:             { usage: LABEL    ,description: "Schema qualified default partition relation name" }
    - tuples:              { usage: GAUGE    ,description: "Number of tuples in this default partition" }


#==============================================================#
# 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: 100000
  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: 090400
  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 (btree only), require pg_index_bloat
  query: SELECT datname, nspname || '.' || relname AS relname, size, ratio FROM pg_index_bloat ORDER BY size DESC LIMIT 64;
  ttl: 300
  timeout: 2
  min_version: 090400
  skip: true
  metrics:
    - datname:             { usage: LABEL    ,description: "Database name of this index" }
    - relname:             { usage: LABEL    ,description: "Schema qualified index name" }
    - size:                { usage: GAUGE    ,description: "Total bytes of this index" }
    - ratio:               { usage: GAUGE    ,description: "Estimated bloat ratio of this index, 0~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" }


#==============================================================#
# 1000 pg_wait_event
#==============================================================#
pg_wait_event:
  name: pg_wait_event
  desc: PostgreSQL wait event sampling based on pg_wait_sampling extension
  query: SELECT coalesce(event_type, 'Running') AS etype, coalesce(event, 'Running') AS event, sum(count) AS count FROM pg_wait_sampling_profile GROUP BY 1,2;
  ttl: 10
  min_version: 100000
  tags: [ cluster, "extension:pg_wait_sampling" ]
  metrics:
    - etype: { usage: "LABEL"   ,description: "wait event type" }
    - event: { usage: "LABEL"   ,description: "wait event name" }
    - count: { usage: "COUNTER" ,description: "Total count of wait events sampled" }

pg_wait_event_1s:
  name: pg_wait_event_1s
  desc: PostgreSQL wait event sampling based on pg_wait_sampling extension
  query: SELECT coalesce(event_type, 'Running') AS etype, coalesce(event, 'Running') AS event, count(*) FROM pg_wait_sampling_history WHERE ts BETWEEN now() - '1s'::INTERVAL AND now() GROUP BY 1,2;
  ttl: 10
  min_version: 100000
  tags: [ cluster, "extension:pg_wait_sampling" ]
  metrics:
    - etype: { usage: "LABEL"   ,description: "wait event type" }
    - event: { usage: "LABEL"   ,description: "wait event name" }
    - count: { usage: "GAUGE"   ,description: "Number of wait events in last second" }


#==============================================================#
# 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: 100000
  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: 100000
  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: 090100
  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" }