fail2ban's database is too large (over 500mb). How do I get it to a reasonable size?

Recently my backups have started failing, and I tracked the problem to the file /var/lib/fail2ban/fail2ban.sqlite3. It is over 500mb. I am not sure whether it has been growing over time or if this is a recent development.

How can I get it to a reasonable size and keep it that size? (For the purposes of this let's say under 500mb.)

Solution 1:

There is a dbpurgeage parameter in fail2ban.conf, which tells how many days of data to keep in the database. The default is one day (1d), so try do decrease it to a couple of hours:

dbpurgeage = 8h

This setting is coupled with findtime: it makes no sense to have a findtime longer than dbpurgeage.

Edit (2021): The note below was true at the time of writing. However nowadays check out neingeist answer instead: fail2ban 0.11.x which starts being available in Linux distributions (e.g. Debian testing, Ubuntu 20.04 and later, Fedora 33), respects the dbpurgeage setting.

Obsolete note: By looking at my own fail2ban database, the dbpurgeage setting does not seem to be working. Therefore the only solution is to delete the entries manually. For example, in order to delete last year's entries run:

sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 \
  "DELETE FROM bans WHERE DATE(timeofban, 'unixepoch') < '2020-01-01'; VACUUM;"

(the sqlite3 executable is usually in the homonymous package).

There seem to be no way to perform a VACUUM of the database without sqlite performing a copy of the database in the same directory. However you can copy the file to another filesystem before performing the operation and than copy back the smaller database.

Solution 2:

You can update to 0.11.x (which contains code to do the purge) and then delete the huge database followed by a restart of fail2ban. It will recreate the database. This is the easiest solution with no drawbacks for most people.

While fail2ban 0.11.x actually contains code to purge old entries (the older version did not!), it does no VACUUM. So another option is to wait for fail2ban to purge the old entries (happens every hour) and perform a manual sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 "VACUUM;". Without the VACUUM the database file will stay at its size.

Solution 3:

sudo /etc/init.d/fail2ban stop

sudo rm -rf /var/lib/fail2ban

sudo /etc/init.d/fail2ban start

sudo reboot

resolved everything for me. check after reboot

df -h

i had 25gb of sqlite files, memory was 94% of 40gb