[GENERAL] pg_stat_archiver Enhancement Request

[Rob Brucks]

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;