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;