Datadog Integration with Postgres For Metric, Trace & Log

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:

• Author
• Recent Posts

Follow me

Rajesh Kumar

Mentor for DevOps - DevSecOps - SRE - Cloud - Container & Micorservices at Software AG

Join my following certification courses...
- DevOps Certified Professionals (DCP)
- Site Reliability Engineering Certified Professionals (SRECP)
- Master in DevOps Engineering (MDE)
- DevSecOps Certified Professionals (DSOCP)
URL - https://www.devopsschool.com/certification/

My Linkedin - https://www.linkedin.com/in/rajeshkumarin

Follow me

Latest posts by Rajesh Kumar (see all)

• Best AI tools for Software Engineers - November 4, 2024
• Installing Jupyter: Get up and running on your computer - November 2, 2024
• An Introduction of SymOps by SymOps.com - October 30, 2024