My database has stopped deleting WAL files and pg_xlog grows to eventually take all my disk space. I have to use pg_controldata and pg_archivecleanup to delete them manually.
I'm almost certain it's to do with my tinkering with replication, but I thought I removed it, and the only remnant of that I can see is the replication slot, but when I try to remove it: pg_drop_replication_slot('barman') causes [55000] ERROR: replication slots can only be used if wal_level > = archive. Which I find strange because I'm trying to remove it, not use it.
$ barman list-server shows nothing
SHOW wal_level minimal
SHOW archive_command (disabled)
SELECT * FROM pg_replication_slots
barman,,physical,,,false,,,,23/DF000000
SHOW server_version 9.5.14
My postgresql.conf is absent of all WAL settings except max_replication_slots = 1, which I tried to set to 0, but the database will not start if I do.
How can I remove my replication slot, or otherwise troubleshoot postgres not deleting its WAL files?
Best Answer
Set "wal_level" back to "archive", and restart. Then drop the slot. Then if you don't want leave it at "archive", set it back to "minimal" again, set max_replication_slots to 0, and restart again.
Removing a slot requires access to the slot machinery. It is a form of "use".