Re: Limiting WAL retained for archiving, like max_slot_wal_keep_size

Hi João,

What do people think about a setting like max_slot_wal_keep_size, but
> for archiving? Off by default, and once it's exceeded the server
> would let the oldest unarchived segments go and record that it did,
> maybe in pg_stat_archiver, so tools can notice the gap and take a new
> base backup.
>

Thanks for raising this. I understand the problem is real, a stuck
archive_command filling pg_wal and taking the primary down is a painful way
to find out archiving broke.
Still, I'm not convinced the server should drop an unarchived WAL on its
own, even as an opt-in.

A few concerns:
1. A gap left by dropped segments can go unnoticed at restore time. With a
recovery target past the gap, recovery fails loudly ("recovery ended before
configured recovery target was reached").
But with no target, restore_command failing on the missing segment looks
like the normal end of the archive.
Recovery stops there and promotes without complaint, and everything after
the gap is gone.

2. pg_backup_stop() would report success for a broken backup. Looking at
the code pg_backup_stop() waits on XLogArchiveIsBusy() only for backup's
last segment, assuming
segments are archived in order, and XLogArchiveIsBusy() treats a segment
missing from pg_wal as already archived. A backup whose WAL range includes
dropped segments would finish normally and be
unusable.

3. pg_stat_archiver can't be a reliable place to record the gap. It is
discarded after crash recovery and can be reset with pg_stat_reset_shared().
The crash most likely in this situation is the one where pg_wal fills up,
so the record of the gap could be lost right when it matters. A durable
record would need something like a WAL record marking the skipped range, so
that tools reading the archive can see it, or state in pg_control that
pg_backup_stop() can check. That is a much bigger change than adding a GUC.

4. The archiver would see dropped segments as orphaned .ready files. Today
it removes those with a WARNING that reads like crash
leftovers, so a deliberate skip and a crash would look the same unless that
path changes too.

I think we can get most of the benefits with less risk. For example,
pg_stat_archiver shows failures, but not how far behind archiving is.
Adding the oldest unarchived segment, or the
bytes waiting to be archived, would let people alert well before the disk
fills. That seems useful on its own.

Regards,
Surya Poondla