Postgres

Postgres

Agent Check Agent Check

Linux Mac OS Windows OS Supported

PostgreSQL Graph

This page describes the Postgres agent integration; if you are looking for the Database Monitoring product for Postgres, see Datadog Database Monitoring.

Overview

Get metrics from PostgreSQL in real time to:

  • Visualize and monitor PostgreSQL states.
  • Received notifications about PostgreSQL failovers and events.

Setup

Installation

The PostgreSQL check is packaged with the Agent. To start gathering your PostgreSQL metrics and logs, install the Agent.

Configuration

Prepare Postgres

To get started with the PostgreSQL integration, create a read-only datadog user with proper access to your PostgreSQL server. Start psql on your PostgreSQL database.

For PostgreSQL version 10 and above, run:

create user datadog with password '<PASSWORD>';
grant pg_monitor to datadog;
grant SELECT ON pg_stat_database to datadog;

For older PostgreSQL versions, run:

create user datadog with password '<PASSWORD>';
grant SELECT ON pg_stat_database to datadog;

To verify the permissions are correct, run the following command:

psql -h localhost -U datadog postgres -c \
"select * from pg_stat_database LIMIT(1);" \
&& echo -e "\e[0;32mPostgres connection - OK\e[0m" \
|| echo -e "\e[0;31mCannot connect to Postgres\e[0m"

When it prompts for a password, enter the one used in the first command.

Note: For PostgreSQL versions 9.6 and below, run the following and create a SECURITY DEFINER to read from pg_stat_activity.

CREATE FUNCTION pg_stat_activity() RETURNS SETOF pg_catalog.pg_stat_activity AS
$$ SELECT * from pg_catalog.pg_stat_activity; $$
LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE VIEW pg_stat_activity_dd AS SELECT * FROM pg_stat_activity();
grant SELECT ON pg_stat_activity_dd to datadog;

Note: When generating custom metrics that require querying additional tables, you may need to grant the SELECT permission on those tables to the datadog user. Example: grant SELECT on <TABLE_NAME> to datadog;. Check the FAQ section for more information.

Host

To configure this check for an Agent running on a host:

Metric collection
  1. Edit the postgres.d/conf.yaml file to point to your host / port and set the masters to monitor. See the sample postgres.d/conf.yaml for all available configuration options.

    init_config:
    
    instances:
      ## @param host - string - required
      ## The hostname to connect to.
      ## NOTE: Even if the server name is "localhost", the agent connects to
      ## PostgreSQL using TCP/IP, unless you also provide a value for the sock key.
      #
      - host: localhost
    
        ## @param port - integer - required
        ## Port to use when connecting to PostgreSQL.
        #
        port: 5432
    
        ## @param user - string - required
        ## Datadog Username created to connect to PostgreSQL.
        #
        username: datadog
    
        ## @param pass - string - required
        ## Password associated with the Datadog user.
        #
        password: "<PASSWORD>"
    
        ## @param dbname - string - optional - default: postgres
        ## Name of the PostgresSQL database to monitor.
        ## Note: If omitted, the default system postgres database is queried.
        #
        dbname: "<DB_NAME>"
       
        # @param disable_generic_tags - boolean - optional - default: false
        # The integration will stop sending server tag as is reduntant with host tag
        disable_generic_tags: true
    
  2. Restart the Agent.

Trace collection

Datadog APM integrates with Postgres to see the traces across your distributed system. Trace collection is enabled by default in the Datadog Agent v6+. To start collecting traces:

  1. Enable trace collection in Datadog.
  2. Instrument your application that makes requests to Postgres.
Log collection

Available for Agent versions >6.0

