PostgreSQL Exporter

Helm Charts

This PostgreSQL exporter review discusses important metrics, alert rules, and all you need to know in order to monitor PostgreSQL, an open source object-related database system using SQL language.

About PostgreSQL

PostgreSQL is an open-source object-relational database system with a strong reputation for reliability, feature robustness, and performance. It uses the SQL language combined with many features that safely store and scale the most complicated data workloads. 

PostgreSQL runs on all major operating systems and is offered as a service by all major cloud providers. It comes with many features aimed at helping developers build applications as well as helping administrators protect data integrity and create fault-tolerant environments. It supports users in managing their data no matter how big or small the dataset.

Since databases are such a critical resource, downtime can cause significant financial and reputation losses, so monitoring is a must. The Postgres exporter is required to monitor and expose Postgres metrics. It queries Postgres, scraps the data, and exposes the metrics to a Kubernetes service endpoint that can further be scrapped by Prometheus to ingest the time series data. For monitoring of Postgres, an external Prometheus exporter is used, which is maintained by the Prometheus Community. On deployment, this exporter scraps sizable metrics from Postgres and helps users get crucial and continuous information about the database which is difficult to extract from PostgreSQL directly. 

For this setup, we are using Bitnami PostgreSQL Helm charts to start the Postgres server.

How do you set up an exporter for Prometheus?

With the latest version of Prometheus (2.33 as of February 2022), these are the ways to set up a Prometheus exporter: 

Method 1 - Native

Supported by Prometheus since the beginning
To set up an exporter in native way a Prometheus config needs to be updated to add the target.
A sample configuration:

# scrape_config job

  - job_name: postgres
    scrape_interval: 45s
    scrape_timeout:  30s
    metrics_path: "/metrics"
    - targets:
      - <postgres exporter endpoint>Code language: PHP (php)
Method 2 - Service Discovery

This method is applicable for Kubernetes deployment only.
With this, a default scrap config can be added to the prometheus.yaml file and an annotation can be added to the exporter service. With this, Prometheus will automatically start scrapping the data from the services with the mentioned path.


     - job_name: kubernetes-services   
        scrape_interval: 15s
        scrape_timeout: 10s
        - role: service
        # Example relabel to scrape only endpoints that have
        # "true" annotation.
        - source_labels: [__meta_kubernetes_service_annotation_prometheus_io_scrape]
          action: keep
          regex: true
        # "/scrape/path" annotation.
        - source_labels: [__meta_kubernetes_service_annotation_prometheus_io_path]
          action: replace
          target_label: __metrics_path__
          regex: (.+)
        # "80" annotation.
        - source_labels: [__address__, __meta_kubernetes_service_annotation_prometheus_io_port]
          action: replace
          target_label: __address__
          regex: (.+)(?::\d+);(\d+)
          replacement: $1:$2Code language: PHP (php)

Exporter service annotations:

 annotations: /metrics "true"Code language: PHP (php)
Method 3 - Prometheus Operator

Setting up a service monitor
The Prometheus operator supports an automated way of scraping data from the exporters by setting up a service monitor Kubernetes object. For reference, a sample service monitor for PostgreSQL can be found here.
These are the necessary steps:

Step 1

Add/update Prometheus operator’s selectors. By default, the Prometheus operator comes with empty selectors which will select every service monitor available in the cluster for scrapping the data.

To check your Prometheus configuration:

Kubectl get prometheus -n <namespace> -o yamlCode language: HTML, XML (xml)

A sample output will look like this.

ruleNamespaceSelector: {}
        app: kube-prometheus-stack
        release: kps
    scrapeInterval: 1m
    scrapeTimeout: 10s
      fsGroup: 2000
      runAsGroup: 2000
      runAsNonRoot: true
      runAsUser: 1000
    serviceAccountName: kps-kube-prometheus-stack-prometheus
    serviceMonitorNamespaceSelector: {}
        release: kpsCode language: CSS (css)

Here you can see that this Prometheus configuration is selecting all the service monitors with the label release = kps

So with this, if you are modifying the default Prometheus operator configuration for service monitor scrapping, make sure you use the right labels in your service monitor as well.

Step 2

Add a service monitor and make sure it has a matching label and namespace for the Prometheus service monitor selectors (serviceMonitorNamespaceSelector & serviceMonitorSelector).

