Step 2: Create Credential and Attach Databases

Find out Heroku Postgres add-on name

First, find out the add-on name of your database - this is not the same as your application name on Heroku.

heroku addons

This will output something like the following:

Owning App  Add-on                       Plan                           Price        Max price State
─────────── ───────────────────────────  ─────────────────────────────  ──────────── ───────── ───────
testapp     postgresql-objective-11111   heroku-postgresql:essential-0  ~$0.007/hour $5/month  created

Create the pganalyze role

For Heroku Postgres production plans (Standard, Premium, Private, and Shield), we recommend creating a new Heroku credential (role) for your Postgres instance. This allows you to create a specific pganalyze role and apply the recommended read-only monitoring privileges. More details on managing Heroku Postgres credentials can be found in the Heroku documentation.

Using the add-on name from the previous step (postgresql-objective-11111 in the example), and the correct application name, run the following command to create the new credential (named pganalyze):

heroku pg:credentials:create postgresql-objective-11111 --name pganalyze -a testapp

Grant monitoring privileges to the pganalyze role

Now connect to the database server with an admin user using the heroku CLI and grant the recommended predefined role and create the necessary schema.

heroku pg:psql -a testapp

GRANT pg_monitor TO pganalyze;
 
CREATE SCHEMA pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO pganalyze;
GRANT USAGE ON SCHEMA public TO pganalyze;

Create monitoring helper functions

Next, connect to the hosted database that you plan to monitor using heroku pg:psql and the default Heroku admin role (NOT the new pganalyze role) and add the following two functions to enable the collection of additional statistics.

DROP FUNCTION IF EXISTS pganalyze.get_column_stats;
CREATE FUNCTION pganalyze.get_column_stats() RETURNS TABLE(
  schemaname name, tablename name, attname name, inherited bool, null_frac real, avg_width int, n_distinct real, correlation real
) AS $$
  /* pganalyze-collector */
  SELECT schemaname, tablename, attname, inherited, null_frac, avg_width, n_distinct, correlation
  FROM pg_catalog.pg_stats
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND tablename <> 'pg_subscription';
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
 
DROP FUNCTION IF EXISTS pganalyze.get_relation_stats_ext;
CREATE FUNCTION pganalyze.get_relation_stats_ext() RETURNS TABLE(
  statistics_schemaname text, statistics_name text,
  inherited boolean, n_distinct pg_ndistinct, dependencies pg_dependencies,
  most_common_val_nulls boolean[], most_common_freqs float8[], most_common_base_freqs float8[]
) AS
$$
  /* pganalyze-collector */ SELECT statistics_schemaname::text, statistics_name::text,
  (row_to_json(se.*)::jsonb ->> 'inherited')::boolean AS inherited, n_distinct, dependencies,
  most_common_val_nulls, most_common_freqs, most_common_base_freqs
  FROM pg_catalog.pg_stats_ext se
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND tablename <> 'pg_subscription';
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

Attach database

Finally, use that add-on name (postgresql-objective-11111 in the example) to attach the Heroku Postgres database to the collector. Be sure to use the newly created pganalyze credential.

You can utilize the --as parameter to specify an alias for this database, this is in particular useful when you attach multiple databases to the same collector.

heroku addons:attach postgresql-objective-11111 --credential pganalyze -a testapp-pganalyze-collector --as MYDB_PRIMARY

This will output the following:

Attaching postgresql-objective-11111 as MYDB_PRIMARY to ⬢ testapp-pganalyze-collector... done
Setting MYDB_PRIMARY config vars and restarting ⬢ testapp-pganalyze-collector... done, v9

You can now verify the configuration:

Continue to Step 3: Verify configuration

Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →