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"

  ## Set true to enable election
  #
  election = true

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

  ## 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
  # 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)
  #
  # [[inputs.postgresql.relations]]
  # 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"
  # ...

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¶

postgresql_lock¶

postgresql_index¶

postgresql_replication¶

postgresql_replication_slot¶

postgresql_size¶

postgresql_statio¶

postgresql_stat¶

postgresql_slru¶

postgresql_bgwriter¶

postgresql_connection¶

postgresql_conflict¶

postgresql_archiver¶

collector¶

Object¶

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:

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.