Repository files navigation

Prometheus exporter for PostgreSQL server metrics.

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

This package is available for Docker:

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

Test with:

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

Example Prometheus config:

scrape_configs:  -job_name:postgresstatic_configs:      -targets:["127.0.0.1:9187"]# Replace IP with the hostname of the docker container if you're running the container in a separate network

Now use the DATA_SOURCE_PASS_FILE with a mounted file containing the password to prevent having the password in an environment variable.

The container process runs with uid/gid 65534 (important for file permissions).

This Feature is in beta and may require changes in future releases. Feedback is welcome.

This exporter supports themulti-target pattern. This allows running a single instance of this exporter for multiple postgres targets. Using the multi-target functionality of this exporter isoptional 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 theauth_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:5432metrics_path:/probeparams: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.

The configuration file controls the behavior of the exporter. It can be set using the--config.file command line flag and defaults topostgres_exporter.yml.

This section defines preset authentication and connection parameters for use in themulti-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 theuserpass type is supported.

Example:

auth_modules:foo1:# Set this to any name you wanttype:userpassuserpass:username:firstpassword:firstpassoptions:# options become key=value parameters of the DSNsslmode:disable

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

To build the Docker image:

make promupromu crossbuild -p linux/amd64 -p linux/armv7 -p linux/arm64 -p linux/ppc64lemake docker

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

• helpShow context-sensitive help (also try --help-long and --help-man).

• [no-]collector.databaseEnable thedatabase collector (default: enabled).

• [no-]collector.database_wraparoundEnable thedatabase_wraparound collector (default: disabled).

• [no-]collector.locksEnable thelocks collector (default: enabled).

• [no-]collector.long_running_transactionsEnable thelong_running_transactions collector (default: disabled).

• [no-]collector.postmasterEnable thepostmaster collector (default: disabled).

• [no-]collector.process_idleEnable theprocess_idle collector (default: disabled).

• [no-]collector.replicationEnable thereplication collector (default: enabled).

• [no-]collector.replication_slotEnable thereplication_slot collector (default: enabled).

• [no-]collector.stat_activity_autovacuumEnable thestat_activity_autovacuum collector (default: disabled).

• [no-]collector.stat_bgwriterEnable thestat_bgwriter collector (default: enabled).

• [no-]collector.stat_databaseEnable thestat_database collector (default: enabled).

• [no-]collector.stat_progress_vacuumEnable thestat_progress_vacuum collector (default: enabled).

• [no-]collector.stat_statementsEnable thestat_statements collector (default: disabled).

• [no-]collector.stat_statements.include_queryEnable selecting statement query together with queryId. (default: disabled)

• --collector.stat_statements.query_lengthMaximum length of the statement text. Default is 120.

• [no-]collector.stat_user_tablesEnable thestat_user_tables collector (default: enabled).

• [no-]collector.stat_wal_receiverEnable thestat_wal_receiver collector (default: disabled).

• [no-]collector.statio_user_indexesEnable thestatio_user_indexes collector (default: disabled).

• [no-]collector.statio_user_tablesEnable thestatio_user_tables collector (default: enabled).

• [no-]collector.walEnable thewal collector (default: enabled).

• [no-]collector.xlog_locationEnable thexlog_location collector (default: disabled).

• config.fileSet the config file path. Default ispostgres_exporter.yml

• web.systemd-socketUse systemd socket activation listeners instead of port listeners (Linux only). Default isfalse

• web.listen-addressAddress to listen on for web interface and telemetry. Default is:9187.

• web.config.fileConfiguration file to use TLS and/or basic authentication. The format of thefile is describedin the exporter-toolkit repository.

• web.telemetry-pathPath under which to expose metrics. Default is/metrics.

• disable-default-metricsUse only metrics supplied fromqueries.yaml via--extend.query-path. Default isfalse.

• disable-settings-metricsUse the flag if you don't want to scrapepg_settings. Default isfalse.

• auto-discover-databases (DEPRECATED)Whether to discover the databases on a server dynamically. Default isfalse.

• extend.query-path (DEPRECATED)Path to a YAML file containing custom queries to run. Check outqueries.yamlfor examples of the format.

• dumpmapsDo not run - print the internal representation of the metric maps. Useful when debugging a customqueries file.

• constantLabels (DEPRECATED)Labels to set in all metrics. A list oflabel=value pairs, separated by commas.

• versionShow 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.

• log.levelSet logging level: one ofdebug,info,warn,error.

• log.formatSet the log format: one oflogfmt,json.

The following environment variables configure the exporter:

• DATA_SOURCE_NAMEthe default legacy format. Accepts URI form and key=value form arguments. TheURI may contain the username and password to connect with.

• DATA_SOURCE_URIan alternative toDATA_SOURCE_NAME which exclusively accepts the hostnamewithout a username and password component. For example,my_pg_hostname ormy_pg_hostname:5432/postgres?sslmode=disable.

