Re: WAL "low watermark" during base backup

On Mon, Sep 5, 2011 at 11:38 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Sun, Sep 4, 2011 at 19:02, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> On Fri, Sep 2, 2011 at 6:52 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>>
>>> Attached patch implements a "low watermark wal location" in the
>>> walsender shmem array. Setting this value in a walsender prevents
>>> transaction log removal prior to this point - similar to how
>>> wal_keep_segments work, except with an absolute number rather than
>>> relative. For now, this is set when running a base backup with WAL
>>> included - to prevent the required WAL to be recycled away while the
>>> backup is running, without having to guestimate the value for
>>> wal_keep_segments. (There could be other ways added to set it in the
>>> future, but that's the only one I've done for now)
>>>
>>> It obviously needs some documentation updates as well, but I wanted to
>>> get some comments on the way it's done before I work on those.
>>
>> I'm not yet fully available for a discussion on this, but not sure I like this.
>>
>> You don't have to guess the setting of wal_keep_segments, you
>> calculate it exactly from the size of your WAL disk. No other
>> calculation is easy or accurate.
>
> Uh, no. What about the (very large number of) cases where pg is just
> sitting on one partition, possibly shared with a whole lot of other
> services? You'd need to set it to all-of-your-disk, which is something
> that will change over time.
>
> Maybe I wasn't entirely clear in the submission, but if it wasn't
> obvious: the use-case for this is the small and simple installations
> that need a simple way of doing a reliable online backup. This is the
> "pg_basebackup -x" usecase altogether - for example, anybody "bigger"
> likely has archiv elogging setup already, in which case this
> functionality is not interesting at all.

I understand the need for a reliable backup, problem is they won't get
one like this.

If your disk fills, the backup cannot end correctly, so you must
somehow avoid the disk filling while the backup is taken.

Removing the safety that prevents the disk from filling doesn't
actually prevent it filling.

If you must have this then make pg_basebackup copy xlog files
regularly during the backup. That way your backup can take forever and
your primary disk won't fill up. In many cases it actually will take
forever, but at least we don't take down the primary.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services