So I inherited this database. At the very beginning, there were already some files on pg_wal.

Then, after some weeks, I do count SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';, it results : 8182 files.

Looking at the file's timestamp on pg_wal directory, I see some new files being created but the number of files stays the same. I am suspecting that the rotation takes place.

Then I observe the pg_stat_archiver : archived_count = **2**; failed_count = 0

Some 3 hours later, I do count again SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}'; It results : 8183. Obviously a new file is created.

Then I do pg_stat_archiver : archived_count = **3**; failed_count = 0

archived_count last_archived_wal           last_archived_time   failed_count    last_failed_wal  last_failed_time 
3              000000010000009B00000083    22-09-13 13:14:20.   0
2022-09-13 08:14:19

This database is very low, not busy at all. Sometimes no one access it. But there is logical replication that sync some 300 records once a day to another server.

Here is my settings :

max_wal_size : 1024 Mb
min_wal_size : 2048 Mb
wal_keep_segment : 0
wal_rotation : ON
wal_level : logical
wal_compression : ON
archive_on : ON
archive_mode : test ! -f /Archive/%f && cp %p /Archive/%f
archive_timeout : 900

Questions :

  1. Why does the second count of pg_wal increase? I am expecting that it decreases because the archived number increases.
  2. If wal_keep_segment = 0, why the number of WALs in pg_wal never 0 ? In the documentation, it says :

If wal_keep_segments is zero (the default), the system doesn't keep any extra segments for standby purposes,

  1. Why the minimum number of WALs, during low period, does not = min_wal_size + wal_keep_segment?

Please enlighten me.

Thank you so much

  • 1
    Look at the actual file names and time stamps using pg_ls_waldir(), or directly using OS tools. Maybe there is a bunch of garbage left over from some long-ago misadventure that never got cleaned up.
    – jjanes
    Sep 13, 2022 at 18:15
  • yes. there were already files on pg_wal even with archive mode = off. Its been running a year but why the WAL files still hanging around ? Shouldn't they be consumed by the postgres internal system ? @jjanes
    – padjee
    Sep 13, 2022 at 22:36
  • No way to know what happened a year ago unless you have a trail of backups you could troll through, or someone to interrogate. If someone manually mucked around in the pg_wal directory (particularly the archive_status sub), they could have broken PostgreSQL's ability to clean up or recycle the old files. In that case, the only way for them to go away is for some brave person to go muck around in pg_wal again.
    – jjanes
    Sep 19, 2022 at 17:55

1 Answer 1


First, run a CHECKPOINT on the primary and see if that deletes some WAL segments (checkpoints happen regularly anyway). Probably, that won't get rid of the WAL segments.

Then, examine the known causes for PostgreSQL to keep around WAL:

  • wal_keep_size (or wal_keep_segments on old versions): you already checked that

  • archiver problems (look into pg_stat_archiver): you already did that as well

  • stale replication slots:

    SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn
    FROM pg_replication_slots;

    Look for slots where active is FALSE and the LSN is old.

  • There are 3 replication : 2 are inactive, 1 active. The active replication has state : streaming; sync_state : async; sent_lsn = write_lsn = replay_lsn = 98/8800C590. I already dropped the inactive replications. And I want to resync the active one. Should I drop the replication and recreate it OR restart the replication ? Thanks @LaurezAlbe
    – padjee
    Sep 13, 2022 at 22:54
  • 1
    The active one synchronizes itself, don't worry about it. Drop the inactive replication slots, and your problem is history. Sep 14, 2022 at 5:53
  • yes. Thanks. Finally the files in pg_wal drops to only 2400+. But, out of curiosity, how long does it take for number of WALs to be reduced to 0 when archiving_mode is turned off ? I have a couple of WALs that are 1 year old. If archiving is off, no WALs will be kept around. So the postgres internal should eat them all up. Correct ? @LaurenzAlbe
    – padjee
    Sep 14, 2022 at 12:33
  • 1
    Yes, correct. WAL is deleted at the end of a checkpoint. Actually, I would have expected all your old WAL segments to go away at that time. I didn't read the code on the exact algorithm, but I would expect your WAL size to go down still more as time goes by, unless there is another obstacle. Sep 14, 2022 at 13:56

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.