Database Monitoring for Heroku Postgres

Atatus Database Monitoring offers comprehensive monitoring for your Heroku Postgres databases, providing valuable insights into query performance, query samples, execution plans, connection details, and system statistics.

Because Heroku does not give you direct host access, the Atatus Infra Agent runs on a separate machine (an EC2/GCE VM, a Heroku Dyno running the Atatus buildpack, etc.) and connects to the Heroku Postgres endpoint over SSL.

• 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:

   copy

   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:

   copy

   heroku pg:psql --credential atatus DATABASE_URL --app <YOUR_APP>
   

   copy

   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:

   copy

   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:

   copy

   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:

   copy

   sudo service atatus-infra-agent restart
   

Running the Agent on a Heroku Dyno (Optional)

If you prefer to run the Atatus Infra Agent on a Heroku Dyno via the Atatus buildpack, you can derive the agent configuration from the HEROKU_POSTGRESQL_<COLOR>_URL environment variable at release time. Place a prerun script at atatus/prerun.sh (replace IVORY with your credential's color):

#!/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

Deploy:

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