Re: archive_timeout behavior for no activity

[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.

-Kevin

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. +