Re: WAL archiving idle database

On Fri, 2007-10-26 at 14:39 -0700, Jeff Davis wrote:
> On Fri, 2007-10-26 at 15:08 -0600, Brian Wipf wrote:
> > I have a test PG 8.2.5 installation that has been left idle with no
> > connections to it whatsoever for the last 24 hours plus. WALs are
> > being archived exactly 5 minutes apart, even though archive_timeout
> > is set to 60. Is this the expected behavior for a database with no
> > changes?
> >
>
> If it's set to just "60" that means 60 seconds.
>
> What's happening is that you have a checkpoint_timeout of 5 minutes, and
> that checkpoint must write a checkpoint record in the WAL, prompting the
> archiving.

archive_timeout is the maximum time to wait for a log switch that
contains newly written WAL. That interval is not the same thing as how
often WAL records are written.

On the idle server a checkpoint is being written every
checkpoint_timeout. Then archive_timeout kicks in 60 seconds later,
switches the log which then archives that file. Then four minutes later
a checkpoint occurs, sees that there is no immediately preceding
checkpoint because of the log switch and writes a new checkpoint record.
Then 60 seconds later... Overall this produces one WAL file every
checkpoint_timeout during idle times, yet without relaxing the guarantee
that WAL will be archived every archive_timeout seconds.

We *could* force it to perform a log switch whether or not new WAL has
been written. If that's what people think is wanted. I'd seen the
behaviour as beneficial up til now.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com