16

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.)

1
  • 1
    Stop the service; rm -f /var/lib/fail2ban/fail2ban.sqlite3; start it again. Hey presto, your database is now a /lot/ smaller :D (I'm only half joking) Feb 8 '20 at 22:11
21

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.

11
  • 1
    Above that value in fail2ban.conf it says "Sets age at which bans should be purged from the database". The 500mb can't be a day's worth of bans, can it? (Also findtime appears nowhere in the file.)
    – felwithe
    Feb 9 '20 at 3:46
  • 500 MiB must be several months of logs. I add the SQL for deleting the old database entries in the answer. Feb 9 '20 at 17:48
  • So this is interesting. I made a copy of the file, opened it with sqlite, deleted every line from every table, and the size of the database is still the same, 519mb. Every single table has 0 rows now (there are 4 as shown with the .tables command: bans, fail2banDb, jails, and logs). Size of the file: still 519mb. (Minus a few bytes, which I assume was the small amount of data I deleted. There were only ~100 lines in bans.)
    – felwithe
    Feb 9 '20 at 22:39
  • 2
    You need to VACUUM afterwards. Feb 9 '20 at 22:42
  • 4
    The sqlite3 command creates a temporary 'fail2ban.sqlite3-journal' file of equal size. My fail2ban.sqlite3 was 17GB so it was not a viable option, not to mention it takes a long time. So I just did a '/etc/init.d/fail2ban stop' and simply removed fail2ban.sqlite3. A new file was created from scratch when I started fail2ban again.
    – chiappa
    Mar 31 '20 at 13:16
9

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.

1
  • 1
    I wouldn't recommend deleting the database, since this would mean an amnesty for the most recent attackers. This note aside, since there are deb packages available, I would recommend Debian users to vote this answer from now on. Jan 30 at 14:25
4
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

1
  • fail2ban-client stop worked for me. Thanks
    – Andrzej
    Oct 17 '20 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.