pg_stat_archiver Enhancement Request

From: Rob Brucks <rob(dot)brucks(at)rackspace(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: pg_stat_archiver Enhancement Request
Date: 2016-04-22 16:30:03
Message-ID: A4EE437B-497E-4880-AA32-2A495C4D26CD@rackspace.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

Browse pgsql-general by date

  From Date Subject
Next Message Yury Zhuravlev 2016-04-22 16:42:07 Re: I need testers for incremental backups (similar Oracle)
Previous Message John R Pierce 2016-04-22 15:54:04 Re: I need testers for incremental backups (similar Oracle)