PostgreSQL default logging is to stderr, and logs do not include detailed information. It is recommended to log into a file with additional details specified in the log line prefix. Refer to the PostgreSQL documentation on this topic for additional details.

  1. Logging is configured within the file /etc/postgresql/<VERSION>/main/postgresql.conf. For regular log results, including statement outputs, uncomment the following parameters in the log section:

      logging_collector = on
      log_directory = 'pg_log'  # directory where log files are written,
                                # can be absolute or relative to PGDATA
      log_filename = 'pg.log'   # log file name, can include pattern
      log_statement = 'all'     # log all queries
      #log_duration = on
      log_line_prefix= '%m [%p] %d %a %u %h %c '
      log_file_mode = 0644
      ## For Windows
      #log_destination = 'eventlog'
    
  2. To gather detailed duration metrics and make them searchable in the Datadog interface, they should be configured inline with the statement themselves. See below for the recommended configuration differences from above and note that both log_statement and log_duration options are commented out. See discussion on this topic here.

    This config logs all statements, but to reduce the output to those which have a certain duration, set the log_min_duration_statement value to the desired minimum duration (in milliseconds):

      log_min_duration_statement = 0    # -1 is disabled, 0 logs all statements
                                        # and their durations, > 0 logs only
                                        # statements running at least this number
                                        # of milliseconds
      #log_statement = 'all'
      #log_duration = on
    
  3. Collecting logs is disabled by default in the Datadog Agent, enable it in your datadog.yaml file:

    logs_enabled: true
    
  4. Add and edit this configuration block to your postgres.d/conf.yaml file to start collecting your PostgreSQL logs:

    logs:
      - type: file
        path: "<LOG_FILE_PATH>"
        source: postgresql
        service: "<SERVICE_NAME>"
        #To handle multi line that starts with yyyy-mm-dd use the following pattern
        #log_processing_rules:
        #  - type: multi_line
        #    pattern: \d{4}\-(0?[1-9]|1[012])\-(0?[1-9]|[12][0-9]|3[01])
        #    name: new_log_start_with_date
    

    Change the service and path parameter values to configure for your environment. See the sample postgres.d/conf.yaml for all available configuration options.

  5. Restart the Agent.

Docker

To configure this check for an Agent running on a container:

Metric collection

Set Autodiscovery Integrations Templates as Docker labels on your application container:

LABEL "com.datadoghq.ad.check_names"='["postgres"]'
LABEL "com.datadoghq.ad.init_configs"='[{}]'
LABEL "com.datadoghq.ad.instances"='[{"host":"%%host%%", "port":5432,"username":"datadog","password":"<PASSWORD>"}]'
Log collection

Collecting logs is disabled by default in the Datadog Agent. To enable it, see the Docker log collection documentation.

Then, set Log Integrations as Docker labels:

LABEL "com.datadoghq.ad.logs"='[{"source":"postgresql","service":"postgresql"}]'
Trace collection

APM for containerized apps is supported on Agent v6+ but requires extra configuration to begin collecting traces.

Required environment variables on the Agent container:

ParameterValue
<DD_API_KEY>api_key
<DD_APM_ENABLED>true
<DD_APM_NON_LOCAL_TRAFFIC>true

See Tracing Docker Applications for a complete list of available environment variables and configuration.

Then, instrument your application container that makes requests to Postgres and set DD_AGENT_HOST to the name of your Agent container.

Kubernetes

To configure this check for an Agent running on Kubernetes:

Metric collection

Set Autodiscovery Integrations Templates as pod annotations on your application container. Aside from this, templates can also be configured with a file, a configmap, or a key-value store.

apiVersion: v1
kind: Pod
metadata:
  name: postgres
  annotations:
    ad.datadoghq.com/postgresql.check_names: '["postgres"]'
    ad.datadoghq.com/postgresql.init_configs: '[{}]'
    ad.datadoghq.com/postgresql.instances: |
      [
        {
          "host": "%%host%%",
          "port":"5432",
          "username":"datadog",
          "password":"<PASSWORD>"
        }
      ]      
spec:
  containers:
    - name: postgres
Log collection

