Step 1: Deploy the Collector

In order to monitor a Crunchy Bridge database you run the pganalyze collector directly on your database server using Postgres Container Apps.

Grant pg_monitor role and log file access to the application user

We recommend using a role with limited privileges for monitoring. You can use Crunchy Bridge's application user as the user for monitoring, or create a separate role. The instructions here assume you are using the application user.

To ensure the application user can access monitoring statistics for all users (not just itself), and can read the server log, run the following with the superuser credentials:

GRANT pg_monitor TO application;

Additionally, run the following to allow the application user to read the Postgres server log:

CREATE SCHEMA IF NOT EXISTS pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO application;
CREATE OR REPLACE FUNCTION pganalyze.read_log_file(log_filename text, read_offset bigint, read_length bigint) RETURNS text AS
$$
DECLARE
  result text;
BEGIN
  IF log_filename !~ '\A[\w\.-]+\Z' THEN
    RAISE EXCEPTION 'invalid log filename';
  END IF;

  SELECT pg_catalog.pg_read_file(
    pg_catalog.current_setting('data_directory') || '/' || pg_catalog.current_setting('log_directory') || '/' || log_filename,
    read_offset,
    read_length
  ) INTO result;

  RETURN result;
END
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

Then, connect to each database that you plan to monitor on this server as a superuser (or equivalent) and run the following to enable the collection of additional column statistics:

CREATE SCHEMA IF NOT EXISTS pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO application;
CREATE OR REPLACE FUNCTION pganalyze.get_column_stats() RETURNS SETOF pg_stats AS
$$
  /* pganalyze-collector */ SELECT schemaname, tablename, attname, inherited, null_frac, avg_width,
    n_distinct, NULL::anyarray, most_common_freqs, NULL::anyarray, correlation, NULL::anyarray,
    most_common_elem_freqs, elem_count_histogram
  FROM pg_catalog.pg_stats;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

Note: We never collect actual table data through this method (see the NULL values in the function), but we do collect statistics about the distribution of values in your tables. You can skip creating the get_column_stats helper function if the database contains highly sensitive information and statistics about it should not be collected. This will impact the accuracy of Index Advisor recommendations.

In later steps you can now specify the application user credentials as the DB_URL.

Enable Postgres Container Apps

If you have not yet enabled Postgres Container Apps, connect to your Crunchy Bridge Postgres database, and run the following commands as a superuser:

CREATE EXTENSION IF NOT EXISTS pgpodman;

Run collector test

Run the following command to verify the configuration. For the DB_URL specify the application user credentials.

SELECT run_container('-t -e DB_URL="REPLACE_ME" -e PGA_API_KEY="API_KEY" -e DB_ALL_NAMES=1 quay.io/pganalyze/collector:stable test');

This should return output like this:

                                run_container                                   
--------------------------------------------------------------------------------
 I Running collector test with pganalyze-collector X.XX.X
 I [default] Testing statistics collection...
 I [default]   Test collection successful for PostgreSQL XX.X
 I [default]   Test snapshot successful (27.9 KB)
 I [default] Testing activity snapshots...
 I [default]   Test snapshot successful (2.28 KB)
 I [default] Testing log download...
 I [default]   Test snapshot successful (1.45 KB)
 I [default]   Log test successful

 Test successful. View servers in pganalyze:
  - [default]: https://app.pganalyze.com/servers/SERVER_ID

(1 row)

Start collector in the background

With a successful test, you can now start the collector container in the background:

SELECT run_container('-d -t -e DB_URL="REPLACE_ME" -e PGA_API_KEY="API_KEY" -e DB_ALL_NAMES=1 quay.io/pganalyze/collector:stable');

Once you've confirmed the install is successful and you're receiving query data in pganalyze, we recommend setting up Log Insights as a follow-up step, to automatically track log events in your database.

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