Re: Can postgres ever delete the recycled future WAL files to free-up disk space if max_wal_size is reduced or wal_recycle is set to off?

On Mon, May 9, 2022 at 6:47 PM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> On Fri, May 6, 2022 at 10:20 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> writes:
> > > Can postgres delete the recycled future WAL files once max_wal_size is
> > > reduced and/or wal_recycle is set to off?
> >
> > A checkpoint should do that, see RemoveOldXlogFiles.
> >
> > Maybe you have a broken WAL archiving setup, or something else preventing
> > removal of old WAL files?
>
> Thanks Tom. My test case is simple [1], no archiving, no replication
> slots - just plain initdb-ed cluster. My expectation is that whenever
> max_wal_size/wal_recycle is changed from the last checkpoint value,
> postgres must be able to delete "optionally" "all or some of" the
> future WAL files to free-up some disk space (which is about to get
> full) so that I can avoid server crashes and I will have some time to
> go scale the disk.
>
> [1]
> show min_wal_size;
> show max_wal_size;
> show wal_recycle;
>
> drop table foo;
> create table foo(col int);
>
> -- run below pg_switch_wal and insert statements 9 times.
> select pg_switch_wal();
> insert into foo select * from generate_series(1, 1000);
>
> select redo_wal_file from pg_control_checkpoint();
>
> checkpoint;
> --there will be around 10 recycled WAL future WAL files.
>
> alter system set max_wal_size to '240MB';
> select pg_reload_conf();
> show max_wal_size;
>
> checkpoint;
> --future WAL files will not be deleted.
>
> alter system set min_wal_size to '24MB';
> select pg_reload_conf();
> show min_wal_size;
>
> checkpoint;
> --future WAL files will not be deleted.
>
> alter system set wal_recycle to off;
> select pg_reload_conf();
> show wal_recycle;
>
> checkpoint;
> --future WAL files will not be deleted.

Hi, I'm thinking out loud - can we add all the recycled WAL files to a
sorted list (oldest recycled WAL file to new recycled WAL file) and
then during checkpoint, if the max_wal_size is reduced or wal_recycle
is set to off, then start deleting the future WAL files from the end
of the sorted list. Upon restart of the server, if required, the
sorted list of future WAL files can be rebuilt.

Thoughts?

Regards,
Bharath Rupireddy.