Sample configuration:

kind: ServiceMonitor
  annotations: postgres-exporter monitor
    app: prometheus-postgres-exporter Helm
    chart: prometheus-postgres-exporter-1.1.0
    heritage: Helm
    release: kps
  name: prometheus-postgres-exporter
  namespace: monitor
  - interval: 15s
    port: postgres-exporter
      app: prometheus-postgres-exporter
      release: postgres-exporter

As you can see, a matching label on the service monitor release = kps is used that is specified in the Prometheus operator scrapping configuration.


The following ones are handpicked metrics that will provide insights into PostgreSQL.

  1. PG is up
    This shows whether the last scrape of metrics from PostgreSQL was able to connect to the server
    ➡ The key of the exporter metric is “pg_up”
    ➡ The value of the metric is a boolean -  1 or 0 which symbolizes if PostgreSQL is up or down respectively (1 for yes, 0 for no) 
  1. Replication lag
    In scenarios with replicated PostgreSQL servers, a high replication lag rate can lead to coherence problems if the master goes down.
    ➡ The metric key is “pg_replication_lag”
    ➡ The value will be in seconds
  1.  Too many connections
    By default, PostgreSQL supports 115 concurrent connections - 15 for superusers and 100 connections for other users. However, you can increase the maximum number of connections in PostgreSQL to support greater concurrency. If there are too many concurrent connections to the PostgreSQL database, it might give the error message “FATAL: sorry, too many clients already” and reject incoming connections.
    ➡ The metric “ pg_stat_activity_count” gives the total active connections on PostgreSQL
    ➡ The number should be calculated based on “pg_settings_max_connections” which is 100 by default
  1. Database size
    As the name suggests, the metric will give insight into the storage usage of each one of the PostgreSQL databases. 
    ➡ The meric “pg_database_size_bytes” shows storage used by each database
  1. Maximum transaction duration
    This metric provides information regarding latency and performance by calculating how much time it takes to get the results from the slowest active transaction.
    ➡ The metric  “pg_stat_activity_max_tx_duration” exposes maximum duration in seconds any active transaction has been running
  • prometheus-community/postgres_exporter
  • prometheus-community/postgres_exporter

    Build Status
    Coverage Status
    Go Report Card
    Docker Pulls

    PostgreSQL Server Exporter

    Prometheus exporter for PostgreSQL server metrics.

    CI Tested PostgreSQL versions: 9.4, 9.5, 9.6, 10, 11, 12, 13, 14

    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_NAME="postgresql://postgres:password@localhost:5432/postgres?sslmode=disable" \

    Building and running

    git clone
    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/amd64 -p linux/ppc64le
    make docker

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


    • help
      Show context-sensitive help (also try --help-long and --help-man).
    • collector.database
      Enable the pg_database collector. Default is enabled
    • collector.bgwriter
      Enable the pg_stat_bgwriter collector. Default is enabled
    • web.listen-address
      Address to listen on for web interface and telemetry. Default is :9187.
    • web.telemetry-path
      Path under which to expose metrics. Default is /metrics.
    • disable-default-metrics
      Use only metrics supplied from queries.yaml via --extend.query-path. Default is false.
    • disable-settings-metrics
      Use the flag if you don't want to scrape pg_settings. Default is false.
    • auto-discover-databases
      Whether to discover the databases on a server dynamically. Default is false.
    • extend.query-path
      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.
    • constantLabels
      Labels to set in all metrics. A list of label=value pairs, separated by commas.
    • version
      Show application version.
    • exclude-databases
      A list of databases to remove when autoDiscoverDatabases is enabled.
    • include-databases
      A list of databases to only include when autoDiscoverDatabases is enabled.
    • log.level
      Set logging level: one of debug, info, warn, error.
    • log.format
      Set the log format: one of logfmt, json.
    • web.config.file
      Configuration file to use TLS and/or basic authentication. The format of the
      file is described in the exporter-toolkit repository.

    Environment Variables

    The following environment variables configure the exporter:

      the default legacy format. Accepts URI form and key=value form arguments. The
      URI may contain the username and password to connect with.
      an alternative to DATA_SOURCE_NAME which exclusively accepts the hostname
      without a username and password component. For example, my_pg_hostname or
      The same as above but reads the URI from a file.
      When using DATA_SOURCE_URI, this environment variable is used to specify
      the username.
      The same, but reads the username from a file.
      When using DATA_SOURCE_URI, this environment variable is used to specify
      the password to connect with.
      The same as above but reads the password from a file.
      Address to listen on for web interface and telemetry. Default is :9187.
      Path under which to expose metrics. Default is /metrics.
      Use only metrics supplied from queries.yaml. Value can be true or false. Default is false.
      Use the flag if you don't want to scrape pg_settings. Value can be true or false. Default is false.
      Whether to discover the databases on a server dynamically. Value can be true or false. Default is false.
      Path to a YAML file containing custom queries to run. Check out queries.yaml
      for examples of the format.
      Labels to set in all metrics. A list of label=value pairs, separated by commas.
      A comma-separated list of databases to remove when autoDiscoverDatabases is enabled. Default is empty string.
      A comma-separated list of databases to only include when autoDiscoverDatabases is enabled. Default is empty string,
      means allow all.
      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 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

    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

    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.

    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 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:
    -- 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 $$
              SELECT                       -- SELECT list can stay empty for this
              FROM   pg_catalog.pg_user
              WHERE  usename = 'postgres_exporter') THEN
        CREATE USER postgres_exporter;
      END IF;
    $$ 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
    CREATE OR REPLACE VIEW postgres_exporter.pg_stat_activity
      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
    CREATE OR REPLACE VIEW postgres_exporter.pg_stat_replication
      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
    CREATE OR REPLACE VIEW postgres_exporter.pg_stat_statements
      SELECT * FROM get_pg_stat_statements();
    GRANT SELECT ON postgres_exporter.pg_stat_statements TO postgres_exporter;


    Remember to use postgres database name in the connection string:


    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
  • PostgreSQL Exporter Helm Chart
  • PostgreSQL Exporter Helm Chart

    The exporter, alert rule, and dashboard can be deployed in Kubernetes using the Helm chart. The Helm chart used for deployment is taken from the Prometheus community, which can be found here.

    Installing PostgreSQL Server

    If your Postgres server is not up and ready you can start it using Helm:

    $ helm repo add bitnami
    $ helm install my-release bitnami/postgresql

    Note that bitnami charts allow you to deploy a Postgres exporter as part of the Helm chart. You can enable it by adding “--set metrics.enabled=true”

    Installing PostgreSQL Exporter
    helm repo add Prometheus-community
    helm repo update
    helm install my-release prometheus-community/prometheus-postgres-exporter

    Some of the common parameters that must be changed in the values file include: 

        # Specify one of both datasource or datasourceSecret
        user: postgres
        # Only one of password, passwordSecret and pgpassfile can be specified
        # Specify passwordSecret if DB password is stored in secret.
        passwordSecret: {}

    All these parameters can be tuned via the values.yaml file here.

    Scrape the metrics

    There are multiple ways to scrape the metrics as discussed above. In addition to the native way of setting up Prometheus monitoring, a service monitor can be deployed (if a Prometheus operator is being used) to scrap the data from the Postgres exporter. With this approach, multiple Postgres servers can be scrapped without altering the Prometheus configuration. Every Postgres exporter comes with its own service monitor.
    In the above-mentioned chart, a service monitor can be deployed by turning it on from the values.yaml file here.

      # When set true then use a ServiceMonitor to configure scraping
      enabled: false
      # Set the namespace the ServiceMonitor should be deployed
      # namespace: monitoring
      # Set how frequently Prometheus should scrape
      # interval: 30s
      # Set path to cloudwatch-exporter telemtery-path
      # telemetryPath: /metrics
      # Set labels for the ServiceMonitor, use this to define your scrape label for Prometheus Operator
      # labels:
      # Set timeout for scrape
      # timeout: 10s
      # Set of labels to transfer from the Kubernetes Service onto the target
      # targetLabels: []
      # MetricRelabelConfigs to apply to samples before ingestion
      # metricRelabelings: []
      # Set relabel_configs as per
      # relabelings: []

    Update the annotation section here if you are not using the Prometheus Operator.

      annotations: /metrics "true"

    This concludes our discussion of the PostgreSQL exporter! If you have any questions, you can reach our team via Stay tuned for further exporter reviews and tips coming soon.

  • PostgreSQL Exporter Alerts
  • PostgreSQL Exporter Alerts

    After digging into all the valuable metrics, this section explains in detail how we can get critical alerts.

    PromQL is a query language for the Prometheus monitoring system. It is designed for building powerful yet simple queries for graphs, alerts, or derived time series (aka recording rules). PromQL is designed from scratch and has zero common grounds with other query languages used in time series databases, such as SQL in TimescaleDB, InfluxQL, or Flux. More details can be found here.

    Prometheus comes with a built-in Alert Manager that is responsible for sending alerts (could be email, Slack, or any other supported channel) when any of the trigger conditions is met. Alerting rules allow users to define alerts based on Prometheus query expressions. They are defined based on the available metrics scraped by the exporter. Click here for a good source for community-defined alerts.

    A general alert looks as follows:

    - alert:(Alert Name)
    expr: (Metric exported from exporter) >/</==/<=/=> (Value)
    for: (wait for a certain duration between first encountering a new expression output vector element and counting an alert as firing for this element)
    labels: (allows specifying a set of additional labels to be attached to the alert)
    annotation: (specifies a set of informational labels that can be used to store longer additional information)

    Some of the recommended PostgreSQL alerts are:

    1. Alert - PostgreSQL is down
    - alert: PostgresqlDown
        expr: pg_up == 0
        for: 0m
          severity: critical
          summary: Postgresql down (instance {{ $labels.instance }})
          description: "Postgresql instance is down\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"
    1. Alert - Replication lag
      - alert: PostgresqlReplicationLag
        expr: pg_replication_lag > 30 and ON(instance) pg_replication_is_replica == 1
        for: 0m
          severity: critical
          summary: Postgresql replication lag (instance {{ $labels.instance }})
          description: "PostgreSQL replication lag is going up (> 30s)\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"
    1.  Alert - Too many connections
     - alert: PostgresqlTooManyConnections
        expr: sum by (datname) (pg_stat_activity_count{datname!~"template.*|postgres"}) > pg_settings_max_connections * 0.8
        for: 2m
          severity: warning
          summary: Postgresql too many connections (instance {{ $labels.instance }})
          description: "PostgreSQL instance has too many connections (> 80%).\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"
    1.   Alert -  Database size
    - alert: PostgresqlHighDbSize
        expr: pg_database_size_bytes / (1024 * 1024 * 1024)
     > 100  # this value depends on available disk size 
        for: 0m
          severity: critical
          summary: Postgresql DB size is more than 100 GB (instance {{ $labels.instance }})
          description: "Postgresql DB size is more than 100 GB\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"
    1.  Alert - Max transaction duration
    - alert: PostgresqlTXDuration
        expr: pg_stat_activity_max_tx_duration{state="active"} > 2
        for: 0m
          severity: critical
          summary: Postgresql active transaction takes more than 2 seconds to complete (instance {{ $labels.instance }})
          description: "PostgreSQL Postgresql active transaction takes more than 2 seconds\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"
  • PostgreSQL Exporter Grafana
  • PostgreSQL Exporter Grafana

    Graphs are easier to understand and more user-friendly than a row of numbers. For this purpose, users can plot their time series data in visualized format using Grafana.

    Grafana is an open-source dashboarding tool used for visualizing metrics with the help of customizable and illustrative charts and graphs. It connects very well with Prometheus and makes monitoring easy and informative. Dashboards in Grafana are made up of panels, with each panel running a PromQL query to fetch metrics from Prometheus.
    Grafana supports community-driven graphs for most of the widely used software, which can be directly imported to the Grafana Community.

    NexClipper uses the PostgreSQL database by the Lucas Estienne dashboard, which is widely accepted and has a lot of useful panels.

    What is a Panel?

    Panels are the most basic component of a dashboard and can display information in various ways, such as gauge, text, bar chart, graph, and so on. They provide information in a very interactive way. Users can view every panel separately and check the value of metrics within a specific time range. 
    The values on the panel are queried using PromQL, which is Prometheus Query Language. PromQL is a simple query language used to query metrics within Prometheus. It enables users to query data, aggregate and apply arithmetic functions to the metrics, and then further visualize them on panels.

    Here are some examples of panels:

    1. Database metrics and settings

    2. Database statistics

0 0 votes
Article Rating

Leave a Reply

Inline Feedbacks
View all comments
1 year ago

comment test

© 2023