PostgreSQL

·

PostgreSQL collector can collect the running status index from PostgreSQL instance, and collect the index to TrueWatch to help monitor and analyze various abnormal situations of PostgreSQL.

Configuration¶

Preconditions¶

• PostgreSQL version >= 9.0

• Create user

  -- PostgreSQL >= 10
  create user datakit with password '<PASSWORD>';
  grant pg_monitor to datakit;
  grant SELECT ON pg_stat_database to datakit;
  
  -- PostgreSQL < 10
  create user datakit with password '<PASSWORD>';
  grant SELECT ON pg_stat_database to datakit;
  

• Enable the pg_stat_statements Extension (Optional)

  When collecting PostgreSQL Object, some key metrics such as qps/tps/avg_query_time rely on the pg_stat_statements extension. The specific steps to enable this extension are as follows:

  • Modify the Configuration File

    Locate and edit the PostgreSQL configuration file (common paths are /var/lib/pgsql/data/postgresql.conf or /etc/postgresql/<version>/main/postgresql.conf), and add or modify the following configuration items:

    # Enable the pg_stat_statements
    shared_preload_libraries = 'pg_stat_statements'
    
    # Optional configuration
    pg_stat_statements.track = 'all'  # collect all SQL statements
    pg_stat_statements.max = 10000  # max number of SQL statements to track
    pg_stat_statements.track_utility = off # ignore utility statements, only track regular SQL statements like `SELECT`, `INSERT`, `UPDATE`, `DELETE`
    

  • Restart the PostgreSQL Service

    After modifying the configuration file, it is necessary to restart the PostgreSQL service for the configurations to take effect.​

  • Create the Extension

    Connect to the target database and execute the following SQL statement to create the extension:

    CREATE EXTENSION pg_stat_statements;
    

    • Verify the Successful Enablement of the Extension:

    SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';
    SELECT * FROM pg_stat_statements LIMIT 10;
    

Collector Configuration¶

