Re: Automatic cleanup of oldest WAL segments with pg_receivexlog

On Sun, Feb 26, 2017 at 10:46 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Thu, Feb 23, 2017 at 9:40 PM, Magnus Hagander <magnus(at)hagander(dot)net>
> wrote:
> > I'm not sure this logic belongs in pg_receivexlog. If we put the decision
> > making there, then we lock ourselves into one "type of policy".
>
> That's not really true. We can add other policies - or extensibility
> - later. A more accurate statement, ISTM, would be that initially we
> only support one type of policy. But that's fine; more can be added
> later.
>

Certainly. In that case, though, we need to plan far enough ahead to make
sure the interface in the form of what parameters and such are called is
prepared for that.

We also have to decide if we want pg_receivexlog to be an actual "backup
manager" that way, or whether it should provide the plumbing for something
else to implement it on top of. For example, it seems reasonable that for
*most* usecases you want something that manages both the log archive and
the base backups together, since they have dependencies. And should that
tool really be pg_receivexlog?

> > Wouldn't this one, along with some other scenarios, be better provided by
> > the "run command at end of segment" function that we've talked about
> before?
> > And then that external command could implement whatever aging logic
> would be
> > appropriate for the environment?
>
> I don't think it's bad to have that, but I don't understand the
> resistance to having a policy that by default lets us keep as much WAL
> as will fit within our space budget. That seems like an eminently
> sensible thing to want. Ideally, I'd like to be able to recovery any
> backup, however old, from that point forward to a point of my
> choosing. But if I run out of disk space, removing the oldest WAL
> files I have is more sensible than not accepting new ones. Sure, I'll
> have less ability to go back in time, but I'm less likely to need the
> data from 3 or 4 backups ago than I am to need the most recent data.
> I'm only going to go back to an older backup if I can't recover from
> the most recent one, or if I need some data that was removed or
> corrupted some time ago. It's good to have that ability for as long
> as it is sustainable, but when I have to pick, I want the new stuff.
>

That's going to be very much "depends".

I might be much better off deleting an older base backup. Because deleting
the oldest xlog might render multiple generations of base backups useless.

The point being that there are a lot of different policies that make sense
depending on exactly how you're doing your backups. I'm not saying this one
is not sensible in some cases, but but I don't think it's all that many.

Which is why I think it's better to implement an interface that gives the
flexibility. I'm not saying we can't do both, but once we have command
called at the end of the segment, implementing this policy outside
pg_receivexlog becomes trivial. That does not hold if they are built in the
other order.

I think we're actually desperately in need of smarter WAL management
> tools in core not just in this respect but in a whole bunch of places,
> and I think size is an excellent thing for those tools to be
> considering. When Heikki implemented min_wal_size and max_wal_size
>

I definitely agree we need smarter tools there. Right now we are sending
people to tools like backrest and barman. Which are fine tools, but at some
point we should draw upon the experience of those tools and include
something in core that solves at least the most common cases , kind of like
pg_basebackup solves most of the *backup* cases, and pg_receivexlog most of
the *archive* cases, we need something that solves most of the *management*
cases. We're still going to send people to the external tools for advanced
usecases I think, but right now we have nothing at all in core for helping
people with the simple cases of management.

We also don't have any tools to help people *restore* a backup (other than
a pg_basebackup -x which you can just uncompress -- but if you want to do
PITR you have to do a lot of manual error-prone work)

So don't mistake my opinion for thinking we have sufficient tooling today :)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/