pg_stat_archiver Enhancement Request

[Rob Brucks <rob.brucks@xxxxxxxxxxxxx>]

I'd like to request the following enhancement to enable better monitoring of a PostgreSQL cluster.

I propose adding a column to "pg_stat_archiver" to determine how many WAL logs are waiting to be archived.  This would enable external monitoring systems to query the status via SQL (without being a superuser) and alert when an excessive number of WAL logs are waiting to be archived. That way intervention can be performed before an out-of-space condition occurs.

I've implemented it as a separate function below, but would rather have it built in to postgres.

Thanks,

Rob Brucks

CREATE OR REPLACE FUNCTION

wal_archives_pending_func(OUT wal_archives_pending int) AS $$

  DECLARE result record;

  BEGIN

    SELECT count(*) as count

      INTO result

      FROM (SELECT pg_ls_dir('pg_xlog/archive_status')) a

     WHERE pg_ls_dir ~ '[0-9A-F]{24}.ready';

    wal_archives_pending := result.count;

  END;

$$

LANGUAGE plpgsql

VOLATILE

SECURITY DEFINER;