prometheus-community/postgres

README

PostgreSQL Server Exporter

Prometheus exporter for PostgreSQL server metrics.

CI Tested PostgreSQL versions: 13, 14, 15, 16, 17, 18.

See the docs/ directory for user-focused documentation: getting started, Docker images, configuration, database permissions, connecting, and secrets.

Quick Start

This package is available for Docker:

# Start an example database
docker run --net=host -it --rm -e POSTGRES_PASSWORD=password postgres
# Connect to it
docker run \
  --net=host \
  -e DATA_SOURCE_USER=postgres \
  -e DATA_SOURCE_PASS=password \
  quay.io/prometheuscommunity/postgres-exporter \
  --datasource.uri="localhost:5432/postgres?sslmode=disable"

Test with:

curl "http://localhost:9187/metrics"

See Getting Started for the other ways to install it, how to point Prometheus at it, and how to supply the password without putting it in an environment variable.

Multi-Target Support

This exporter supports the multi-target pattern. This allows running a single instance of this exporter for multiple postgres targets. Using the multi-target functionality of this exporter is optional and meant for cases where it is impossible to install the exporter as a sidecar, for example SaaS-managed services.

To use the multi-target functionality, send an http request to the endpoint /probe?target=foo:5432 where target is set to the DSN of the postgres instance to scrape metrics from.

To avoid putting sensitive information like username and password in the URL, preconfigured auth modules are supported via the auth_modules section of the config file. auth_modules for DSNs can be used with the /probe endpoint by specifying the ?auth_module=foo http parameter.

Example Prometheus config:

scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets:
        - server1:5432
        - server2:5432
    metrics_path: /probe
    params:
      auth_module: [foo]
    relabel_configs:
      - source_labels: [__address__]
        target_label: __param_target
      - source_labels: [__param_target]
        target_label: instance
      - target_label: __address__
        replacement: 127.0.0.1:9116  # The postgres exporter's real hostname:port.

Configuration File

The configuration file controls the behavior of the exporter. It can be set using the --config.file command line flag and defaults to postgres_exporter.yml. Set --config.file="" to run without a configuration file.

auth_modules

This section defines preset authentication and connection parameters for use in the multi-target endpoint. auth_modules is a map of modules with the key being the identifier which can be used in the /probe endpoint. Currently only the userpass type is supported.

Example:

auth_modules:
  foo1: # Set this to any name you want
    type: userpass
    userpass:
      username: first
      password: firstpass
    options:
      # options become key=value parameters of the DSN
      sslmode: disable

Building and running

git clone https://github.com/prometheus-community/postgres_exporter.git
cd postgres_exporter
make build
./postgres_exporter <flags>

To build the Docker image:

make promu
promu crossbuild -p linux/amd64 -p linux/armv7 -p linux/arm64 -p linux/ppc64le
make docker

This will build the docker image as prometheuscommunity/postgres_exporter:${branch}.

Flags

