Обсуждение: oldest WAL files not removed

At Wed, 1 Sep 2021 12:48:51 +0200, <o.lepretre@gmail.com> wrote in 
> Hi,
> 
>  
> 
> Looking at WAL folder after a crash, I noticed that new files after
> restarting overwrite the more recent files before the crash and not the
> oldest, which was what I expected. 
> 
> Is that normal ?  I got only one file marked .deleted. Does that happens
> when a WAL file hase been completed updated in the database and if then
> while all oldest files aren't marked .deleted after restarting ?
> 
> 
> Example :
> 
> Crash occurs Aug 31 22:03 which is the more recent Wal file, the oldest file
> is Aug 30 17:20 (and 105 files between those two)
> 
> After restarting Aug 30 17:20 is still there, Aug 31 22:03 disappeared, one
> new file is Sep 1 12:15 marked .deleted (restarting date) and one new Sep 1
> 12:36 which I guess is normal. Right now, I see an new wal file and the
> previous one marked .deleted which is ok. 
> 
> Why are the oldest wal files still there ?? Can I remove them ?
> 
> Hope I'm clear enough and thanks for explanations,

It would be very helpful you gave us the name of the files.  Due to
WAL file recycling, timestamps are frequently shuffled aginst names.

In any case, no WAL files ought to be manually removed.  If you don't
need the recycled-for-future files that much, consider reducing
min_wal_size.

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.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Hy Kyotaro,

Thanks for this explanation. I joined the files to be complete.

Have a good day,

Olivier

30/08/2021  20:40        16 777 216 000000010000008E00000088
30/08/2021  20:58        16 777 216 000000010000008E00000089
30/08/2021  21:09        16 777 216 000000010000008E0000008A
30/08/2021  21:24        16 777 216 000000010000008E0000008B
31/08/2021  10:56        16 777 216 000000010000008E0000008D
31/08/2021  11:04        16 777 216 000000010000008E0000008E
31/08/2021  11:05        16 777 216 000000010000008E00000090
31/08/2021  11:05        16 777 216 000000010000008E00000092
31/08/2021  11:05        16 777 216 000000010000008E00000091
31/08/2021  11:12        16 777 216 000000010000008E0000008F
31/08/2021  11:21        16 777 216 000000010000008E00000093
31/08/2021  11:29        16 777 216 000000010000008E00000095
31/08/2021  11:30        16 777 216 000000010000008E00000096
31/08/2021  11:36        16 777 216 000000010000008E00000098
31/08/2021  11:37        16 777 216 000000010000008E00000099
31/08/2021  11:37        16 777 216 000000010000008E0000009A
31/08/2021  11:37        16 777 216 000000010000008E0000009B
31/08/2021  11:37        16 777 216 000000010000008E0000009D
31/08/2021  11:37        16 777 216 000000010000008E0000009E
31/08/2021  11:37        16 777 216 000000010000008E0000009C
31/08/2021  11:37        16 777 216 000000010000008E0000009F
31/08/2021  11:37        16 777 216 000000010000008E000000A0
31/08/2021  11:37        16 777 216 000000010000008E000000A2
31/08/2021  11:37        16 777 216 000000010000008E000000A3
31/08/2021  11:37        16 777 216 000000010000008E000000A4
31/08/2021  11:37        16 777 216 000000010000008E000000A1
31/08/2021  11:37        16 777 216 000000010000008E00000097
31/08/2021  11:38        16 777 216 000000010000008E00000094
31/08/2021  12:08        16 777 216 000000010000008E000000A6
31/08/2021  12:08        16 777 216 000000010000008E000000A8
31/08/2021  12:08        16 777 216 000000010000008E000000A7
31/08/2021  12:12        16 777 216 000000010000008E000000A9
31/08/2021  12:21        16 777 216 000000010000008E000000AC
31/08/2021  12:21        16 777 216 000000010000008E000000AB
31/08/2021  12:21        16 777 216 000000010000008E000000AE
31/08/2021  12:21        16 777 216 000000010000008E000000AD
31/08/2021  12:27        16 777 216 000000010000008E000000AA
31/08/2021  14:27        16 777 216 000000010000008E000000A5
31/08/2021  14:36        16 777 216 000000010000008E000000B0
31/08/2021  14:36        16 777 216 000000010000008E000000B3
31/08/2021  14:36        16 777 216 000000010000008E000000B2
31/08/2021  14:36        16 777 216 000000010000008E000000B5
31/08/2021  14:36        16 777 216 000000010000008E000000B4
31/08/2021  14:42        16 777 216 000000010000008E000000B1
31/08/2021  14:48        16 777 216 000000010000008E000000AF
31/08/2021  14:48        16 777 216 000000010000008E000000B6
31/08/2021  14:55        16 777 216 000000010000008E000000B9
31/08/2021  14:55        16 777 216 000000010000008E000000BB
31/08/2021  14:55        16 777 216 000000010000008E000000BA
31/08/2021  15:02        16 777 216 000000010000008E000000B8
31/08/2021  16:01        16 777 216 000000010000008E000000B7
31/08/2021  16:04        16 777 216 000000010000008E000000BD
31/08/2021  16:11        16 777 216 000000010000008E000000C0
31/08/2021  16:11        16 777 216 000000010000008E000000BF
31/08/2021  16:11        16 777 216 000000010000008E000000C2
31/08/2021  16:11        16 777 216 000000010000008E000000C1
31/08/2021  16:12        16 777 216 000000010000008E0000008C
31/08/2021  16:17        16 777 216 000000010000008E000000BE
31/08/2021  17:31        16 777 216 000000010000008E000000BC
31/08/2021  17:38        16 777 216 000000010000008E000000C4
31/08/2021  17:41        16 777 216 000000010000008E000000C7
31/08/2021  17:41        16 777 216 000000010000008E000000C6
31/08/2021  17:41        16 777 216 000000010000008E000000C5
31/08/2021  17:41        16 777 216 000000010000008E000000C9
31/08/2021  17:47        16 777 216 000000010000008E000000C8
31/08/2021  17:51        16 777 216 000000010000008E000000C3
31/08/2021  17:53        16 777 216 000000010000008E000000CA
31/08/2021  17:58        16 777 216 000000010000008E000000CD
31/08/2021  17:58        16 777 216 000000010000008E000000CC
31/08/2021  17:58        16 777 216 000000010000008E000000CF
31/08/2021  17:58        16 777 216 000000010000008E000000CE
31/08/2021  18:01        16 777 216 000000010000008E000000CB
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

