Slide 1
Most trusted JOB oriented professional program
DevOps Certified Professional (DCP)

Take your first step into the world of DevOps with this course, which will help you to learn about the methodologies and tools used to develop, deploy, and operate high-quality software.

Slide 2
DevOps to DevSecOps – Learn the evolution
DevSecOps Certified Professional (DSOCP)

Learn to automate security into a fast-paced DevOps environment using various open-source tools and scripts.

Slide 2
Get certified in the new tech skill to rule the industry
Site Reliability Engineering (SRE) Certified Professional

A method of measuring and achieving reliability through engineering and operations work – developed by Google to manage services.

Slide 2
Master the art of DevOps
Master in DevOps Engineering (MDE)

Get enrolled for the most advanced and only course in the WORLD which can make you an expert and proficient Architect in DevOps, DevSecOps and Site Reliability Engineering (SRE) principles together.

Slide 2
Gain expertise and certified yourself
Azure DevOps Solutions Expert

Learn about the DevOps services available on Azure and how you can use them to make your workflow more efficient.

Slide 3
Learn and get certified
AWS Certified DevOps Professional

Learn about the DevOps services offered by AWS and how you can use them to make your workflow more efficient.

previous arrow
next arrow

Datadog Integration with Postgres For Metric, Trace & Log

Spread the Knowledge

Database Monitoring provides deep visibility into your Postgres databases by exposing query metrics, query samples, explain plans, database states, failovers, and events.

The Agent collects telemetry directly from the database by logging in as a read-only user. Do the following setup to enable Database Monitoring with your Postgres database:

Step 1 – Install Datadog Agent

Step 2 – Install and Configure postgresql 

Step 3 – Prepare Postgres & Configurations

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

Connect to the chosen database as a superuser (or another user with sufficient permissions). For example, if your chosen database is postgres, connect as the postgres user using psql by running:


$ psql -h mydb.example.com -d postgres -U postgres


For PostgreSQL version 10 and above, run:

create user datadog with password '<PASSWORD>';

grant SELECT ON pg_stat_database to datadog;

# Create the following schema in every database:
CREATE SCHEMA datadog;
GRANT USAGE ON SCHEMA datadog TO datadog;
GRANT USAGE ON SCHEMA public TO datadog;
GRANT pg_monitor TO datadog;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

When generating custom metrics that require querying additional tables, you may need to grant the SELECT permission on those tables to the datadog user. 

grant SELECT on <TABLE_NAME> 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"

psql -h localhost -U datadog postgres -A \
  -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"

psql -h localhost -U datadog postgres -A \
  -c "select * from pg_stat_activity limit 1;" \
  && echo -e "\e[0;32mPostgres pg_stat_activity read OK\e[0m" \
  || echo -e "\e[0;31mCannot read from pg_stat_activity\e[0m"

psql -h localhost -U datadog postgres -A \
  -c "select * from pg_stat_statements limit 1;" \
  && echo -e "\e[0;32mPostgres pg_stat_statements read OK\e[0m" \
  || echo -e "\e[0;31mCannot read from pg_stat_statements\e[0m"

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

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.

Step 4 – Host configure to check for an Agent for Metric collection

  • Edit the postgres.d/conf.yaml file to point to your host / port and set the masters to monitor.
  • Restart the Agent.

init_config:
instances:
  - dbm: true
    host: localhost
    port: 5432
    username: datadog
    password: '<PASSWORD>'
    ## Optional: Connect to a different database if needed for `custom_queries`
    # dbname: '<DB_NAME>'

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


Step 5 – Configure Postgres settings

Configure the following parameters in the postgresql.conf file and then restart the server for the settings to take effect.

Step 6 – verify

Restart a agent Postgres

$ sudo datadog-agent status
Run the Agent’s status subcommand and look for postgres under the Checks section. Or visit the Databases page to get started!

Integrtion metrics

The following metrics will be tracked by this integration:

