Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Wed, Mar 22, 2017 at 1:56 AM, David Steele wrote: > Hi Michael, > > On 3/10/17 9:15 AM, Peter Eisentraut wrote: >> >> On 3/9/17 17:03, Michael Paquier wrote: >>> >>> Having something like --limit-retained-segments partially addresses >>> it, as long as there is a way to

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

Hi Michael, On 3/10/17 9:15 AM, Peter Eisentraut wrote: On 3/9/17 17:03, Michael Paquier wrote: Having something like --limit-retained-segments partially addresses it, as long as there is a way to define an automatic mode, based on statvfs() obviously. But that is not portable/usable enough,

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On 3/9/17 17:03, Michael Paquier wrote: > Having something like --limit-retained-segments partially addresses > it, as long as there is a way to define an automatic mode, based on > statvfs() obviously. But that is not portable/usable enough, as we have determined, I think. Have you looked into

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Thu, Mar 9, 2017 at 10:54 PM, Peter Eisentraut wrote: > On 3/7/17 11:16, Robert Haas wrote: >> Well, if the problem you're trying to solve is "retain WAL for as long >> as possible without running out of disk space and having everything go >> kablooey", then

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On 3/7/17 11:16, Robert Haas wrote: > Well, if the problem you're trying to solve is "retain WAL for as long > as possible without running out of disk space and having everything go > kablooey", then it would solve that problem, and I think that's a very > reasonable problem to want to solve.

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Tue, Mar 7, 2017 at 11:08 AM, Peter Eisentraut wrote: > On 3/6/17 17:16, Robert Haas wrote: >> 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

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On 3/6/17 17:16, Robert Haas wrote: > 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 would be doable, but would it solve

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Tue, Mar 7, 2017 at 7:16 AM, Robert Haas wrote: > On Mon, Mar 6, 2017 at 3:26 PM, Peter Eisentraut > 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

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Mon, Mar 6, 2017 at 3:26 PM, Peter Eisentraut 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

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

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

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Sat, Mar 4, 2017 at 1:09 PM, Peter Eisentraut wrote: > On 2/27/17 00:32, Michael Paquier wrote: >> On Sun, Feb 26, 2017 at 8:24 AM, Michael Paquier >> wrote: >>> To be consistent with archive_command and restore_command I'd rather

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On 2/27/17 00:32, Michael Paquier wrote: > On Sun, Feb 26, 2017 at 8:24 AM, Michael Paquier > wrote: >> To be consistent with archive_command and restore_command I'd rather >> not do that. The command called can decide by itself what to do by >> looking at the shape of

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Sun, Feb 26, 2017 at 10:46 AM, Robert Haas wrote: > On Thu, Feb 23, 2017 at 9:40 PM, Magnus Hagander > 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

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

