Re: Avoid WAL archiving when idle?

On Wed, Aug 6, 2014 at 8:49 PM, Laurence Rowe <l(at)lrowe(dot)co(dot)uk> wrote:

> I have WAL archiving setup on Postgres 9.3.2 using WAL-E on CentOS 6.4
> using the postgresql.org RPM. This is working fine, except I see a lot of
> spurious activity in the S3 bucket with wal files being backed up every 5
> minutes even when the database is idle. This can make restoring to a dev
> server really slow if it's been a long time since the last base backup. The
> only non-default configuration is:
>
> wal_level = archive
> archive_mode = on
> archive_command = '/usr/local/bin/envdir /etc/wal-e.d/env
> /tools/python/current/bin/wal-e wal-push %p'
> archive_timeout = 60
>
> The 5 minute interval matches the default checkpoint_timeout, so I guess
> I'm seeing the same problem as mentioned here:
> http://www.postgresql.org/message-id/CAMkU=1wCyN7JNOTXCnCqpULtzNfV8ZWH5BqrqZhA+uGB1x-fTA@mail.gmail.com
>
> Is there anyway I can configure PostgreSQL to avoid continuously archiving
> WAL files while idle but still place a limit on the time until a database
> write is archived?
>

I changed guc.c so that I could set to checkpoint_timeout to 100h, and then
set it that high.

Not the ideal solution, perhaps.

Cheers,

Jeff