PostgreSQL

[[inputs.postgresql]]
  ## Server address
  # URI format
  # postgres://[datakit[:PASSWORD]]@localhost[/dbname]?sslmode=[disable|verify-ca|verify-full]
  # or simple string
  # host=localhost user=pqgotest password=... sslmode=... dbname=app_production

  address = "postgres://datakit:PASSWORD@localhost/postgres?sslmode=disable"

  ## Ignore databases which are gathered. Do not use with 'databases' option.
  #
  # ignored_databases = ["db1"]

  ## Specify the list of the databases to be gathered. Do not use with the 'ignored_databases' option.
  #
  # databases = ["db1"]

  ## Specify the name used as the "server" tag.
  #
  # outputaddress = "db01"

  ## Collect interval
  # Time unit: "ns", "us", "ms", "s", "m", "h"
  #
  interval = "10s"

  ## @param connect_timeout - duration - optional - default: "10s"
  ## Timeout for connecting to PostgreSQL and executing collector queries.
  # connect_timeout = "10s"

  ## Set true to enable election
  #
  election = true

  ## v2+ overrides metric-category measurement names to "postgresql_metric". Default: v2
  ## Set to "v1" to keep legacy names: postgresql, postgresql_function, postgresql_lock, postgresql_stat,
  ## postgresql_index, postgresql_size, postgresql_statio, postgresql_replication, postgresql_replication_slot,
  ## postgresql_slru, postgresql_io, postgresql_bgwriter, postgresql_connection, postgresql_conflict,
  ## postgresql_archiver, postgresql_dbm_metric, postgresql_dbm_session, postgresql_dbm_connection
  measurement_version = "v2"

  ## Set true to enable collecting function metrics from pg_stat_user_functions.
  #
  collect_function_metrics = false

  ## Metric name in metric_exclude_list will not be collected.
  #
  metric_exclude_list = [""]

  ## Set dbm to true to collect database activity 
  dbm = false

  ## Config dbm metric 
  [inputs.postgresql.dbm_metric]
    enabled = true
    interval = "60s"

  ## Config dbm sample 
  [inputs.postgresql.dbm_sample]
    enabled = true
    ## @param explain_cache_ttl - duration - optional - default: "10m"
    ## TTL for explain-rate cache.
    explain_cache_ttl = "10m"
    ## @param plan_cache_ttl - duration - optional - default: "1h"
    ## Do not re-emit the same execution plan within this window.
    plan_cache_ttl = "1h"

  ## Config dbm activity
  [inputs.postgresql.dbm_activity]
    enabled = true
    interval = "10s"

  ## Config database discovery. 
  # Discovered databases will be used to for database specific metric collection, 
  # such as object collection and function metrics collection.
  #
  [inputs.postgresql.auto_discovery_database]
    # Set true to enable database discovery
    enabled = false

    # Maximum number of databases to discover
    max_databases = 100

    # Regex pattern to include databases
    include = [".*"]

    # Regex pattern to exclude databases. The pattern in "exclude" takes precedence over "include". 
    exclude = ["model", "msdb", "cloudsqladmin", "rdsadmin"]

    #Discovery interval
    interval = "600s"

  ## collect object
  [inputs.postgresql.object]
    # Set true to enable collecting objects
    enabled = true

    # interval to collect postgresql object which will be greater than collection interval
    interval = "600s"

    [inputs.postgresql.object.collect_schemas]
      # Set true to enable collecting schemas
      enabled = true

      # Maximum number of tables to collect
      max_tables = 300

      # Maximum number of databases to collect
      max_database = 100

  ## Relations config
  #
  # The list of relations/tables can be specified to track per-relation metrics. 
  # To collect relation metrics, you need to specify the databases or database_regex field to indicate which databases to collect.
  # relation_name refer to the name of a relation, either relation_name or relation_regex must be set.
  # relation_regex is a regex rule, only takes effect when relation_name is not set.
  # schemas used for filtering, ignore this field when it is empty
  # relkind can be a list of the following options:
  #   r(ordinary table), i(index), S(sequence), t(TOAST table), p(partitioned table),
  #   m(materialized view), c(composite type), f(foreign table)
  # Size metrics are collected only for ordinary tables. Index metrics are collected only for user indexes.
  # Lock metrics are collected for all relation types. The rest of the metrics are collected only for user tables.
  #
  # [[inputs.postgresql.relations]]
  # databases = ["postgres"]
  # database_regex = "<DATABASE_PATTERN>"
  # relation_name = "<TABLE_NAME>"
  # relation_regex = "<TABLE_PATTERN>"
  # schemas = ["public"]
  # relkind = ["r", "p"]

  ## Run a custom SQL query and collect corresponding metrics.
  #
  # [[inputs.postgresql.custom_queries]]
  #   sql = '''
  #     select datname,numbackends,blks_read
  #     from pg_stat_database
  #     limit 10
  #   '''
  #   metric = "postgresql_custom_stat"
  #   tags = ["datname" ]
  #   fields = ["numbackends", "blks_read"]
  #   interval = "10s"

  ## Log collection
  #
  # [inputs.postgresql.log]
  # files = []
  # pipeline = "postgresql.p"

  ## Custom tags
  #
  [inputs.postgresql.tags]
  # some_tag = "some_value"
  # more_tag = "some_other_value"
  # ...

ALTER ROLE datakit INHERIT;

CREATE SCHEMA datakit;
GRANT USAGE ON SCHEMA datakit TO datakit;
GRANT USAGE ON SCHEMA public TO datakit;
GRANT pg_monitor TO datakit;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

CREATE SCHEMA datakit;
GRANT USAGE ON SCHEMA datakit TO datakit;
GRANT USAGE ON SCHEMA public TO datakit;
GRANT pg_monitor TO datakit;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

CREATE SCHEMA datakit;
GRANT USAGE ON SCHEMA datakit TO datakit;
GRANT USAGE ON SCHEMA public TO datakit;
GRANT SELECT ON pg_stat_database TO datakit;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

CREATE OR REPLACE FUNCTION datakit.pg_stat_activity() RETURNS SETOF pg_stat_activity AS
  $$ SELECT * FROM pg_catalog.pg_stat_activity; $$
LANGUAGE sql
SECURITY DEFINER;
CREATE OR REPLACE FUNCTION datakit.pg_stat_statements() RETURNS SETOF pg_stat_statements AS
    $$ SELECT * FROM pg_stat_statements; $$
LANGUAGE sql
SECURITY DEFINER;