Question Fail2ban sqlite database huge

[SteveITS]

We had a client's server run out of disk space last night. It looks like fail2ban had made several copies of its database for some reason until it ran out:

-rw------- 1 root root 1232522240 May 4 09:18 fail2ban.sqlite3
-rw------- 1 root root 1232522240 May 3 21:03 fail2ban.sqlite3.20210504-010217
-rw------- 1 root root 1232522240 May 3 21:04 fail2ban.sqlite3.20210504-010359
-rw------- 1 root root 1232522240 May 3 21:05 fail2ban.sqlite3.20210504-010420
-rw------- 1 root root 1232522240 May 3 21:05 fail2ban.sqlite3.20210504-010525
-rw------- 1 root root 1232522240 May 3 21:05 fail2ban.sqlite3.20210504-010548
-rw------- 1 root root 1232522240 May 3 21:06 fail2ban.sqlite3.20210504-010603
-rw------- 1 root root 1105612800 May 3 21:06 fail2ban.sqlite3.20210504-010625
-rw------- 1 root root 0 May 4 09:03 fail2ban.sqlite3.20210504-130353

We deleted it all, but in reviewing this, does Plesk not clear out the fail2ban database? Is there a way to tell it to "within Plesk?" It can be manually pruned and doing so for 90 days on a few servers removes 75-90% of the file size:
sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 "delete from bans where timeofban <= strftime('%s', date('now', '-90 days'));"
sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 "vacuum;"

Whenever I've looked on any server fail2ban only had around 10-20 IPs listed as banned.

I've also found at least one more with a database copy dating back to March 2020 (fail2ban.sqlite3.2020*).

 

[SteveITS]

The space issue caused a failed Plesk upgrade (we got the email alert 30 hours later, after the second attempt/failure) so I had to engage support. Along the way they wrote:
"Since 18.0.35 fail2ban was updated to version v0.11 which has automatic scripts for purging old fail2ban data, therefore it should not eat a lot of space anymore."

 

[Pleskie]

The space issue caused a failed Plesk upgrade (we got the email alert 30 hours later, after the second attempt/failure) so I had to engage support. Along the way they wrote:
"Since 18.0.35 fail2ban was updated to version v0.11 which has automatic scripts for purging old fail2ban data, therefore it should not eat a lot of space anymore."

FYI: as far as I know since Fail2Ban version v0.11 old entries are being purged based on the dbpurgeage parameter. However the database is not being 'vacuumed'. This means that old entries are being 'flagged' as deleted, but are not actually removed from the database. So the database will still keep growing. My advice, create a cron job that vacuums the database each night. Then the old entries will be truly deleted.

 

[SteveITS]

FYI: as far as I know since Fail2Ban version v0.11 old entries are being purged based on the dbpurgeage parameter. However the database is not being 'vacuumed'. This means that old entries are being 'flagged' as deleted, but are not actually removed from the database. So the database will still keep growing. My advice, create a cron job that vacuums the database each night. Then the old entries will be truly deleted.

I see, thanks for the update. I hadn't had a change to dig into it much yet but I had already scripted the two commands above, plus one to delete the extra, dated copies:
rm -f /var/lib/fail2ban/fail2ban.sqlite3.*

Also note article After update to Plesk 18.0.35 disk space usage increases drastically or disk is already full: /var/lib/fail2ban/ taking all the space was just created. "The issue is confirmed as the bug PPPM-12981. Fail2Ban tries to upgrade its database and fails, continually creating files in an endless loop." Workaround is to stop the service and delete the files. When I looked on all our servers at least one I know had a fail2ban.sqlite3.* file from March 2020.

 

[Pleskie]

@SteveITS

Stopping the service and deleting files is a workaround to get rid of the large files but won't prevent the new database to grow big again.

Try what I said: create a cron job to run every night which vacuums the database. That will prevent it from growing extremely large.

 

[SteveITS]

create a cron job

Already did per the above but thanks.
I looked at the servers that upgraded to 18.0.35 since I did this last Thursday and all had one extra fail2ban.sqlite3.* file. So not an infinite number nor enough to run out of space as the Plesk KB describes, thankfully, but if the database is (used to be) a few GB or more, any copies use a lot of space.

Edit: note Plesk's example in their KB is a 26 GB database with multiple 13 GB files.