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 superuser credentials:

GRANT pg_monitor TO application;

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 and extended statistics:

CREATE SCHEMA IF NOT EXISTS pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO application;

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;

Note: We never collect actual table data through this method (see the NULL values in the get_column_stats function and omitted fields like most_common_vals in the get_relation_stats_ext function), but we do collect statistics about the distribution of values in your tables. You can skip creating the get_column_stats and get_relation_stats_ext helper functions 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;

Create Crunchy Bridge API key

Providing the Crunchy Bridge API key to the collector will allow the collector to obtain accurate Storage Space information, as well as cluster information. It is recommended to create a new API key for the collector and passing it as an environment value. See the Crunchy Bridge documentation for how to create API keys.

Run collector test

Run the following command to verify the configuration. For the DB_URL, specify the database URL to monitor. For the CRUNCHY_BRIDGE_API_KEY, specify the API key created above. Before running this command, you can run \t \a to show the result in a better format.

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

This should return output like this:

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] Submitted full snapshot successfully
I [default] Testing activity snapshots...
I [default] Testing log download...
E [default] ERROR - Could not download logs: could not collect logs: LogReadSql/QueryRow: pq: permission denied for function pg_read_file

...Test summary

Test successful

Note that Log Insights will show error until you complete the setup step of Log Insights.

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 CRUNCHY_BRIDGE_API_KEY="REPLACE_ME" -e DB_ALL_NAMES=1 --privileged 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.