7

We set our max_wal_size to 24 GB recently (from the 1 GB default), did some testing, and then set it to 12 GB followed by a server restart. When I query the size of the WAL on the filesystem (total size of files in pg_xlog directory), it still shows as about 20 GB. I have issued a manual checkpoint and restarted the server, but the WAL hasn't shrunk back down to 12 GB. This is a pretty simple implementation -- no replication nor archiving in place and there are no long-running transactions that exist. Am I misunderstanding how this is supposed to work? Does it not delete the old WAL files when you lower the max_wal_size value and when all transactions have been been completed and the server restarts?

Postgres version: 9.6.15

OS: Linux Ubuntu 16.04.6 LTS

Settings:

max_wal_size = 12GB

min_wal_size = 80MB

wal_keep_segments = 0

checkpoint_timeout = 15min

checkpoint_completion_target = 0.87

wal_compression = off

archive_mode = off

wal_level = minimal

Improve this question

1 Answer 1

Reset to default
7

It looks at "max_wal_size" to decide if WAL freed up by the just-finished checkpoint should be recycled or deleted. But WAL files that have already been recycled (renamed in anticipation of future re-use) in prior checkpoints while "max_wal_size" was high will not get deleted when "max_wal_size" is lowered. They will eventually work their way out of the system as they get reused and then deleted (rather then recycled again).

If you can't wait for that to happen, you will have to delete them manually, which is a rather perilous thing to do--if you mess it up you will destroy your database.

Improve this answer
3
  • Thanks for the insight on this.Very helpful. Definitely will not attempt perilous deletes! Just wanted to better understand the behavior and what to expect. Thanks again.
    – DBEnthusiast
    Sep 18, 2019 at 19:34
  • 1
    Essentially, the more data modification activity you generate, the faster pg_xlog (pg_wal in recent versions) will shrink.
    – Laurenz Albe
    Sep 19, 2019 at 5:53
  • And I can confirm now that it has shrunk down to 10.7GB after running some additional data modification queries.
    – DBEnthusiast
    Sep 19, 2019 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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