[[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"
  # ...

Database Performance Metrics Collection¶

Version-1.84.0

Database performance metrics are mainly derived from the built-in system views and extension plugins of PostgreSQL, with the most core ones including pg_stat_activity and pg_stat_statements. These tools provide methods to obtain the internal execution status of the database at runtime: pg_stat_activity displays real-time information such as the activity status of current sessions, executed queries, and waiting events; pg_stat_statements records the execution statistics of historical SQL statements, including execution times, time consumption, IO status, etc. Through these views and plugins, DataKit can collect statement metrics, query samples, execution plans, real-time session activities, and the derived session/connection metrics required for PostgreSQL DBM.

To enable this feature, the following steps need to be performed.

• Modify the configuration file to enable monitoring and collection

[[inputs.postgresql]]

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

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

  ## Config dbm sample 
  [inputs.postgresql.dbm_sample]
    enabled = true
    explain_cache_ttl = "10m"
    plan_cache_ttl = "1h"

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

...

When DBM is enabled, DataKit also collects postgresql_dbm_session and postgresql_dbm_connection automatically. postgresql_dbm_session is aggregated from DBM activity rows, while postgresql_dbm_connection is collected through a separate grouped query against pg_stat_activity. No extra switch is required for these two metric sets.

• PostgreSQL Configuration

Modify the configuration file (e.g., postgresql.conf) and configure the relevant parameters:

shared_preload_libraries = 'pg_stat_statements'
track_activity_query_size = 4096 # Required for collection of larger queries.

• Permission Configuration

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;

• Additional permissions and explain-plan helper

  If dbm_sample.enabled = true, create the helper function in every database where execution plans need to be collected:

  CREATE OR REPLACE FUNCTION datakit.explain_statement(
     l_query TEXT,
     OUT explain JSON
  )
  RETURNS SETOF JSON AS
  $$
  DECLARE
    curs REFCURSOR;
    plan JSON;
  BEGIN
    OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query);
    FETCH curs INTO plan;
    CLOSE curs;
    RETURN QUERY SELECT plan;
  END;
  $$
  LANGUAGE 'plpgsql'
  RETURNS NULL ON NULL INPUT
  SECURITY DEFINER;
  

Metric¶

For all of the following data collections, the global election tags will added automatically, we can add extra tags in [inputs.postgresql.tags] if needed:

postgresql_metric¶

Metric set including PostgreSQL database, function, lock, statistics, index, size, relation I/O, replication, replication slot, simple LRU cache, I/O, background writer, connection, conflict, archiver, and DBM (metric/session/connection) statistics, unified in v2

collector¶

Object¶

db_query¶

PostgreSQL DBM query object. Each object represents a unique normalized SQL statement identified by query_signature, which is derived from db, rolname, and query text.

db_exec_plan¶

PostgreSQL database statement sample information, including statement text, execution plans, used for detailed analysis of SQL execution and performance issues.( Version-1.84.0)

database¶

PostgreSQL object metrics( Version-1.76.0)

Structure of the message field¶

The basic structure of the message field is as follows:​

{
  "setting": {
    "DateStyle":"ISO, MDY",
    ...
  },

  "databases": [ # databases information
    {
      "name": "db1",
      "encoding": "utf8",
      "owner": "datakit",
      "schemas": [ # schemas information
        {
          "name": "schema1",
          "owner": "datakit",
          "tables": [ # tables information
            {
              "name": "table1",
              "columns": [], # columns information
              "indexes": [], # indexes information
              "foreign_keys": [], # foreign keys information
            }
            ...
          ]
        },
        ...
      ]
    }
    ...
  ]
}

setting¶

The data in the setting field is sourced from the pg_settings system view and is used to display the configuration parameter information of the current database. For more details, please refer to the PostgreSQL documentation.

databases¶

The databases field stores information about all databases on the PostgreSQL server. The detailed information of each database is shown in the following table:

The schemas field contains information about all the schemas in the database. The information for each schema is as follows:

The tables field contains information about all the tables in the database. The information for each table is as follows:

• tables.columns

The columns field contains information about all the columns in the tables. The information for each column is as follows:

• tables.indexes

The indexes field contains information about all the indexes in the database tables. The information for each index is as follows:

The indexes.columns field, which contains information about the columns included in the index, has the following details for each column:

• tables.foreign_keys

The foreign_keys field contains information about all the foreign_keys in the database tables. The information for each foreign_key is as follows:

Logging¶

postgresql_dbm_activity¶

PostgreSQL database activity information collected from the pg_stat_activity view. Provides detailed information about the current activity of each backend, including query status, execution time, and resource usage.( Version-1.84.0)

File log¶

Log Collection¶

• PostgreSQL logs are output to stderr by default. To open file logs, configure them in postgresql's configuration file /etc/postgresql/<VERSION>/main/postgresql.conf as follows:

logging_collector = on    # Enable log writing to files

log_directory = 'pg_log'  # Set the file storage directory, absolute path or relative path (relative PGDATA)

log_filename = 'pg.log'   # Log file name
log_statement = 'all'     # Record all queries

#log_duration = on
log_line_prefix= '%m [%p] %d [%a] %u [%h] %c ' # 日志行前缀
log_file_mode = 0644

# For Windows
#log_destination = 'eventlog'

For more configuration, please refer to the doc。

• The PostgreSQL collector does not have log collection enabled by default. You can open files in conf.d/db/postgresql.conf and write to the absolute path of the PostgreSQL log file. For example:

[[inputs.postgresql]]

  ...

  [inputs.postgresql.log]
  files = ["/tmp/pgsql/postgresql.log"]

When log collection is turned on, a log with a log source of postgresql is generated by default.

Notices:

• Log collection only supports logs on hosts where DataKit is installed.

Log Pipeline Cut¶

The original log is

2021-05-31 15:23:45.110 CST [74305] test [pgAdmin 4 - DB:postgres] postgres [127.0.0.1] 60b48f01.12241 LOG:  statement:
        SELECT psd.*, 2^31 - age(datfrozenxid) as wraparound, pg_database_size(psd.datname) as pg_database_size
        FROM pg_stat_database psd
        JOIN pg_database pd ON psd.datname = pd.datname
        WHERE psd.datname not ilike 'template%'   AND psd.datname not ilike 'rdsadmin'
        AND psd.datname not ilike 'azure_maintenance'   AND psd.datname not ilike 'postgres'

Description of the cut field:

FAQ¶

Missing metrics¶

For metrics postgresql_lock/postgresql_stat/postgresql_index/postgresql_size/postgresql_statio, the relations field in the configuration file needs to be enabled. If some of these metrics are partially missing, it may be because there is no data for the relevant metrics.