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 no
database activity

* http://archives.postgresql.org/pgsql-hackers/2010-01/msg01469.php
* http://archives.postgresql.org/pgsql-hackers/2010-02/msg00395.php

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +