Re: Avoid WAL archiving when idle?

Laurence Rowe 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@.gmail
>
> 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?
>
> Laurence

The better solution would be for the checkpointer to simply not checkpoint
if there is nothing to write out. It should not require manual
configuration since as soon as anything gets written to the WAL the maximum
delay would kick in and in the absence of anything to archive no time period
would make sense.

I'm do not believe there is anything you can do currently - the most likely
recommendation is that you simply need to perform the base backup more
frequently - possibly during those long idle periods you mention. Keeping
in mind your idea of "idle" and Postgres' may differ...

But I do agree that this functionality would make using PostgreSQL in a
small-ish scale environment more user friendly in cases where streaming
replication isn't a requirement.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Avoid-WAL-archiving-when-idle-tp5813992p5813999.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.