Sun, Feb 26, 2017 at 12:24 AM, Michael Paquier wrote: > On Sun, Feb 26, 2017 at 12:41 AM, Magnus Hagander > wrote: > > On Feb 25, 2017 15:00, "Michael Paquier" > wrote: > > > > On Sat, Feb 25, 2017 at 10:32 PM, Magnus

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Sun, Feb 26, 2017 at 8:24 AM, Michael Paquier wrote: > To be consistent with archive_command and restore_command I'd rather > not do that. The command called can decide by itself what to do by > looking at the shape of the argument string. Just before the CF begins,

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Fri, Feb 24, 2017 at 11:58 AM, Jim Nasby wrote: > Why not provide % replacements that contain that info? pg_receivexlog has a > much better shot at doing that correctly than some random user tool... > > (%f could certainly be useful for other things) (was

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Thu, Feb 23, 2017 at 9:40 PM, Magnus Hagander 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 -

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Sun, Feb 26, 2017 at 12:41 AM, Magnus Hagander wrote: > On Feb 25, 2017 15:00, "Michael Paquier" wrote: > > On Sat, Feb 25, 2017 at 10:32 PM, Magnus Hagander > wrote: >> Oh, I definitely think such a command should be able

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Feb 25, 2017 15:00, "Michael Paquier" wrote: On Sat, Feb 25, 2017 at 10:32 PM, Magnus Hagander wrote: > Oh, I definitely think such a command should be able to take a placeholder > like %f telling which segment it has just processed. In fact,

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Sat, Feb 25, 2017 at 10:32 PM, Magnus Hagander wrote: > Oh, I definitely think such a command should be able to take a placeholder > like %f telling which segment it has just processed. In fact, I'd consider > it one of the most important features of it :) I cannot think

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Fri, Feb 24, 2017 at 3:52 AM, Michael Paquier wrote: > On Fri, Feb 24, 2017 at 1:10 AM, Magnus Hagander > wrote: > > I'm not sure this logic belongs in pg_receivexlog. If we put the decision > > making there, then we lock ourselves into one

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Fri, Feb 24, 2017 at 3:56 AM, Jim Nasby wrote: > On 2/23/17 8:47 PM, Michael Paquier wrote: > >> Anything else than measured in bytes either requires a lookup at the >> file timestamp, which is not reliable with noatime or a lookup at WAL >> itself to decide when is

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On 2/23/17 9:01 PM, Michael Paquier wrote: An idea here would be to add in the long header of the segment a timestamp of when it was created. This is inherent to only the server generating the WAL. ISTM it'd be reasonable (maybe even wise) for WAL files to contain info about the first and

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Fri, Feb 24, 2017 at 11:56 AM, Jim Nasby wrote: > On 2/23/17 8:47 PM, Michael Paquier wrote: >> >> Anything else than measured in bytes either requires a lookup at the >> file timestamp, which is not reliable with noatime or a lookup at WAL >> itself to decide when is

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On 2/23/17 8:52 PM, Michael Paquier wrote: OK, I forgot a bit about this past discussion. So let's say that we have a command, why not also allow users to use at will a marker %f to indicate the file name just completed? One use case here is to scan the file for the oldest and/or newest

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On 2/23/17 8:47 PM, Michael Paquier wrote: Anything else than measured in bytes either requires a lookup at the file timestamp, which is not reliable with noatime or a lookup at WAL itself to decide when is the commit timestamp that matches the oldest point in time of the backup policy. An

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Fri, Feb 24, 2017 at 1:10 AM, Magnus Hagander 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". > > Wouldn't this one, along with some other scenarios, be better provided by

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Fri, Feb 24, 2017 at 5:37 AM, Jim Nasby wrote: > ISTM what's really needed is a good way for users to handle retention for > both WAL as well as base backups. A tool that did that would need to > understand what WAL is required to safely restore a base backup. It

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Thu, Feb 23, 2017 at 10:54 PM, Stephen Frost wrote: > Micahel, > > * Michael Paquier (michael.paqu...@gmail.com) wrote: >> Is there any interest for a feature like that? I have a non-polished >> patch at hand but I can work on that for the upcoming CF if there are >> voices

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On 2/23/17 10:10 AM, Magnus Hagander wrote: 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

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

On Thu, Feb 23, 2017 at 7:36 AM, Michael Paquier wrote: > Hi all, > > When storing WAL segments on a dedicated partition with > pg_receivexlog, for some deployments, the removal of past WAL segments > depends on the frequency of base backups happening on the server. In

Re: [HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

Micahel, * Michael Paquier (michael.paqu...@gmail.com) wrote: > Is there any interest for a feature like that? I have a non-polished > patch at hand but I can work on that for the upcoming CF if there are > voices in favor of such a feature. The feature could be simply > activated with a

[HACKERS] Automatic cleanup of oldest WAL segments with pg_receivexlog

Hi all, When storing WAL segments on a dedicated partition with pg_receivexlog, for some deployments, the removal of past WAL segments depends on the frequency of base backups happening on the server. In short, once a new base backup is taken, it may not be necessary to keep around those past WAL