help Show context-sensitive help (also try --help-long and --help-man).
[no-]collector.aurora_global_db_instance_status Enable the aurora_global_db_instance_status collector (default: disabled).
[no-]collector.aurora_global_db_status Enable the aurora_global_db_status collector (default: disabled).
[no-]collector.aurora_instance Enable the aurora_instance collector (default: disabled).
[no-]collector.aurora_replica_status Enable the aurora_replica_status collector (default: disabled).
[no-]collector.aurora_stat_bgwriter Enable the aurora_stat_bgwriter collector (default: disabled).
[no-]collector.aurora_stat_database Enable the aurora_stat_database collector (default: disabled).
[no-]collector.aurora_stat_dml_activity Enable the aurora_stat_dml_activity collector (default: disabled).
[no-]collector.aurora_stat_get_db_commit_latency Enable the aurora_stat_get_db_commit_latency collector (default: disabled).
[no-]collector.aurora_stat_logical_wal_cache Enable the aurora_stat_logical_wal_cache collector (default: disabled).
[no-]collector.aurora_stat_optimized_reads_cache Enable the aurora_stat_optimized_reads_cache collector (default: disabled).
[no-]collector.database Enable the database collector (default: enabled).
[no-]collector.database_wraparound Enable the database_wraparound collector (default: disabled).
[no-]collector.locks Enable the locks collector (default: enabled).
[no-]collector.long_running_transactions Enable the long_running_transactions collector (default: disabled).
[no-]collector.postmaster Enable the postmaster collector (default: disabled).
[no-]collector.process_idle Enable the process_idle collector (default: disabled).
[no-]collector.replication Enable the replication collector (default: enabled).
[no-]collector.replication_slots Enable the replication_slots collector (default: enabled).
[no-]collector.settings Enable the settings collector (default: enabled).
[no-]collector.stat_activity Enable the stat_activity collector (default: enabled).
[no-]collector.stat_activity_autovacuum Enable the stat_activity_autovacuum collector (default: disabled).
[no-]collector.stat_archiver Enable the stat_archiver collector (default: enabled).
[no-]collector.stat_bgwriter Enable the stat_bgwriter collector (default: enabled).
[no-]collector.stat_checkpointer Enable the stat_checkpointer collector (default: disabled).
[no-]collector.stat_database Enable the stat_database collector (default: enabled).
[no-]collector.stat_progress_vacuum Enable the stat_progress_vacuum collector (default: enabled).
[no-]collector.stat_replication Enable the stat_replication collector (default: enabled).
[no-]collector.stat_statements Enable the stat_statements collector (default: disabled).
[no-]collector.stat_statements.include_query Enable selecting statement query together with queryId. (default: disabled)
--collector.stat_statements.query_length Maximum length of the statement text. Default is 120.
--collector.stat_statements.limit Maximum number of statements to return. Default is 100.
--collector.stat_statements.exclude_databases Comma-separated list of database names to exclude from pg_stat_statements metrics. Default is none.
--collector.stat_statements.exclude_users Comma-separated list of user names to exclude from pg_stat_statements metrics. Default is none.
[no-]collector.stat_user_tables Enable the stat_user_tables collector (default: enabled).
[no-]collector.stat_wal_receiver Enable the stat_wal_receiver collector (default: disabled).
[no-]collector.statio_user_indexes Enable the statio_user_indexes collector (default: disabled).
[no-]collector.statio_user_tables Enable the statio_user_tables collector (default: enabled).
[no-]collector.wal Enable the wal collector (default: enabled).
[no-]collector.xlog_location Enable the xlog_location collector (default: disabled).
config.file Set the config file path. Default is postgres_exporter.yml
web.systemd-socket Use systemd socket activation listeners instead of port listeners (Linux only). Default is false
web.listen-address Address to listen on for web interface and telemetry. Default is :9187.
web.config.file Configuration file to use TLS and/or basic authentication. The format of the file is described in the exporter-toolkit repository.
web.telemetry-path Path under which to expose metrics. Default is /metrics.
web.max-requests Maximum number of parallel scrape requests per endpoint (/metrics and /probe have independent limits). Excess requests receive HTTP 503. Use 0 to disable. Default is 40.
web.disable-exporter-metrics Exclude Go, process, and postgres_exporter_build_info metrics. PostgreSQL scrape-status and configuration-reload metrics remain available. Default is false.
disable-default-metrics Use only metrics supplied from queries.yaml via --extend.query-path. Default is false.
auto-discover-databases (DEPRECATED) Whether to discover the databases on a server dynamically. Default is false.
auto-discover-databases.max-concurrency Maximum number of discovered databases to scrape concurrently in a single scrape, when auto-discover-databases is enabled. Default is 10.
extend.query-path (DEPRECATED) Path to a YAML file containing custom queries to run. Check out queries.yaml for examples of the format.
dumpmaps Do not run - print the internal representation of the metric maps. Useful when debugging a custom queries file.
[no-]wrap-large-counters Wrap 64-bit counter values at 2^53 to avoid floating point rounding. Default is true.
constantLabels (DEPRECATED) Labels to set in all metrics. A list of label=value pairs, separated by commas.
version Show application version.
exclude-databases (DEPRECATED) A list of databases to remove when autoDiscoverDatabases is enabled.
include-databases (DEPRECATED) A list of databases to only include when autoDiscoverDatabases is enabled.
datasource.uri The hostname, port, database and query string to connect to, without credentials. For example, my_pg_hostname or my_pg_hostname:5432/postgres?sslmode=disable.
datasource.uri-file The same as above but reads the URI from a file.
datasource.user-file Reads the username to connect with from a file.
datasource.pass-file Reads the password to connect with from a file. The flag takes a path, so the password itself never appears on the command line.
log.level Set logging level: one of debug, info, warn, error.
log.format Set the log format: one of logfmt, json.

Environment Variables

The following environment variables configure the exporter. The ones marked DEPRECATED have a --datasource.* flag equivalent, are only used when that flag is unset, and will be removed in v0.22.0. The exporter logs a warning at startup when one of them is set.

