Need to free up space by removing pg_wal files in Postgres

Question

We have exhausted our disk capacity because of thousands of pg_wal files. pg_wal archiving is not working because of wrong configuration. Since the space is too low, we cannot start archiving on that disk anymore either.

Below is the log-

archive command failed with exit code 1 The failed archive command was: scp pg_wal/000000010000000000000001 host_name:walarchives/000000010000000000000001 ssh: connect to host host_name port 22: No route to host lost connection

Now, how can we delete all those pg_wal files to claim as much space as possible?

[root@HOST_NAME pg_wal]# ls -altr | wc -l
23376
[root@HOST_NAME pg_wal]#


[root@HOST_NAME data]# du -sh * | sort -h | tail -n 1
366G    pg_wal
[root@HOST_NAME data]#

Answer 1

If you have no plan to use these files for recovery, you can temporarily set archive_command to a command that succeeds but does nothing. The server will call it on each WAL file to archive and then delete it from the disk. You should quickly recover most of the space.

The documentation for archive_command says:

Setting archive_command to a command that does nothing but return true, e.g., /bin/true (REM on Windows), effectively disables archiving, but also breaks the chain of WAL files needed for archive recovery, so it should only be used in unusual circumstances.

Once you have fixed the root cause that prevents archive from working, you'll need to do a full backup.