Postgresql – How to cleanup Postgres WAL


What is the correct way to cleanup WAL postgres? I have a database with more than 100 GB and it haves around 600 GB in pg_wal. Also I have 2 logical replications set up.

Master and Slaves – Postgres 10.

Master and Slaves have commented wal_keep_segments and max_wal_size.

pg_archivecleanup did not work with %r option, then I got the archive name by searching in pg_controldata latest checkpoint's REDO WAL file and deleted the logs, but then one replica stopped with the error

could not receive data from WAL stream: FATAL: requested WAL segment xxx has already been removed

To solve this I deleted and recreated the replica.

Best Answer

"pg_wal" cleans itself up. You should almost never touch pg_wal by hand. If it is not cleaning itself up, you need to figure out why and fix the underlying issue.

One possible reason is that you have a replication slot which is holding it back. Either a replica is using a slot and is unable to keep up. Or you have a slot which has no replica attached, for example you destroyed the replica but didn't drop the slop it used to occupy. You can see what slots you have by querying pg_replication_slots, and if necessary drop one with pg_drop_replication_slot, both run on the master. You would look for the slot with the oldest non-NULL value of "restart_lsn".

Another reason is that you have "archive_mode" turned on, but your "archive_command" is constantly failing or can't keep up. You will see warnings about this in your server log file if it is failing.

"pg_archivecleanup" is used to clean up a WAL archive. "pg_wal" is not the archive, it is the live WAL files. You are lucky you didn't destroy your database by monkeying around in there.