Database Monitoring for Heroku Postgres

• Setup Database Access for the Agent
• Configure Atatus Infrastructure Agent

​

Before You Begin

​

Setup Database Access for the Agent

1. Create a dedicated Heroku credential for the Agent and attach it to your database:
   heroku pg:credentials:create --name atatus --app <YOUR_APP>
   heroku addons:attach <DATABASE_NAME> --credential atatus --app <YOUR_APP>
   
   This creates a new HEROKU_POSTGRESQL_<COLOR>_URL config var containing the connection URL the Atatus Agent should use.
2. Connect to the database using the new credential and grant monitoring access:
   heroku pg:psql --credential atatus DATABASE_URL --app <YOUR_APP>
   
   CREATE SCHEMA atatus;
   GRANT USAGE ON SCHEMA atatus TO atatus;
   GRANT USAGE ON SCHEMA public TO atatus;
   GRANT pg_monitor TO atatus;
   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
   
3. Create the explain function:
   CREATE OR REPLACE FUNCTION atatus.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;
   

​

Configure Atatus Infrastructure Agent

1. Install the Atatus Infrastructure agent on a host that can reach your Heroku Postgres endpoint.
2. Find the connection details from the credential you created. Run heroku config --app <YOUR_APP> and locate the HEROKU_POSTGRESQL_<COLOR>_URL value matching the atatus credential. The URL has the form:
   postgres://<USERNAME>:<PASSWORD>@<HOST>:<PORT>/<DB_NAME>
   
3. Update the PostgreSQL configuration file at /etc/atatus-infra-agent/conf.d/postgresql.d/postgresql.yml:
   metrics:
     - hosts: ["<HOST>"]
       port: <PORT>
       username: <USERNAME>
       password: <PASSWORD>
       db_name: <DB_NAME>
       ssl: true
       dbm: true
   
   Heroku Postgres requires SSL, so ssl: true is mandatory.
4. Restart the Atatus Infra Agent:
   sudo service atatus-infra-agent restart
   

​

Running the Agent on a Heroku Dyno (Optional)

#!/usr/bin/env bash

if [ -n "$HEROKU_POSTGRESQL_IVORY_URL" ]; then
  PGREGEX='^postgres://([^:]+):([^@]+)@([^:]+):([^/]+)/(.*)$'
  if [[ $HEROKU_POSTGRESQL_IVORY_URL =~ $PGREGEX ]]; then
    sed -i "s/<HOST>/${BASH_REMATCH[3]}/"     "$ATATUS_CONF_DIR/conf.d/postgresql.d/postgresql.yml"
    sed -i "s/<USERNAME>/${BASH_REMATCH[1]}/" "$ATATUS_CONF_DIR/conf.d/postgresql.d/postgresql.yml"
    sed -i "s/<PASSWORD>/${BASH_REMATCH[2]}/" "$ATATUS_CONF_DIR/conf.d/postgresql.d/postgresql.yml"
    sed -i "s/<PORT>/${BASH_REMATCH[4]}/"     "$ATATUS_CONF_DIR/conf.d/postgresql.d/postgresql.yml"
    sed -i "s/<DB_NAME>/${BASH_REMATCH[5]}/"  "$ATATUS_CONF_DIR/conf.d/postgresql.d/postgresql.yml"
  fi
fi

git add atatus/
git commit -m "Enable Atatus Postgres monitoring"
git push heroku main

​

Collecting Custom Metrics (Optional)

metrics:
  - hosts:
      - "<HOST>"
    port: <PORT>
    username: <USERNAME>
    password: <PASSWORD>
    db_name: <DB_NAME>
    ssl: true
    dbm: true
    dbm_postgresql_options:
      additional_metrics_options:
        custom_queries:
          enabled: true
          max_custom_queries: 5
          custom_query:
            - query: |
                SELECT COUNT(*) AS user_count FROM public.users;
              columns:
                - name: user_count
                  type: gauge
              tags:
                - source:custom_user_count