Postgres Pro
Downloads
Home   >   mailing lists

Re: Avoid WAL archiving when idle? - Mailing list pgsql-general

From Jeff Janes
Subject Re: Avoid WAL archiving when idle?
Date
Msg-id CAMkU=1xMFX7gP4e_5_NjJ9Xb7A=dRi_H3_bLAtvy3ZHOATe+Ug@mail.gmail.com
Whole thread Raw
In response to Avoid WAL archiving when idle?  (Laurence Rowe <l@lrowe.co.uk>)
List pgsql-general
Tree view
On Wed, Aug 6, 2014 at 8:49 PM, Laurence Rowe <l@lrowe.co.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

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: dump/restore with a hidden dependency?
Next
From: AI Rumman
Date:
Subject: not finding rows using ctid