-----Message d'origine-----
De : Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Envoyé : jeudi 2 septembre 2021 04:41
À : o.lepretre@gmail.com
Cc : pgsql-general@postgresql.org
Objet : Re: oldest WAL files not removed

At Wed, 1 Sep 2021 12:48:51 +0200, <o.lepretre@gmail.com> wrote in
> Hi,
>
>
>
> Looking at WAL folder after a crash, I noticed that new files after
> restarting overwrite the more recent files before the crash and not
> the oldest, which was what I expected.
>
> Is that normal ?  I got only one file marked .deleted. Does that
> happens when a WAL file hase been completed updated in the database
> and if then while all oldest files aren't marked .deleted after restarting
?
>
>
> Example :
>
> Crash occurs Aug 31 22:03 which is the more recent Wal file, the
> oldest file is Aug 30 17:20 (and 105 files between those two)
>
> After restarting Aug 30 17:20 is still there, Aug 31 22:03
> disappeared, one new file is Sep 1 12:15 marked .deleted (restarting
> date) and one new Sep 1
> 12:36 which I guess is normal. Right now, I see an new wal file and
> the previous one marked .deleted which is ok.
>
> Why are the oldest wal files still there ?? Can I remove them ?
>
> Hope I'm clear enough and thanks for explanations,

It would be very helpful you gave us the name of the files.  Due to WAL file
recycling, timestamps are frequently shuffled aginst names.

In any case, no WAL files ought to be manually removed.  If you don't need
the recycled-for-future files that much, consider reducing min_wal_size.

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.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

At Thu, 2 Sep 2021 08:21:37 +0200, <o.lepretre@gmail.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