1

I have configured archive_timeout = 5 (i.e. 5 seconds) for a PostgreSQL 9.6 database in order to conduct tests around point-in-time recovery. My archive_command invokes cp into a local archive directory.

If I check the file modification dates in that local archive directory I see that WAL files are typically created in there only approx. every 20 seconds apart, i.e. far more than the configured 5 seconds. This is for PostgreSQL 9.6 running inside a Docker container on a lightly loaded system.

What could explain this large discrepancy?

1

The documentation has an answer to this:

When this parameter is greater than zero, the server will switch to a new segment file whenever this many seconds have elapsed since the last segment file switch, and there has been any database activity, including a single checkpoint (checkpoints are skipped if there is no database activity).

Based on your observation and the above quote, I guess you have a checkpoint every 20 seconds or so, and a very low rate of changes.

1
  • Yep, I guess that's true, the and in the cited documentation is important. Your answer is also backed up by this comment: "pedantically, archive_timeout will only trigger archive_command if required (aka there was database activity)". – rookie099 Jan 5 '18 at 7:05

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.