Reading logs directly from Postgres

In some environments, it's possible to access Postgres logs by reading them directly through the Postgres pg_read_file function. However, this function is restricted to Postgres superusers, and since we recommend connecting to your database from the collector with a restricted monitoring user, it's not directly available. To work around this, we recommend creating the following helper function as a superuser (note this needs to be an actual superuser, i.e. not available on systems like Amazon RDS) and granting access to the monitoring user (here we assume that user is called "pganalyze"):

CREATE SCHEMA IF NOT EXISTS pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO pganalyze;
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;

After creating the helper function, re-run the collector test to confirm logs can be accessed.