--- isPrivate: true title: Setting up Heroku Postgres for Database Monitoring description: Datadog, the leading service for cloud-scale monitoring. breadcrumbs: >- Docs > Database Monitoring > Setting up Postgres > Setting up Heroku Postgres for Database Monitoring --- # Setting up Heroku Postgres for Database Monitoring This guide assumes that you have configured the [Datadog Heroku buildpack](https://docs.datadoghq.com/agent/basic_agent_usage/heroku/) in your application dynos. [Datadog Database Monitoring](https://www.datadoghq.com/product/database-monitoring/) allows you to view query metrics and explain plans from all of your databases in a single place. This guide covers how to set up Database Monitoring for a [Heroku Postgres managed database](https://devcenter.heroku.com/articles/heroku-postgresql). *Note*: Only databases in the [Standard and Premium plans](https://devcenter.heroku.com/articles/heroku-postgres-plans) publish metrics used by the integration. Not all the features of Database Monitoring are available when used with a Postgres instance in the Hobby plan. ## Preparing the Postgres Database{% #preparing-the-postgres-database %} First, create a `datadog` user in your database: ```shell # Ensure that you are in the root directory of the application heroku pg:credentials:create --name datadog # Attach the new credential to the application heroku addons:attach --credential datadog ``` Attaching the new credential to the application creates a new environment variable in your application with the connection URL. Note that environment variable, as you will use it later. Login to your Postgres database using the default credentials and give the `datadog` credential the right permissions: ```shell heroku pg:psql ``` Once in the psql terminal, create the following schema: ```sql 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; ``` Create the following function in the database: ```sql CREATE OR REPLACE FUNCTION datadog.explain_statement( l_query TEXT, OUT explain JSON ) RETURNS SETOF JSON AS $$ DECLARE curs REFCURSOR; plan JSON; BEGIN OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query); FETCH curs INTO plan; CLOSE curs; RETURN QUERY SELECT plan; END; $$ LANGUAGE 'plpgsql' RETURNS NULL ON NULL INPUT SECURITY DEFINER; ``` ## Configuring the Postgres integration{% #configuring-the-postgres-integration %} Next, configure the Datadog agent to enable the Postgres integration, using one of the following two options. **Option A**: Use a buildpack to create a static Postgres configuration that cannot be modified. In addition, Database Monitoring may be enabled through this method. **Option B**: Create a custom Postgres configuration with the ability to enable additional features that aren't available through the static configuration in Option A. {% tab title="Option A: Static Configuration" %} ### Static Configuration{% #static-configuration %} To enable the Postgres integration to collect standard metrics, set `DD_ENABLE_HEROKU_POSTGRES` to true, then rebuild the application: ```shell heroku config:set DD_ENABLE_HEROKU_POSTGRES=true git commit --allow-empty -m "enabled postgres integration" git push heroku main ``` To enable both the Postgres integration and Database Monitoring, set `DD_ENABLE_HEROKU_POSTGRES` and `DD_ENABLE_DBM` to true: ```shell heroku config:set DD_ENABLE_HEROKU_POSTGRES=true heroku config:add DD_ENABLE_DBM=true git commit --allow-empty -m "enabled postgres integration with DBM" git push heroku main ``` The Postgres integration and, if enabled, Database Monitoring, will begin collecting metrics. {% /tab %} {% tab title="Option B: Custom Configuration" %} ### Custom Configuration{% #custom-configuration %} {% alert level="danger" %} **Important**: If you tried Option A first and need to remove the `DD_ENABLE_HEROKU_POSTGRES` and `DD_ENABLE_DBM` configurations, use the commands below: ```shell heroku config:unset DD_ENABLE_HEROKU_POSTGRES heroku config:unset DD_ENABLE_DBM ``` {% /alert %} First, find the connection string for the Datadog user to connect to Postgres by running `heroku config` and locating the `HEROKU_POSTGRESQL__URL` variable. The `` component of the variable name differs by user. In the sample output below, the `` component is `IVORY`. Copy or note the connection string found in your `HEROKU_POSTGRESQL__URL` variable. ```bash === immense-scrubland-xxxxx Config Vars DATABASE_URL: postgres://:@:/ DD_API_KEY: ***** DD_DYNO_HOST: true DD_SITE: datadoghq.com HEROKU_APP_DEFAULT_DOMAIN_NAME: immense-scrubland-xxxxx.herokuapp.com HEROKU_APP_ID: 9159bc31-f54f-xxxxx-99fc-876f51bfea94 HEROKU_APP_NAME: immense-scrubland-xxxxx HEROKU_POSTGRESQL_IVORY_URL: postgres://:@:/ HEROKU_RELEASE_CREATED_AT: 2024-10-23T19:18:24Z HEROKU_RELEASE_VERSION: v17 HEROKU_SLUG_COMMIT: 383c7b6105fe2a11baeddb9b75703eb1660dd519 HEROKU_SLUG_DESCRIPTION: Deploy 383c7b61 ``` In the root of the project, create a directory for the Postgres configuration called `datadog/conf.d/postgres.d`, containing a file called `conf.yaml`: ```shell mkdir -p datadog/conf.d/postgres.d touch datadog/conf.d/postgres.d/conf.yaml ``` Add the following to `conf.yaml`: ```yaml init_config: instances: - dbm: true host: port: username: password: dbname: ssl: True ``` To manually locate the correct values for the placeholders in the YAML file, follow the Manual Setup. To programmatically replace them, follow the Prerun Script instructions. {% collapsible-section %} ##### Manual Setup #### Manual Setup{% #manual-setup %} Locate the`HEROKU_POSTGRESQL__URL` connection string from above. If you need to locate the string again, run `heroku config`. The connection string follows the structure: `postgres://:@:/`. Using that structure, replace the `conf.yaml` placeholders, save the file, and redeploy the Heroku application and agent with the commands below: ```shell git add . git commit --allow-empty -m "enable postgres integration" git push heroku main ``` {% /collapsible-section %} {% collapsible-section %} ##### Prerun Script #### Prerun Script{% #prerun-script %} Using a [prerun script](https://docs.datadoghq.com/agent/basic_agent_usage/heroku/#prerun-script), you can programatically replace the placeholder `conf.yaml` values before starting the Datadog Agent. If you don't have a prerun script yet, create a shell script called `prerun.sh` in the `datadog/` directory in project root, and add the script below. **Note:** In the below example, the Datadog user connection variable in the Heroku configuration is called `HEROKU_POSTGRESQL_IVORY_URL`. Replace `IVORY` with the component that appears as part of your connection variable. ```shell #!/usr/bin/env bash # Update the Postgres configuration using the Heroku application environment variable if [ -n "$HEROKU_POSTGRESQL_IVORY_URL" ]; then POSTGREGEX='^postgres://([^:]+):([^@]+)@([^:]+):([^/]+)/(.*)$' if [[ $HEROKU_POSTGRESQL_IVORY_URL =~ $POSTGREGEX ]]; then sed -i "s//${BASH_REMATCH[3]}/" "$DD_CONF_DIR/conf.d/postgres.d/conf.yaml" sed -i "s//${BASH_REMATCH[1]}/" "$DD_CONF_DIR/conf.d/postgres.d/conf.yaml" sed -i "s//${BASH_REMATCH[2]}/" "$DD_CONF_DIR/conf.d/postgres.d/conf.yaml" sed -i "s//${BASH_REMATCH[4]}/" "$DD_CONF_DIR/conf.d/postgres.d/conf.yaml" sed -i "s//${BASH_REMATCH[5]}/" "$DD_CONF_DIR/conf.d/postgres.d/conf.yaml" fi fi ``` Deploy to Heroku: ```shell git add . git commit -m "Enable postgres integration" git push heroku main ``` {% /collapsible-section %} {% /tab %} The database connection is now configured. To enable additional features, such as [schema collection](https://docs.datadoghq.com/database_monitoring/schema_explorer), refer to the options available in the Postgres [conf.yaml.example](https://github.com/DataDog/integrations-core/blob/master/postgres/datadog_checks/postgres/data/conf.yaml.example) file.