Thread: 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?

Hi,

I have a scenario where max_wal_size = 10GB and wal_recycle = on, the
postgres starts to recycle and keep WAL files for future use,
eventually around 600~ WAL files have been kept in the pg_wal
directory. The checkpoints were happening at regular intervals. But
the disk was about to get full (of course scaling up disk is an
option) but to avoid "no space left on device" crashes, changed
max_wal_size = 5GB, and issued a checkpoint, thinking that postgres
will free up the 5GB of disk space. It seems like that's not the case
because postgres will not remove future WAL files even after
max_wal_size is reduced, but if it can delete the future WAL file(s)
immediately, the server would have had 5GB free disk space to keep the
server up avoiding crash and meanwhile disk scaling can be performed.

Can postgres delete the recycled future WAL files once max_wal_size is
reduced and/or wal_recycle is set to off?

Thoughts?

Regards,
Bharath Rupireddy.

Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.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?

            regards, tom lane

On Fri, May 6, 2022 at 10:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.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.

Regards,
Bharath Rupireddy.

On Mon, May 9, 2022 at 6:47 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
>
> On Fri, May 6, 2022 at 10:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.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.

On Fri, May 06, 2022 at 09:50:26PM +0530, Bharath Rupireddy wrote:
> It seems like that's not the case because postgres will not remove future WAL
> files even after max_wal_size is reduced,

In the past, I've had to generate synthetic write traffic and checkpoints to
get WAL to shrink.  +1 to make it respect max_wal_size on its own.

-- 
Justin

On 5/13/22 05:35, Bharath Rupireddy wrote:
> 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.

(This is registered in CF, but there doesn't seem to be a patch as part
of this thread, so I don't think there's anything to review. I've closed
it out to avoid further triage, but if I've somehow missed a patch feel
free to resurrect it -- and maybe point the patch out in an annotation
or something.)

Thanks,
--Jacob