understanding pg_wal and archiving

Question

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
stats_reset                          
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,

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

Please enlighten me.

Thank you so much

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.