DATA_SOURCE_NAME the default legacy format. Accepts URI form and key=value form arguments. The URI may contain the username and password to connect with.
DATA_SOURCE_URI (DEPRECATED, use --datasource.uri) an alternative to DATA_SOURCE_NAME which exclusively accepts the hostname without a username and password component. For example, my_pg_hostname or my_pg_hostname:5432/postgres?sslmode=disable.
DATA_SOURCE_URI_FILE (DEPRECATED, use --datasource.uri-file) The same as above but reads the URI from a file.
DATA_SOURCE_USER When using --datasource.uri, this environment variable is used to specify the username.
DATA_SOURCE_USER_FILE (DEPRECATED, use --datasource.user-file) The same, but reads the username from a file.
DATA_SOURCE_PASS When using --datasource.uri, this environment variable is used to specify the password to connect with.
DATA_SOURCE_PASS_FILE (DEPRECATED, use --datasource.pass-file) The same as above but reads the password from a file.
PG_EXPORTER_COLLECTION_TIMEOUT Timeout duration to use when collecting the statistics, default to 1m. When the timeout is reached, the database connection will be dropped. It avoids connections stacking when the database answers too slowly (for instance if the database creates/drop a huge table and locks the tables) and will avoid exhausting the pool of connections of the database. Value of 0 or less than 1ms is considered invalid and will report an error.
PG_EXPORTER_WEB_TELEMETRY_PATH (DEPRECATED) Path under which to expose metrics when --web.telemetry-path is unset. Default is /metrics. This environment variable will be removed in postgres_exporter v0.22.0. Use --web.telemetry-path instead; an explicit flag takes precedence.
PG_EXPORTER_DISABLE_DEFAULT_METRICS Use only metrics supplied from queries.yaml. Value can be true or false. Default is false.
PG_EXPORTER_AUTO_DISCOVER_DATABASES (DEPRECATED) Whether to discover the databases on a server dynamically. Value can be true or false. Default is false.
PG_EXPORTER_EXTEND_QUERY_PATH Path to a YAML file containing custom queries to run. Check out queries.yaml for examples of the format.
PG_EXPORTER_CONSTANT_LABELS (DEPRECATED) Labels to set in all metrics. A list of label=value pairs, separated by commas.
PG_EXPORTER_EXCLUDE_DATABASES (DEPRECATED) A comma-separated list of databases to remove when autoDiscoverDatabases is enabled. Default is empty string.
PG_EXPORTER_INCLUDE_DATABASES (DEPRECATED) A comma-separated list of databases to only include when autoDiscoverDatabases is enabled. Default is empty string, means allow all.
PG_EXPORTER_METRIC_PREFIX A prefix to use for each of the default metrics exported by postgres-exporter. Default is pg

Settings set by environment variables starting with PG_ will be overwritten by the corresponding CLI flag if given.

Setting the Postgres server's data source name

The PostgreSQL server's data source name must be set via the DATA_SOURCE_NAME environment variable.

For running it locally on a default Debian/Ubuntu install, this will work (transpose to init script as appropriate):

sudo -u postgres DATA_SOURCE_NAME="user=postgres host=/var/run/postgresql/ sslmode=disable" postgres_exporter

Also, you can set a list of sources to scrape different instances from the one exporter setup. Just define a comma separated string.

sudo -u postgres DATA_SOURCE_NAME="port=5432,port=6432" postgres_exporter

See the github.com/lib/pq module for other ways to format the connection string.

Adding new metrics

The exporter will attempt to dynamically export additional metrics if they are added in the future, but they will be marked as "untyped". Additional metric maps can be easily created from Postgres documentation by copying the tables and using the following Python snippet:

x = """tab separated raw text of a documentation table"""
for l in StringIO(x):
    column, ctype, description = l.split('\t')
    print """"{0}" : {{ prometheus.CounterValue, prometheus.NewDesc("pg_stat_database_{0}", "{2}", nil, nil) }}, """.format(column.strip(), ctype, description.strip())

Adjust the value of the resultant prometheus value type appropriately. This helps build rich self-documenting metrics for the exporter.

Adding new metrics via a config file (DEPRECATED)

This feature is deprecated in favor of built-in collector functions. For generic SQL database monitoring see the sql_exporter.

The -extend.query-path command-line argument specifies a YAML file containing additional queries to run. Some examples are provided in queries.yaml.

Disabling default metrics

To work with non-officially-supported postgres versions (e.g. 8.2.15), or variants of postgres (e.g. Greenplum), you can disable the default metrics with the --disable-default-metrics flag. This removes all built-in metrics, and uses only metrics defined by queries in the queries.yaml file you supply (so you must supply one, otherwise the exporter will return nothing but internal statuses and not your database).

Automatically discover databases (DEPRECATED)

To scrape metrics from all databases on a database server, the database DSN's can be dynamically discovered via the --auto-discover-databases flag. When true, SELECT datname FROM pg_database WHERE datallowconn = true AND datistemplate = false and datname != current_database() is run for all configured DSN's. From the result a new set of DSN's is created for which the metrics are scraped.

