Re: WAL archiving idle database

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.

If you want it to happen less frequently, it's often safe to have
checkpoint timeout set to something larger by a reasonable amount.

Anyone using a checkpoint_timeout is going to end up with quite a few
mostly-empty 16MB files to deal with. Someone wrote a utility to zero
out the empty space in WAL segments, you might look at
"pg_clearxlogtail" written by Kevin Grittner (search the archives or
pgfoundry). This allows you to gzip the files to basically nothing.

Regards,
Jeff Davis