• DATA_SOURCE_URI_FILEThe same as above but reads the URI from a file.

• DATA_SOURCE_USERWhen usingDATA_SOURCE_URI, this environment variable is used to specifythe username.

• DATA_SOURCE_USER_FILEThe same, but reads the username from a file.

• DATA_SOURCE_PASSWhen usingDATA_SOURCE_URI, this environment variable is used to specifythe password to connect with.

• DATA_SOURCE_PASS_FILEThe same as above but reads the password from a file.

• PG_EXPORTER_WEB_TELEMETRY_PATHPath under which to expose metrics. Default is/metrics.

• PG_EXPORTER_DISABLE_DEFAULT_METRICSUse only metrics supplied fromqueries.yaml. Value can betrue orfalse. Default isfalse.

• PG_EXPORTER_DISABLE_SETTINGS_METRICSUse the flag if you don't want to scrapepg_settings. Value can betrue orfalse. Default isfalse.

• PG_EXPORTER_AUTO_DISCOVER_DATABASES (DEPRECATED)Whether to discover the databases on a server dynamically. Value can betrue orfalse. Default isfalse.

• PG_EXPORTER_EXTEND_QUERY_PATHPath to a YAML file containing custom queries to run. Check outqueries.yamlfor examples of the format.

• PG_EXPORTER_CONSTANT_LABELS (DEPRECATED)Labels to set in all metrics. A list oflabel=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_PREFIXA prefix to use for each of the default metrics exported by postgres-exporter. Default ispg

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

The PostgreSQL server'sdata source namemust be set via theDATA_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 thegithub.com/lib/pq module for other ways to format the connection string.

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

x="""tab separated raw text of a documentation table"""forlinStringIO(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 buildrich self-documenting metrics for the exporter.

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

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

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-metricsflag. This removes all built-in metrics, and uses only metrics defined by queries in thequeries.yaml file you supply(so you must supply one, otherwise the exporter will return nothing but internal statuses and not your database).

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 theresult a new set of DSN's is created for which the metrics are scraped.

In addition, the option--exclude-databases adds the possibily 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 topg_database table, but do scrape from only if database is in include list.

To be able to collect metrics frompg_stat* views as non-superuser in PostgreSQLserver versions >= 10 you can grant thepg_monitor orpg_read_all_statsbuilt-in roles to the user. Ifyou need to monitor older PostgreSQL servers, you will have to create functionsand 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 REPLACEFUNCTION__tmp_create_user() returns voidas $$BEGIN  IF NOT EXISTS (SELECT-- SELECT list can stay empty for thisFROMpg_catalog.pg_userWHERE  usename='postgres_exporter') THENCREATEUSERpostgres_exporter;  END IF;END;$$ language plpgsql;SELECT __tmp_create_user();DROPFUNCTION __tmp_create_user();ALTERUSER postgres_exporter WITH PASSWORD'password';ALTERUSER postgres_exporterSET 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 CONNECTON 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 sothey can act as security barriers. Functions need to be created to share thisdata with the non-superuser. Only creating the views will leave out the mostimportant bits of data.

CREATESCHEMAIF NOT EXISTS postgres_exporter;GRANT USAGEON SCHEMA postgres_exporter TO postgres_exporter;CREATE OR REPLACEFUNCTIONget_pg_stat_activity() RETURNS SETOF pg_stat_activityAS$$SELECT*FROMpg_catalog.pg_stat_activity; $$LANGUAGE sqlVOLATILESECURITY DEFINER;CREATE OR REPLACEVIEWpostgres_exporter.pg_stat_activityASSELECT*from get_pg_stat_activity();GRANTSELECTONpostgres_exporter.pg_stat_activity TO postgres_exporter;CREATE OR REPLACEFUNCTIONget_pg_stat_replication() RETURNS SETOF pg_stat_replicationAS$$SELECT*FROMpg_catalog.pg_stat_replication; $$LANGUAGE sqlVOLATILESECURITY DEFINER;CREATE OR REPLACEVIEWpostgres_exporter.pg_stat_replicationASSELECT*FROM get_pg_stat_replication();GRANTSELECTONpostgres_exporter.pg_stat_replication TO postgres_exporter;CREATE EXTENSION IF NOT EXISTS pg_stat_statements;CREATE OR REPLACEFUNCTIONget_pg_stat_statements() RETURNS SETOF pg_stat_statementsAS$$SELECT*FROMpublic.pg_stat_statements; $$LANGUAGE sqlVOLATILESECURITY DEFINER;CREATE OR REPLACEVIEWpostgres_exporter.pg_stat_statementsASSELECT*FROM get_pg_stat_statements();GRANTSELECTONpostgres_exporter.pg_stat_statements TO postgres_exporter;

NOTE
Remember to usepostgres database name in the connection string:

DATA_SOURCE_NAME=postgresql://postgres_exporter:password@localhost:5432/postgres?sslmode=disable

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