0

How can I efficiently delete thousands of WAL (Write-Ahead Logging) files in the /wal directory in a PostgreSQL database to free up disk space while ensuring the integrity of the database?

-rw------- 1 postgres postgres 16777216 Feb 5 14:11 000000020000000B0000004D

-rw------- 1 postgres postgres 16777216 Feb 6 03:13 000000020000000B0000004E

-rw------- 1 postgres postgres 16777216 Feb 6 07:57 000000020000000B0000004F

I have a broken backup because the stanza keeps showing me this error.

ERROR: [082]: WAL segment 000000020000000B00000093 was not archived before the 60000ms timeout

2 Answers 2

2

It looks like the archiver has a problem. Set archive_mode = off and restart PostgreSQL, then it will delete the WAL segments automatically after the next checkpoint. You can trigger a checkpoint with the CHECKPOINT command if you don't want to wait.

Never, ever, delete any files in pg_wal yourself.

3
  • So you're saying that if I set archive_mode=off and restart PostgreSQL, all WAL files will be automatically deleted?
    – Jan Kůst
    Feb 6 at 11:32
  • After then next checkpoint yes. Except the WAL segments that cannot yet be deleted. Feb 6 at 11:43
  • Thx! It works!!
    – Jan Kůst
    Feb 6 at 14:07
0

It sounds like your archiving setup is broken.

https://www.postgresql.org/docs/16/wal-configuration.html

Also, if WAL archiving is used, old segments cannot be removed or recycled until they are archived. If WAL archiving cannot keep up with the pace that WAL is generated, or if archive_command or archive_library fails repeatedly, old WAL files will accumulate in pg_wal until the situation is resolved.

So - you either need to fix your wal archiving or turn it off (remove the archive_command). Obviously you will then want to get your backups working again as soon as possible.

Your Answer

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

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