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"

  ## Set true to enable election
  #
  election = true

  ## 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

  ## Config dbm sample 
  [inputs.postgresql.dbm_sample]
    enabled = true  

  ## Config dbm activity
  [inputs.postgresql.dbm_activity]
    enabled = true  

  ## 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

      # Set true to enable auto discovery database
      auto_discovery_database = false

      # 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;