Show archive lag (WAL not yet archived)

Command

SELECT last_archived_wal, last_failed_wal, archived_count, failed_count, pg_walfile_name(pg_current_wal_lsn()) AS current_wal FROM pg_stat_archiver;

What it does

Shows archive lag by comparing the last archived WAL file, the WAL currently being written and the archiving failure counters.

When to use it

To monitor whether archive_command is falling behind and to catch archiving failures.

Caution

If failed_count keeps increasing, archive_command is failing and pg_wal can fill up; act immediately to prevent the disk from overflowing.