Re: oldest WAL files not removed

At Thu, 2 Sep 2021 08:21:37 +0200, <o(dot)lepretre(at)gmail(dot)com> wrote in
> Hy Kyotaro,
>
> Thanks for this explanation. I joined the files to be complete.

Thanks, it is informative. It looks like this if sorted in the file-name order.

01/09/2021 12:15 16 777 216 000000010000008E00000061.deleted
01/09/2021 16:35 16 777 216 000000010000008E00000086.deleted
01/09/2021 16:35 16 777 216 000000010000008E00000087
30/08/2021 20:40 16 777 216 000000010000008E00000088
...
31/08/2021 17:58 16 777 216 000000010000008E000000CF

> If you looked the files only in timestamp order, with a high odds, the
> "oldest" file is a recycled file to be used in future, and the "newest" file
> is the currently written one. If so, the reason that the
> oldest-in-timestamp file is still there is it is still waiting to be used.
> Even if you removed the to-be-used-in-future files, such files would
> increase to the same extent according to the setting of min_wal_size.

The reason that 000000010000008E00000088 is still there is it is still
waiting to be used. The files 000000010000008E00000061/86.deleted
have been already removed in the postgres' view but remain because
someone else is still using it. If they persist too long, they could
be removed manually if possible.

The files 88 to CF look like preallocated, or recycled files. Since
there are 76 files, it seems like min_wal_size is set to 1GB or so. If
you don't need that many files preallocated in your pg_wal directory,
consider reducing min_wal_size. But note that the preallocated files
won't be gone right away just by doing that,

If you really want to delete that file right away, the preallocated
files are theoretically removable. You can see the current-writing
file name by the following query then move to somewhere the files with
names larger than the current file in the file-name order, then remove
the files after making sure the system can restart.

=# select pg_walfile_name(pg_current_wal_lsn());

If the system is active, the current file may advance so be careful
not to remove files with its substantial contents. This is why I said
"In any case, no WAL files ought to be manually removed."

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center