NameUnitsDescription
postgres.replication_delay_bytesbytesDeprecated please use postgresql.replication_delay_bytes instead
postgresql.active_queriesThe number of active queries in this database.
postgresql.active_waiting_queriesThe number of waiting queries in this database in state active.
postgresql.analyzedThe number of times this table has been manually analyzed.
postgresql.autoanalyzedThe number of times this table has been analyzed by the autovacuum daemon.
postgresql.autovacuumedThe number of times this table has been vacuumed by the autovacuum daemon.
postgresql.before_xid_wraparoundtransactionsThe number of transactions that can occur until a transaction wraparound.
postgresql.bgwriter.buffers_allocThe number of buffers allocated
postgresql.bgwriter.buffers_backendbuffersThe number of buffers written directly by a backend.
postgresql.bgwriter.buffers_backend_fsyncThe of times a backend had to execute its own fsync call instead of the background writer.
postgresql.bgwriter.buffers_checkpointThe number of buffers written during checkpoints.
postgresql.bgwriter.buffers_cleanThe number of buffers written by the background writer.
postgresql.bgwriter.checkpoints_requestedThe number of requested checkpoints that were performed.
postgresql.bgwriter.checkpoints_timedThe number of scheduled checkpoints that were performed.
postgresql.bgwriter.maxwritten_cleanThe number of times the background writer stopped a cleaning scan due to writing too many buffers.
postgresql.bgwriter.sync_timemillisecondsThe total amount of checkpoint processing time spent synchronizing files to disk.
postgresql.bgwriter.write_timemillisecondsThe total amount of checkpoint processing time spent writing files to disk.
postgresql.buffer_hithits/secondThe number of times disk blocks were found in the buffer cache, preventing the need to read from the database.
postgresql.commitstransactions/secondThe number of transactions that have been committed in this database.
postgresql.connectionsconnectionsThe number of active connections to this database. If DBM is enabled, this metric is tagged with state, app, db and user
postgresql.database_sizebytesThe disk space used by this database.
postgresql.db.countitemsThe number of available databases.
postgresql.dead_rowsrowsThe estimated number of dead rows.
postgresql.deadlocksThe number of deadlocks detected in this database
postgresql.disk_readblocks/secondThe number of disk blocks read in this database.
postgresql.function.callsThe number of calls made to a function.
postgresql.heap_blocks_hithits/secondThe number of buffer hits in this table.
postgresql.heap_blocks_readblocks/secondThe number of disk blocks read from this table.
postgresql.index_bloatpercentThe estimated percentage of index bloat.
postgresql.index_blocks_hithits/secondThe number of buffer hits in all indexes on this table.
postgresql.index_blocks_readblocks/secondThe number of disk blocks read from all indexes on this table.
postgresql.index_rel_rows_fetchedrows/secondThe number of live rows fetched by index scans.
postgresql.index_rel_scansThe overall number of index scans initiated on this table.
postgresql.index_rows_fetchedrows/secondThe number of live rows fetched by index scans.
postgresql.index_rows_readrows/secondThe number of index entries returned by scans on this index.
postgresql.index_scansThe number of index scans initiated on this table, tagged by index.
postgresql.index_sizebytesThe total disk space used by indexes attached to the specified table.
postgresql.live_rowsrowsThe estimated number of live rows.
postgresql.lockslocksThe number of locks active for this database.
postgresql.max_connectionsconnectionsThe maximum number of client connections allowed to this database.
postgresql.percent_usage_connectionsfractionsThe number of connections to this database as a fraction of the maximum number of allowed connections.
postgresql.queries.countqueriesThe total query execution count per query_signature, db, and user. (DBM only)
postgresql.queries.duration.maxnanosecondsThe age of the longest running query per user, db and app. (DBM only)
postgresql.queries.duration.sumnanosecondsThe sum of the age of all running queries per user, db and app. (DBM only)
postgresql.queries.local_blks_dirtiedblocksTotal number of local blocks dirtied per query_signature, db, and user. (DBM only)
postgresql.queries.local_blks_hitblocksTotal number of local block cache hits per query_signature, db, and user. (DBM only)
postgresql.queries.local_blks_readblocksTotal number of local blocks read per query_signature, db, and user. (DBM only)
postgresql.queries.local_blks_writtenblocksTotal number of local blocks written per query_signature, db, and user. (DBM only)
postgresql.queries.rowsrowsThe total number of rows retrieved or affected per query_signature, db, and user. (DBM only)
postgresql.queries.shared_blks_dirtiedblocksTotal number of shared blocks dirtied per query_signature, db, and user. (DBM only)
postgresql.queries.shared_blks_hitblocksTotal number of shared block cache hits per query_signature, db, and user. (DBM only)
postgresql.queries.shared_blks_readblocksTotal number of shared blocks read per query_signature, db, and user. (DBM only)
postgresql.queries.shared_blks_writtenblocksTotal number of shared blocks written per query_signature, db, and user. (DBM only)
postgresql.queries.temp_blks_readblocksTotal number of temp blocks read per query_signature, db, and user. (DBM only)
postgresql.queries.temp_blks_writtenblocksTotal number of temp blocks written per query_signature, db, and user. (DBM only)
postgresql.queries.timenanosecondsThe total query execution time per query_signature, db, and user. (DBM only)
postgresql.replication.wal_flush_lagsecondsTime 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.
postgresql.replication.wal_replay_lagsecondsTime 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.
postgresql.replication.wal_write_lagsecondsTime 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.
postgresql.replication_delaysecondsThe current replication delay in seconds. Only available with postgresql 9.1 and newer
postgresql.replication_delay_bytesbytesThe current replication delay in bytes. Only available with postgresql 9.2 and newer
postgresql.rollbackstransactions/secondThe number of transactions that have been rolled back in this database.
postgresql.rows_deletedrows/secondThe number of rows deleted by queries in this database
postgresql.rows_fetchedrows/secondThe number of rows fetched by queries in this database
postgresql.rows_hot_updatedrows/secondThe number of rows HOT updated, meaning no separate index update was needed.
postgresql.rows_insertedrows/secondThe number of rows inserted by queries in this database
postgresql.rows_returnedrows/secondThe number of rows returned by queries in this database
postgresql.rows_updatedrows/secondThe number of rows updated by queries in this database
postgresql.seq_rows_readrows/secondThe number of live rows fetched by sequential scans.
postgresql.seq_scansThe number of sequential scans initiated on this table.
postgresql.table.counttablesThe number of user tables in this database.
postgresql.table_bloatpercentThe estimated percentage of table bloat.
postgresql.table_sizebytesThe total disk space used by the specified table. Includes TOAST, free space map, and visibility map. Excludes indexes.
postgresql.temp_bytesbytes/secondThe amount of data written to temporary files by queries in this database.
postgresql.temp_filesfiles/secondThe number of temporary files created by queries in this database.
postgresql.toast_blocks_hithits/secondThe number of buffer hits in this table’s TOAST table.
postgresql.toast_blocks_readblocks/secondThe number of disk blocks read from this table’s TOAST table.
postgresql.toast_index_blocks_hitblocks/secondThe number of buffer hits in this table’s TOAST table index.
postgresql.toast_index_blocks_readblocks/secondThe number of disk blocks read from this table’s TOAST table index.
postgresql.total_sizebytesThe total disk space used by the table, including indexes and TOAST data.
postgresql.transactions.duration.maxnanosecondsThe age of the longest running transaction per user, db and app. (DBM only)
postgresql.transactions.duration.sumnanosecondsThe sum of the age of all running transactions per user, db and app. (DBM only)
postgresql.transactions.idle_in_transactiontransactionsThe number of ‘idle in transaction’ transactions in this database.
postgresql.transactions.opentransactionsThe number of open transactions in this database.
postgresql.vacuumedThe number of times this table has been manually vacuumed.
postgresql.waiting_queriesThe number of waiting queries in this database.
postgresql.wal_agesecondsThe age in seconds of the oldest WAL file.
Rajesh Kumar
Latest posts by Rajesh Kumar (see all)
0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x