Collecting logs is disabled by default in the Datadog Agent. To enable it, see the Kubernetes log collection documentation.

Then, set Log Integrations as pod annotations. This can also be configured with a file, a configmap, or a key-value store.

apiVersion: v1
kind: Pod
metadata:
  name: postgres
  annotations:
    ad.datadoghq.com/postgres.logs: '[{"source":"postgresql","service":"<SERVICE_NAME>"}]'
spec:
  containers:
    - name: postgres
Trace collection

APM for containerized apps is supported on hosts running Agent v6+ but requires extra configuration to begin collecting traces.

Required environment variables on the Agent container:

ParameterValue
<DD_API_KEY>api_key
<DD_APM_ENABLED>true
<DD_APM_NON_LOCAL_TRAFFIC>true

See Tracing Kubernetes Applications and the Kubernetes DaemonSet Setup for a complete list of available environment variables and configuration.

Then, instrument your application container that makes requests to Postgres.

ECS

To configure this check for an Agent running on ECS:

Metric collection

Set Autodiscovery Integrations Templates as Docker labels on your application container:

{
  "containerDefinitions": [{
    "name": "postgres",
    "image": "postgres:latest",
    "dockerLabels": {
      "com.datadoghq.ad.check_names": "[\"postgres\"]",
      "com.datadoghq.ad.init_configs": "[{}]",
      "com.datadoghq.ad.instances": "[{\"host\":\"%%host%%\", \"port\":5432,\"username\":\"datadog\",\"password\":\"<PASSWORD>\"}]"
    }
  }]
}
Log collection

Collecting logs is disabled by default in the Datadog Agent. To enable it, see the ECS log collection documentation.

Then, set Log Integrations as Docker labels:

{
  "containerDefinitions": [{
    "name": "postgres",
    "image": "postgres:latest",
    "dockerLabels": {
      "com.datadoghq.ad.logs": "[{\"source\":\"postgresql\",\"service\":\"postgresql\"}]"
    }
  }]
}
Trace collection

APM for containerized apps is supported on Agent v6+ but requires extra configuration to begin collecting traces.

Required environment variables on the Agent container:

ParameterValue
<DD_API_KEY>api_key
<DD_APM_ENABLED>true
<DD_APM_NON_LOCAL_TRAFFIC>true

See Tracing Docker Applications for a complete list of available environment variables and configuration.

Then, instrument your application container that makes requests to Postgres and set DD_AGENT_HOST to the EC2 private IP address.

Validation

Run the Agent’s status subcommand and look for postgres under the Checks section.

Data Collected

Some of the metrics listed below require additional configuration, see the sample postgres.d/conf.yaml for all configurable options.

Metrics

