Re: archive_timeout behavior for no activity

Kevin Grittner wrote:
> [resend, because of apparent failure to hit the list]
> 
> Bruce Momjian  wrote: 
>  
> > I am dismayed that we are using a 16MB file for monitoring archive
> > activity.  Can't you use pg_current_xlog_location() and only check
> > for an archive file when that location changes?
>  
> Hmmm....  Let me think about that.  The intent was to check the
> end-to-end health of the PITR backups.  The current process ensures
> that the archive command is working, the crontab scripts to copy the
> files are working (they get copied from the database server to
> multiple locations), and that the one copied to our central location
> applies cleanly to a warm standby (thereby providing confirmation of
> the health of that process).  I'd have to think about how much we
> would lose with the change you suggest, and how much we'd care about
> that.  I guess in a pinch we could always use a crontab job to force
> something to the WAL files periodically, but now that the system is
> proven and "settled in", perhaps constant validation of some of
> those points is overkill.
>  
> By the way a near-empty WAL file is only 16KB by the time
> pg_clearxlogtail and gzip get done chewing on it, and we have a
> parallel stream of data from our application which allows us to keep
> the archive frequency to once per hour.  Not everyone is going to be
> in this position, though, so I can understand the motivation to
> change it.
>  
> > Is there a TODO here?
>  
> Well, if I'm the only one who likes the status quo, I'm not sure
> that should preclude a change that would benefit others.  I'm sure
> we can code around it one way or another.  Perhaps some of the new
> monitoring functions in 9.0 will help.  I'll have to take a look.
>  
> Seriously, if there would be significant benefit to others, don't
> let me be the spoiler here; we'll sort it out.

Seems there is enough concern about the existing behavior that I have
added a TODO item:
Consider avoiding WAL switching via archive_timeout if there has been nodatabase activity    *
http://archives.postgresql.org/pgsql-hackers/2010-01/msg01469.php   *
http://archives.postgresql.org/pgsql-hackers/2010-02/msg00395.php
 

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +