Step 1: Create helper function and test

Log output for Crunchy Bridge database servers is collected directly from the database server using SQL functions.

If you are using the application user, an additional function needs to be created to grant access to the logs:

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;

You can then verify whether this is working by running the test command:

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 quay.io/pganalyze/collector:stable test');

This should show an 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)

Note the "Log test successful", which indicates that the helper method works correctly.

You will start seeing log data in pganalyze Log Insights within a few minutes.

We recommend setting up Log-based EXPLAIN as follow-up step, to automatically EXPLAIN slow queries in Postgres.

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