PostgreSQL statement metricset
editPostgreSQL statement metricset
editThis is the statement
metricset of the PostgreSQL module.
This module collects information from the pg_stat_statements
view, that keeps
track of planning and execution statistics of all SQL statements executed by
the server.
pg_stat_statements
is included by an additional module in PostgreSQL. This
module requires additional shared memory, and is disabled by default.
You can enable it by adding this module to the configuration as a shared preloaded library.
shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all
Preloading this library in your server will increase the memory usage of your PostgreSQL server. Use it with care.
Once the server is started with this module, it starts collecting statistics
about all statements executed. To make these statistics available in the
pg_stat_statements
view, the following statement needs to be executed in the
server:
CREATE EXTENSION pg_stat_statements;
You can read more about the available options for this module in the official documentation.
The PostgreSQL module of Filebeat is also able to collect information about statements executed in the server from its logs. You may chose which one is better for your needings. An important difference is that the Metricbeat module collects aggregated information when the statement is executed several times, but cannot know when each statement was executed. This information can be obtained from logs.
For a description of each field in the metricset, see the exported fields section.
Here is an example document generated by this metricset:
{ "@timestamp": "2017-10-12T08:05:34.853Z", "event": { "dataset": "postgresql.statement", "duration": 115000, "module": "postgresql" }, "metricset": { "name": "statement", "period": 10000 }, "postgresql": { "statement": { "database": { "oid": 13395 }, "query": { "calls": 132, "id": -3489238739385425370, "memory": { "local": { "dirtied": 0, "hit": 0, "read": 0, "written": 0 }, "shared": { "dirtied": 0, "hit": 924, "read": 0, "written": 0 }, "temp": { "read": 0, "written": 0 } }, "rows": 396, "text": "SELECT d.datname as \"Name\",\n pg_catalog.pg_get_userbyid(d.datdba) as \"Owner\",\n pg_catalog.pg_encoding_to_char(d.encoding) as \"Encoding\",\n d.datcollate as \"Collate\",\n d.datctype as \"Ctype\",\n pg_catalog.array_to_string(d.datacl, $1) AS \"Access privileges\"\nFROM pg_catalog.pg_database d\nORDER BY 1", "time": { "max": { "ms": 0.325369 }, "mean": { "ms": 0.07867374242424244 }, "min": { "ms": 0.053835 }, "stddev": { "ms": 0.037920252272212004 }, "total": { "ms": 10.384934000000003 } } }, "user": { "id": 10 } } }, "service": { "address": "192.168.128.2:5432", "type": "postgresql" } }