Every discovered database gets its own connection and gets scraped for the collectors that report on a single database (e.g. stat_user_tables, statio_user_tables, statio_user_indexes). Collectors that report on the whole server (e.g. wal, bgwriter, stat_activity) still only run once, against the first configured DSN, since re-running them against another database on the same server would report the exact same rows again. The set of discovered databases is re-evaluated on every scrape, so databases created or dropped after the exporter starts are picked up without a restart. At most --auto-discover-databases.max-concurrency discovered databases are scraped concurrently, so a server with many databases cannot make a single scrape open more connections than it can spare.

In addition, the option --exclude-databases adds the possibility to filter the result from the auto discovery to discard databases you do not need.

If you want to include only subset of databases, you can use option --include-databases. Exporter still makes request to pg_database table, but do scrape from only if database is in include list.

Running as non-superuser

To be able to collect metrics from pg_stat* views as non-superuser in PostgreSQL server versions >= 10 you can grant the pg_monitor or pg_read_all_stats built-in roles to the user. If you need to monitor older PostgreSQL servers, you will have to create functions and views as a superuser, and assign permissions separately to those.

-- To use IF statements, hence to be able to check if the user exists before
-- attempting creation, we need to switch to procedural SQL (PL/pgSQL)
-- instead of standard SQL.
-- More: https://www.postgresql.org/docs/9.3/plpgsql-overview.html
-- To preserve compatibility with <9.0, DO blocks are not used; instead,
-- a function is created and dropped.
CREATE OR REPLACE FUNCTION __tmp_create_user() returns void as $$
BEGIN
  IF NOT EXISTS (
          SELECT                       -- SELECT list can stay empty for this
          FROM   pg_catalog.pg_user
          WHERE  usename = 'postgres_exporter') THEN
    CREATE USER postgres_exporter;
  END IF;
END;
$$ language plpgsql;

SELECT __tmp_create_user();
DROP FUNCTION __tmp_create_user();

ALTER USER postgres_exporter WITH PASSWORD 'password';
ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog;

-- If deploying as non-superuser (for example in AWS RDS), uncomment the GRANT
-- line below and replace <MASTER_USER> with your root user.
-- GRANT postgres_exporter TO <MASTER_USER>;

GRANT CONNECT ON DATABASE postgres TO postgres_exporter;

Run following command if you use PostgreSQL versions >= 10

GRANT pg_monitor to postgres_exporter;

Run following SQL commands only if you use PostgreSQL versions older than 10. In PostgreSQL, views run with the permissions of the user that created them so they can act as security barriers. Functions need to be created to share this data with the non-superuser. Only creating the views will leave out the most important bits of data.

CREATE SCHEMA IF NOT EXISTS postgres_exporter;
GRANT USAGE ON SCHEMA postgres_exporter TO postgres_exporter;

CREATE OR REPLACE FUNCTION get_pg_stat_activity() RETURNS SETOF pg_stat_activity AS
$$ SELECT * FROM pg_catalog.pg_stat_activity; $$
LANGUAGE sql
VOLATILE
SECURITY DEFINER;

CREATE OR REPLACE VIEW postgres_exporter.pg_stat_activity
AS
  SELECT * from get_pg_stat_activity();

GRANT SELECT ON postgres_exporter.pg_stat_activity TO postgres_exporter;

CREATE OR REPLACE FUNCTION get_pg_stat_replication() RETURNS SETOF pg_stat_replication AS
$$ SELECT * FROM pg_catalog.pg_stat_replication; $$
LANGUAGE sql
VOLATILE
SECURITY DEFINER;

CREATE OR REPLACE VIEW postgres_exporter.pg_stat_replication
AS
  SELECT * FROM get_pg_stat_replication();

GRANT SELECT ON postgres_exporter.pg_stat_replication TO postgres_exporter;

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE OR REPLACE FUNCTION get_pg_stat_statements() RETURNS SETOF pg_stat_statements AS
$$ SELECT * FROM public.pg_stat_statements; $$
LANGUAGE sql
VOLATILE
SECURITY DEFINER;

CREATE OR REPLACE VIEW postgres_exporter.pg_stat_statements
AS
  SELECT * FROM get_pg_stat_statements();

GRANT SELECT ON postgres_exporter.pg_stat_statements TO postgres_exporter;

NOTE
Remember to use postgres database name in the connection string:
DATA_SOURCE_NAME=postgresql://postgres_exporter:password@localhost:5432/postgres?sslmode=disable

Running the tests

# Run the unit tests
make test
# Start the test database with docker
docker run -p 5432:5432 -e POSTGRES_DB=circle_test -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=test -d postgres
# Run the integration tests
DATA_SOURCE_NAME='postgresql://postgres:test@localhost:5432/circle_test?sslmode=disable' GOOPTS='-v -tags integration' make test