I have a PostgreSQL 8.4 installation, and I have configured the archive_timeout setting to 5 minutes to improve the odds that I always have complete WAL file to apply in case of system failure.

However, this creates a LOT of big files, even if nothing has changed! Is there a way to tell PostgreSQL to only create these WAL files at the timeout interval IF something has changed?

  • 1
    This has been addressed in up-to-date versions of Postgres (especially in Postgres 10) – a_horse_with_no_name Mar 8 '18 at 8:06

The archive_timeout and the checkpoint_timeout feed off each other in such a way that you will get a WAL switch once per whichever of those two settings is greater, even on a system that is otherwise idle. So you can increase checkpoint_timeout to the maximum value (1 hour) to cut down on the idle log generation by a factor of 12. The problem is that a checkpoint creates a record that needs to be archived, and the archival creates traffic that needs to be checkpointed (or at least, that PostgreSQL can't prove to itself doesn't need to be checkpointed)

There are a variety of ways around this, but most of them only work if you are running something less ancient than version 8.4.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.