postgresql.connections
(gauge)
The number of active connections to this database.
Shown as connection
postgresql.commits
(gauge)
The number of transactions that have been committed in this database.
Shown as transaction
postgresql.rollbacks
(gauge)
The number of transactions that have been rolled back in this database.
Shown as transaction
postgresql.disk_read
(gauge)
The number of disk blocks read in this database.
Shown as block
postgresql.buffer_hit
(gauge)
The number of times disk blocks were found in the buffer cache, preventing the need to read from the database.
Shown as hit
postgresql.rows_returned
(gauge)
The number of rows returned by queries in this database
Shown as row
postgresql.rows_fetched
(gauge)
The number of rows fetched by queries in this database
Shown as row
postgresql.rows_inserted
(gauge)
The number of rows inserted by queries in this database
Shown as row
postgresql.rows_updated
(gauge)
The number of rows updated by queries in this database
Shown as row
postgresql.rows_deleted
(gauge)
The number of rows deleted by queries in this database
Shown as row
postgresql.database_size
(gauge)
The disk space used by this database.
Shown as byte
postgresql.db.count
(gauge)
The number of available databases.
Shown as item
postgresql.deadlocks
(gauge)
The number of deadlocks detected in this database
postgresql.temp_bytes
(gauge)
The amount of data written to temporary files by queries in this database.
Shown as byte
postgresql.temp_files
(gauge)
The number of temporary files created by queries in this database.
Shown as file
postgresql.bgwriter.checkpoints_timed
(count)
The number of scheduled checkpoints that were performed.
postgresql.bgwriter.checkpoints_requested
(count)
The number of requested checkpoints that were performed.
postgresql.bgwriter.buffers_checkpoint
(count)
The number of buffers written during checkpoints.
postgresql.bgwriter.buffers_clean
(count)
The number of buffers written by the background writer.
postgresql.bgwriter.maxwritten_clean
(count)
The number of times the background writer stopped a cleaning scan due to writing too many buffers.
postgresql.bgwriter.buffers_backend
(count)
The number of buffers written directly by a backend.
Shown as buffer
postgresql.bgwriter.buffers_alloc
(count)
The number of buffers allocated
postgresql.bgwriter.buffers_backend_fsync
(count)
The of times a backend had to execute its own fsync call instead of the background writer.
postgresql.bgwriter.write_time
(count)
The total amount of checkpoint processing time spent writing files to disk.
Shown as millisecond
postgresql.bgwriter.sync_time
(count)
The total amount of checkpoint processing time spent synchronizing files to disk.
Shown as millisecond
postgresql.locks
(gauge)
The number of locks active for this database.
Shown as lock
postgresql.seq_scans
(gauge)
The number of sequential scans initiated on this table.
postgresql.seq_rows_read
(gauge)
The number of live rows fetched by sequential scans.
Shown as row
postgresql.index_scans
(gauge)
The number of index scans initiated on this table, tagged by index.
postgresql.index_rel_scans
(gauge)
The overall number of index scans initiated on this table.
postgresql.index_rows_fetched
(gauge)
The number of live rows fetched by index scans.
Shown as row
postgresql.index_rel_rows_fetched
(gauge)
The number of live rows fetched by index scans.
Shown as row
postgresql.rows_hot_updated
(gauge)
The number of rows HOT updated, meaning no separate index update was needed.
Shown as row
postgresql.live_rows
(gauge)
The estimated number of live rows.
Shown as row
postgresql.dead_rows
(gauge)
The estimated number of dead rows.
Shown as row
postgresql.autovacuumed
(count)
The number of times this table has been vacuumed by the autovacuum daemon.
postgresql.analyzed
(count)
The number of times this table has been manually analyzed.
postgresql.index_rows_read
(gauge)
The number of index entries returned by scans on this index.
Shown as row
postgresql.table_size
(gauge)
The total disk space used by the specified table. Includes TOAST, free space map, and visibility map. Excludes indexes.
Shown as byte
postgresql.index_size
(gauge)
The total disk space used by indexes attached to the specified table.
Shown as byte
postgresql.total_size
(gauge)
The total disk space used by the table, including indexes and TOAST data.
Shown as byte
postgresql.table.count
(gauge)
The number of user tables in this database.
Shown as table
postgresql.max_connections
(gauge)
The maximum number of client connections allowed to this database.
Shown as connection
postgresql.percent_usage_connections
(gauge)
The number of connections to this database as a fraction of the maximum number of allowed connections.
Shown as fraction
postgresql.replication_delay
(gauge)
The current replication delay in seconds. Only available with postgresql 9.1 and newer
Shown as second
postgresql.replication_delay_bytes
(gauge)
The current replication delay in bytes. Only available with postgresql 9.2 and newer
Shown as byte
postgresql.replication.wal_write_lag
(gauge)
Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written it (but not yet flushed it or applied it). This can be used to gauge the delay that synchronous_commit level remote_write incurred while committing if this server was configured as a synchronous standby. Only available with postgresql 10 and newer.
Shown as second
postgresql.replication.wal_flush_lag
(gauge)
Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written and flushed it (but not yet applied it). This can be used to gauge the delay that synchronous_commit level on incurred while committing if this server was configured as a synchronous standby. Only available with postgresql 10 and newer.
Shown as second
postgresql.replication.wal_replay_lag
(gauge)
Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written, flushed and applied it. This can be used to gauge the delay that synchronous_commit level remote_apply incurred while committing if this server was configured as a synchronous standby. Only available with postgresql 10 and newer.
Shown as second
postgresql.heap_blocks_read
(gauge)
The number of disk blocks read from this table.
Shown as block
postgresql.heap_blocks_hit
(gauge)
The number of buffer hits in this table.
Shown as hit
postgresql.index_blocks_read
(gauge)
The number of disk blocks read from all indexes on this table.
Shown as block
postgresql.index_blocks_hit
(gauge)
The number of buffer hits in all indexes on this table.
Shown as hit
postgresql.table_bloat
(gauge)
The estimated percentage of table bloat.
Shown as percent
postgresql.toast_blocks_read
(gauge)
The number of disk blocks read from this table's TOAST table.
Shown as block
postgresql.toast_blocks_hit
(gauge)
The number of buffer hits in this table's TOAST table.
Shown as hit
postgresql.toast_index_blocks_read
(gauge)
The number of disk blocks read from this table's TOAST table index.
Shown as block
postgresql.toast_index_blocks_hit
(gauge)
The number of buffer hits in this table's TOAST table index.
Shown as block
postgresql.transactions.open
(gauge)
The number of open transactions in this database.
Shown as transaction
postgresql.transactions.idle_in_transaction
(gauge)
The number of 'idle in transaction' transactions in this database.
Shown as transaction
postgresql.before_xid_wraparound
(gauge)
The number of transactions that can occur until a transaction wraparound.
Shown as transaction
postgresql.active_queries
(gauge)
The number of active queries in this database.
postgresql.waiting_queries
(gauge)
The number of waiting queries in this database.
postgresql.queries.count
(count)
The total query execution count per normalized query, database, and user.
Shown as query
postgresql.queries.time
(count)
The total query execution time per normalized query, database, and user.
Shown as nanosecond
postgresql.queries.rows
(count)
The total number of rows retrieved or affected per normalized query, database, and user.
Shown as row
postgresql.queries.shared_blks_hit
(count)
Total number of shared block cache hits per normalized query, database, and user.
Shown as block
postgresql.queries.shared_blks_read
(count)
Total number of shared blocks read per normalized query, database, and user.
Shown as block
postgresql.queries.shared_blks_dirtied
(count)
Total number of shared blocks dirtied per normalized query, database, and user.
Shown as block
postgresql.queries.shared_blks_written
(count)
Total number of shared blocks written per normalized query, database, and user.
Shown as block
postgresql.queries.local_blks_hit
(count)
Total number of local block cache hits per normalized query, database, and user.
Shown as block
postgresql.queries.local_blks_read
(count)
Total number of local blocks read per normalized query, database, and user.
Shown as block
postgresql.queries.local_blks_dirtied
(count)
Total number of local blocks dirtied per normalized query, database, and user.
Shown as block
postgresql.queries.local_blks_written
(count)
Total number of local blocks written per normalized query, database, and user.
Shown as block
postgresql.queries.temp_blks_read
(count)
Total number of temp blocks read per normalized query, database, and user.
Shown as block
postgresql.queries.temp_blks_written
(count)
Total number of temp blocks written per normalized query, database, and user.
Shown as block
postgresql.wal_age
(gauge)
The age in seconds of the oldest WAL file
Shown as second

Events

The PostgreSQL check does not include any events.

Service Checks

postgres.can_connect
Returns CRITICAL if the Agent is unable to connect to the monitored PostgreSQL instance. Returns OK otherwise.
Statuses: ok, critical

Troubleshooting

Need help? Contact Datadog support.

Further Reading

Additional helpful documentation, links, and articles:

FAQ

Blog posts