Re: Automatic cleanup of oldest WAL segments with pg_receivexlog

On Tue, Mar 7, 2017 at 7:16 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Mar 6, 2017 at 3:26 PM, Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>> On 3/4/17 02:09, Michael Paquier wrote:
>>> Well, that's one reason why I was thinking that having an independent
>>> in-core option to clean up the tail of the oldest segments is
>>> interesting: users don't need to maintain their own infra logic to do
>>> anything. Now this end-segment command can as well be used with a
>>> small binary doing this cleanup, but the monitoring of the thing gets
>>> harder as multiple processes get spawned.
>>
>> I think the initial idea of having an option that does something
>> specific is better than an invitation to run a general shell command. I
>> have some doubts that the proposal to clean up old segments based on
>> file system space is workable. For example, that assumes that you are
>> the only one operating on the file system. If something else fills up
>> the file system, this system could then be induced to clean up
>> everything immediately, without any reference to what you still need.
>> Also, the various man pages about statvfs() that I found are pretty
>> pessimistic about how portable it is.

You can count Windows in that.

> What if we told pg_receivewal (or pg_receivexlog, whatever that is) a
> maximum number of segments to retain before removing old ones? Like
> pg_receivewal --limit-retained-segments=50GB, or something like that.

That's of course doable as well by counting the entries available. Now
one reason why I did not do that is because in my case the archiver is
started as a service using a fixed script, and the size to retain can
be flexible as users can decide the VM size at deployment :)
Having this option would be better than nothing, just that it is not
that flexible if you think about it.
--
Michael