pgexporter Metrics
The main task of pgexporter is to gather metrics from your database and format it according to Prometheus metrics standards. pgexporter provides some pre-defined metrics that are necessary/useful, and also provides the capability of providing custom metrics to the user.
Internal Metrics
pgexporter defines a lot of metrics out of the box. By default, all of them are enabled, and will be reflected in the output. However, if you need only specific metrics, you can enable only them as shown here (and thus, disable other metrics).
Listing them (for a wider view, visit here):
| Category | Name | Support | Type | Labels | Collector | Sort | Server | Description |
| General | pgexporter_state | v10-16 | counter | N/A | N/A | N/A | State of pgexporter. | |
| pgexporter_version | counter | pgexporter_version | N/A | N/A | N/A | Version of pgexporter. | ||
| pgexporter_postgresql_active | gauge | server | N/A | name | both | Status of PostgreSQL servers. | ||
| pgexporter_postgresql_version | counter | server, version | N/A | name | both | PostgreSQL Versions on servers. | ||
| pgexporter_uptime | counter | server | N/A | name | both | The PostgreSQL uptime in seconds. | ||
| pgexporter_postgresql_primary | gauge | server | primary | name | both | Is the PostgreSQL instance the primary | ||
| pg_settings | pgexporter_pg_settings_name_of_setting | v10-16 | gauge | server | settings | name | both | Settings. |
| pg_database | pgexporter_pg_database_size | v10-16 | gauge | server, database | db | data | both | Size of the database |
| pg_locks | pgexporter_pg_locks_count | v10-16 | gauge | server, database, mode | locks | data | both | Lock count of a database |
| pg_replication_slots | pgexporter_pg_replication_slots_active | v10-16 | gauge | server, slot_name, slot_type, database | replication | data | both | Is the replication active |
| pgexporter_pg_replication_slots_temporary | gauge | Is the replication temporary | ||||||
| pg_stat_bgwriter | pgexporter_pg_stat_bgwriter_buffers_alloc | v10-16 | gauge | server, database, mode | stat_bgwriter | name | both | buffers_alloc |
| pgexporter_pg_stat_bgwriter_buffers_backend | gauge | buffers_backend | ||||||
| pgexporter_pg_stat_bgwriter_buffers_backend_fsync | gauge | buffers_backend_fsync | ||||||
| pgexporter_pg_stat_bgwriter_buffers_checkpoint | gauge | buffers_checkpoint | ||||||
| pgexporter_pg_stat_bgwriter_buffers_clean | gauge | buffers_clean | ||||||
| pgexporter_pg_stat_bgwriter_checkpoint_sync_time | counter | checkpoint_sync_time | ||||||
| pgexporter_pg_stat_bgwriter_checkpoint_write_time | counter | checkpoint_write_time | ||||||
| pgexporter_pg_stat_bgwriter_checkpoints_req | counter | checkpoints_req | ||||||
| pgexporter_pg_stat_bgwriter_checkpoints_timed | counter | checkpoints_timed | ||||||
| pgexporter_pg_stat_bgwriter_maxwritten_clean | counter | maxwritten_clean | ||||||
| pg_process_idle_seconds | pgexporter_pg_process_idle_seconds | v10-16 | histogram | server, application_name | idle_procs | name | both | Histogram of idle processes |
| pg_available_extensions | pgexporter_pg_available_extensions | v10-16 | gauge | server | available_extensions | name | both | Number of available extensions for installation. |
| pg_extension | pgexporter_pg_installed_extensions | v10-16 | gauge | server, extensions | installed_extensions | name | both | Number of installed extensions. |
| pg_file_settings | pgexporter_pg_file_settings | v10-16 | gauge | server, sourcefile, applied | file_settings | data | both | Settings that are applied. |
| pg_indexes | pgexporter_pg_indexes | v10-16 | gauge | server, schemaname, tablename | indexes | data | both | Indexes for each schemaname for each tablename. |
| pg_matviews | pgexporter_pg_matviews | v10-16 | gauge | server | matviews | name | both | Number of applied Materialized views. |
| pg_rules | pgexporter_pg_rules | v10-16 | gauge | server, tablename | rules | data | both | Number of rules in table. |
| pg_shadow | pgexporter_pg_shadow | v10-16 | gauge | server, tablename | auth_type | data | both | Number of users with authentication type. |
| pg_usr_evt_trigger | pgexporter_pg_usr_evt_trigger | v10-16 | gauge | server | usr_evt_trigger | name | both | Number of user defined event triggers. |
| pg_db_conn | pgexporter_pg_db_conn | v10-16 | gauge | server, database | connections | name | both | Number of database connections. |
| pgexporter_pg_db_conn_ssl | gauge | server, database | data | both | Number of DB connections with SSL. | |||
| pg_statio_all_tables | pgexporter_pg_statio_all_tables_heap_blks_read | v10-16 | counter | server | statio_all_tables | name | both | Number of disk blocks read in postgres db. |
| pgexporter_pg_statio_all_tables_heap_blks_hit | counter | Number of buffer hits read in postgres db. | ||||||
| pgexporter_pg_statio_all_tables_idx_blks_read | counter | Number of disk blocks reads from all indexes in postgres db. | ||||||
| pgexporter_pg_statio_all_tables_idx_blks_hit | counter | Number of buffer hits read from all indexes in postgres db. | ||||||
| pgexporter_pg_statio_all_tables_toast_blks_read | counter | Number of disk blocks reads from postgres db's TOAST tables. | ||||||
| pgexporter_pg_statio_all_tables_toast_blks_hit | counter | Number of buffer hits read from postgres db's TOAST tables. | ||||||
| pgexporter_pg_statio_all_tables_tidx_blks_read | counter | Number of disk blocks reads from postgres db's TOAST table indexes. | ||||||
| pgexporter_pg_statio_all_tables_tidx_blks_hit | counter | Number of buffer hits read from postgres db's TOAST table indexes. | ||||||
| pg_statio_all_sequences | pgexporter_pg_statio_all_sequences_blks_read | v10-16 | counter | server | statio_all_sequences | name | both | Number of disk blocks read from sequences in postgres db. |
| pgexporter_pg_statio_all_sequences_blks_hit | counter | Number of buffer hits read from sequences in postgres db. | ||||||
| pg_stat_user_functions | pgexporter_pg_stat_user_functions_calls | v10-16 | counter | server, funcname | stat_user_functions | data | both | Number of times function is called. |
| pgexporter_pg_stat_user_functions_self_time | counter | Total time spent in milliseconds on the function itself. | ||||||
| pgexporter_pg_stat_user_functions_total_time | counter | Total time spent in milliseconds by the function and any other functions called by it. | ||||||
| pg_stat_statements_calls | pgexporter_pg_stat_statements_calls | v10-16 | counter | server, query | stat_statements_calls | data | both | Number of times the SQL query is executed. |
| pg_stat_statements_rows | pgexporter_pg_stat_statements_rows | v10-16 | counter | server, query | stat_statements_rows | data | both | Number of rows the SQL query affects. |
| pg_stat_replication | pgexporter_pg_stat_replication | v10-16 | gauge | server, application_name | stat_replication | data | both | Number of streaming WAL connections per application. |
| pg_stat_archiver | pgexporter_pg_stat_archiver_archived_count | v10-16 | counter | server | stat_archiver | data | both | Number of successful archived WAL files. |
| pgexporter_pg_stat_archiver_success_time_elapsed_ms | counter | Milliseconds since successful archived WAL file. | ||||||
| pgexporter_pg_stat_archiver_archived_count | counter | Number of failed archival operation on WAL files. | ||||||
| pgexporter_pg_stat_archiver_archived_count | counter | Milliseconds since last failed archival operation on WAL files. | ||||||
| pg_wal_last_received | pgexporter_pg_wal_last_received | v11-16 | counter | server, sender | wal_last_received | name | replica | Time since last message received from WAL sender. |
| pg_gss_auth | pgexporter_pg_gss_auth | v12-16 | gauge | server | gssapi | name | both | Number of GSSAPI authenticated DB connections. |
| pg_encrypted_conn | pgexporter_pg_encrypted_conn | gauge | server | encryted_conns | name | both | Number of encrypted DB connections. | |
| pg_shmem_allocations | pgexporter_pg_shmem_allocations_size | v13-16 | histogram | server | shmem_size | name | both | Histogram of shared memory sizes. |
| pg_stat_statements_total_exec_time | pgexporter_pg_stat_statements_total_exec_time | gauge | server, query | stat_statements_total_exec_time | name | both | Milliseconds taken by the sql query to execute. | |
| pg_stat_statements_plans | pgexporter_pg_stat_statements_plans | counter | server, query | stat_statements_plans | data | both | Number of times the sql query is planned. | |
| pg_stat_statements_wal_bytes | pgexporter_pg_stat_statements_wal_bytes | counter | server, query | stat_statements_wal_bytes | data | both | Bytes occupied in WAL. | |
| pg_mem_ctx | pgexporter_pg_mem_ctx_contexts | v14-16 | gauge | server, parent | mem_ctx | name | both | Number of memory contexts per parent. |
| pgexporter_pg_mem_ctx_free_bytes | gauge | Free bytes per memory context. | ||||||
| pgexporter_pg_mem_ctx_used_bytes | gauge | Used bytes per memory context. | ||||||
| pgexporter_pg_mem_ctx_total_bytes | gauge | Total bytes per memory context. | ||||||
| pg_stat_wal | pgexporter_pg_stat_wal_wal_records | v14-16 | counter | server | stat_wal | name | both | Number of WAL records generated. |
| pgexporter_pg_stat_wal_wal_fpi | counter | Number of WAL full page images generated. | ||||||
| pgexporter_pg_stat_wal_wal_bytes | counter | Total bytes of generated WAL. | ||||||
| pgexporter_pg_stat_wal_wal_buffers_full | counter | Number of disk writes due to WAL buffers being full. | ||||||
| pgexporter_pg_stat_wal_wal_write | counter | Number of times WAL files were written to disk. | ||||||
| pgexporter_pg_stat_wal_wal_sync | counter | Number of times WAL files were synced to disk. | ||||||
| pgexporter_pg_stat_wal_wal_write_time | counter | Time taken for WAL files to be written to disk. | ||||||
| pgexporter_pg_stat_wal_wal_sync_time | counter | Time taken for WAL files to be synced to disk. | ||||||
| pg_stat_database | pgexporter_pg_stat_database_blk_read_time | v10-16 | counter | server, database | stat_db | name | both | pg_stat_database_blk_read_time |
| pgexporter_pg_stat_database_blk_write_time | counter | pg_stat_database_blk_write_time | ||||||
| pgexporter_pg_stat_database_blks_hit | counter | pg_stat_database_blks_hit | ||||||
| pgexporter_pg_stat_database_blks_read | counter | pg_stat_database_blks_read | ||||||
| pgexporter_pg_stat_database_deadlocks | counter | pg_stat_database_deadlocks | ||||||
| pgexporter_pg_stat_database_temp_files | gauge | pg_stat_database_temp_files | ||||||
| pgexporter_pg_stat_database_temp_bytes | gauge | pg_stat_database_temp_bytes | ||||||
| pgexporter_pg_stat_database_tup_returned | counter | pg_stat_database_tup_returned | ||||||
| pgexporter_pg_stat_database_tup_fetched | counter | pg_stat_database_tup_fetched | ||||||
| pgexporter_pg_stat_database_tup_inserted | counter | pg_stat_database_tup_inserted | ||||||
| pgexporter_pg_stat_database_tup_updated | counter | pg_stat_database_tup_updated | ||||||
| pgexporter_pg_stat_database_tup_deleted | counter | pg_stat_database_tup_deleted | ||||||
| pgexporter_pg_stat_database_xact_commit | counter | pg_stat_database_xact_commit | ||||||
| pgexporter_pg_stat_database_xact_rollback | counter | pg_stat_database_xact_rollback | ||||||
| pgexporter_pg_stat_database_conflicts | counter | pg_stat_database_conflicts | ||||||
| pgexporter_pg_stat_database_numbackends | gauge | pg_stat_database_numbackends | ||||||
| pgexporter_pg_stat_database_checksum_failures | v12-16 | gauge | pg_stat_database_checksum_failures | |||||
| pgexporter_pg_stat_database_session_time | v14-16 | gauge | pg_stat_database_session_time | |||||
| pgexporter_pg_stat_database_active_time | gauge | pg_stat_database_active_time | ||||||
| pgexporter_pg_stat_database_idle_in_transaction_time | gauge | pg_stat_database_idle_in_transaction_time | ||||||
| pgexporter_pg_stat_database_sessions | gauge | pg_stat_database_sessions | ||||||
| pgexporter_pg_stat_database_sessions_abandoned | gauge | pg_stat_database_sessions_abandoned | ||||||
| pgexporter_pg_stat_database_sessions_fatal | gauge | pg_stat_database_sessions_fatal | ||||||
| pgexporter_pg_stat_database_sessions_killed | gauge | pg_stat_database_sessions_killed | ||||||
| pg_wal_prefetch_reset | pgexporter_pg_wal_prefetch_reset | v15-16 | counter | server | wal_prefetch_reset | name | both | Seconds from last WAL prefetch stats reset. |
| pg_gssapi_credentials_delegated | pgexporter_pg_gssapi_credentials_delegated | v16 | gauge | server | gssapi_creds_delegated | name | both | Number of DB connections with delegated GSSAPI credentials. |
| pg_stat_io | pgexporter_pg_stat_io_reads | v16 | counter | server, backend_type | stat_io | name | both | Number of read operations. |
| pgexporter_pg_stat_io_read_time | counter | Total time spent on read operations in milliseconds. | ||||||
| pgexporter_pg_stat_io_writes | counter | Number of write operations. | ||||||
| pgexporter_pg_stat_io_write_time | counter | Total time spent on write operations in milliseconds. | ||||||
| pgexporter_pg_stat_io_writebacks | counter | Number of writeback to permanent storage requests sent to kernel. | ||||||
| pgexporter_pg_stat_io_writeback_time | counter | Total time spent on writeback operations in milliseconds. | ||||||
| pgexporter_pg_stat_io_extends | counter | Number of relation extend operations. | ||||||
| pgexporter_pg_stat_io_extend_time | counter | Total time spent on relation extend operations in milliseconds. | ||||||
| pgexporter_pg_stat_io_op_bytes | gauge | Bytes per unit of I/O read, written or extended. | ||||||
| pgexporter_pg_stat_io_hits | counter | The number of times a desired block was found in shared buffer. | ||||||
| pgexporter_pg_stat_io_evictions | counter | The number of times a block has been written out from shared or local buffer in order to make it available for another use. | ||||||
| pgexporter_pg_stat_io_reuses | counter | The number of times an existing buffer in a size-limited ring buffer outside of shared buffers was reused as part of an I/O operation. | ||||||
| pgexporter_pg_stat_io_fsyncs | counter | Number of fsync calls. | ||||||
| pgexporter_pg_stat_io_fsync_time | counter | Total time spent on fsync operations in milliseconds. | ||||||
| pg_stat_database_conflicts | pgexporter_pg_stat_database_conflicts_confl_tablespace | v16 | counter | server, database | stat_conflicts | data | both | pg_stat_database_conflicts_confl_tablespace |
| pgexporter_pg_stat_database_conflicts_confl_lock | counter | pg_stat_database_conflicts_confl_lock | ||||||
| pgexporter_pg_stat_database_conflicts_confl_snapshot | counter | pg_stat_database_conflicts_confl_snapshot | ||||||
| pgexporter_pg_stat_database_conflicts_confl_bufferpin | counter | pg_stat_database_conflicts_confl_bufferpin | ||||||
| pgexporter_pg_stat_database_conflicts_confl_dead | counter | pg_stat_database_conflicts_confl_dead | ||||||
| pgexporter_pg_stat_database_conflicts_confl_active_logicalslot | counter | pg_stat_database_conflicts_confl_active_logicalslot | ||||||
| pg_stat_all_indexes | pgexporter_pg_stat_all_indexes_idx_scans | v10-16 | counter | server, relname | stat_all_indexes | data | both | Number of index scans on the table's indexes. |
| pgexporter_pg_stat_all_indexes_idx_tup_reads | counter | Number of index entries returned by scans on the table's indexes. | ||||||
| pgexporter_pg_stat_all_indexes_idx_tup_fetchs | counter | Number of rows fetched by simple index scans on the table's indexes. | ||||||
| pgexporter_pg_stat_all_indexes_time_elapsed_ms | v16 | counter | Milliseconds since last scan of an index in the table. |
Custom Metrics
Custom metrics are either defined in a single YAML file, or inside multiple YAML files inside a single directory.
NOTE
Examples of custom YAML files can be found here.
The overall schema for the custom metrics are:
YAML = {
version : number,
metrics : Metric[]
};
Metric = {
queries : Query[],
tag : string,
collector : string,
sort : "name" (default) | "data",
server : "both" (default) | "primary" | "replica",
};
Query = {
query : string,
columns : Column[],
version ?: number
};
Column = Label | Counter | Gauge;
Label = {
name: string,
type: "label"
};
Counter = {
name ?: string,
type: "counter",
description: string
};
Gauge = {
name ?: string,
type: "gauge",
description: string
};YAML = {
version : number,
metrics : Metric[]
};
Metric = {
queries : Query[],
tag : string,
collector : string,
sort : "name" (default) | "data",
server : "both" (default) | "primary" | "replica",
};
Query = {
query : string,
columns : Column[],
version ?: number
};
Column = Label | Counter | Gauge;
Label = {
name: string,
type: "label"
};
Counter = {
name ?: string,
type: "counter",
description: string
};
Gauge = {
name ?: string,
type: "gauge",
description: string
};Custom metrics have to be defined in yaml files. There may be a single file or multiple of them (within a single directory).
TIP
Some examples of user-defined metrics can be found here and can be used as a reference while going through this guide.
The structure of these custom yaml files can be approached in a top-down manner as follows.
Top Level
At the top-most level of the yaml file, there are two keys version and metrics:
yml
version: ...
metrics: ...version: ...
metrics: ...versionspecifies a default minimum PostgreSQL support version for the queries (more on this below).metricsis a list of user defined metrics.
Metrics
The metrics key is a list of metrics and has the following structure:
yml
...
metrics:
- queries: ...
tag: ...
collector: ...
sort: ...
server: ...
- queries: ...
tag: ...
collector: ...
sort: ...
server: ...
- queries: ...
tag: ...
collector: ...
sort: ...
server: ...
......
metrics:
- queries: ...
tag: ...
collector: ...
sort: ...
server: ...
- queries: ...
tag: ...
collector: ...
sort: ...
server: ...
- queries: ...
tag: ...
collector: ...
sort: ...
server: ...
...queriesare a list of query alternatives (more on this below).tag: This specifies the tag of the metics (more on this below).collector: This specifies acollectorname for the metric. This allows you to disable a collector by using the-Cflag (see here for details).sort(optional): This specifies how the output of each metric will be sorted. Currently there are two supported values:name(default): This will sort the output according to server's name.data: This will sort the output according to the data of the first column of the SQL query that will be run on the server (more on this below).
server(optional): This specifies on which type of server the metric should query. There are three possible values to this:primary: This means that this metric is only for primary servers.replica: This means that this metric is only for replica servers.both(default): This means that this metric is for both types of servers.
Queries
Each queries key is an object of the following structure:
yml
- queries:
- query: ...
columns: ...
version: ...
- query: ...
columns: ...
version: ...
...- queries:
- query: ...
columns: ...
version: ...
- query: ...
columns: ...
version: ...
...query: Query String for the query alternative (explained below).version(optional): Minimum PostgreSQL version required to run the query (explained below). If this value is not provided, then the default is taken from the top levelversion.columns: List of columns (explained below)
There are multiple queries for each metrics. They exist because not all queries are supported across all versions of PostgreSQL. The solution is to provide a query, as well as the minimum version of PostgreSQL it will run on.
Thus queries contains multiple entries, each containing a query and a minimum PostgreSQL version required to run it.
Query Selection Based On Server Version
Depending on the version of the server, a suitable query is picked. It is picked according to the following rule:
If your server has a version v, then it will select the query with the maximum value of version that it can find which is also less than or equal to v.
For example:
txt
|Server mininum supported PostgreSQL version|Query|
|-------------------------------------------|-----|
| 10 | Q1 |
| 12 | Q2 |
| 14 | Q3 |
| 16 | Q4 |
| 18 | Q5 |
| 20 | Q6 |
| 22 | Q7 ||Server mininum supported PostgreSQL version|Query|
|-------------------------------------------|-----|
| 10 | Q1 |
| 12 | Q2 |
| 14 | Q3 |
| 16 | Q4 |
| 18 | Q5 |
| 20 | Q6 |
| 22 | Q7 |txt
Server is v9: Not Supported (No query is sent)
Server is v10: Q1 sent
Server is v11: Q1 sent
Server is v12: Q2 sent
Server is v13: Q2 sent
Server is v14: Q3 sent
Server is v15: Q3 sent
Server is v16: Q4 sent
Server is v17: Q4 sent
Server is v18: Q5 sent
Server is v19: Q5 sent
Server is v21: Q6 sent
Server is v25: Q7 sentServer is v9: Not Supported (No query is sent)
Server is v10: Q1 sent
Server is v11: Q1 sent
Server is v12: Q2 sent
Server is v13: Q2 sent
Server is v14: Q3 sent
Server is v15: Q3 sent
Server is v16: Q4 sent
Server is v17: Q4 sent
Server is v18: Q5 sent
Server is v19: Q5 sent
Server is v21: Q6 sent
Server is v25: Q7 sentColumns
Labels, Gauges, Counters and Histograms are the types of Prometheus metrics currently supported by pgexporter. Histogram metric type is a bit different from the rest:
Labels, Gauges and Counters
For labels, gauges and counters, columns contains a list of columns, which can be either one of the following:
yml
- name: ...
type: label- name: ...
type: labelor,
yml
- name: ...
type: gauge
description: ...- name: ...
type: gauge
description: ...or,
yml
- name: ...
type: counter
description: ...- name: ...
type: counter
description: ...Gauges or Counters do not strictly need names. It will, by default, take the name from the tag of the metric like pgexporter_tag. However, any names provided will be appended to the tag like: pgexporter_tag_name, and hence names should be provided when dealing with multiple metrics derived from a single query.
NOTE
Names are required for distinction when the same metric has more than one counter/gauge as if not provided, all of these will have the same metric name.
Histograms
How to Use Custom Metrics
Suppose the user defines their metrics in custom.yml, having the path /location/to/custom.yml. To run pgepxorter with the metrics defined in this file:
sh
$ pgexporter -c pgexporter.conf -u pgexporter_users.conf -Y /location/to/custom.yml$ pgexporter -c pgexporter.conf -u pgexporter_users.conf -Y /location/to/custom.ymlor, if all the custom yaml files are kept in a directory having path /location/to/custom/dir, pgexporter is run as:
sh
$ pgexporter -c pgexporter.conf -u pgexporter_users.conf -Y /location/to/custom/dir$ pgexporter -c pgexporter.conf -u pgexporter_users.conf -Y /location/to/custom/dirExamples can be refered from here.