My dnf history is rather large due to several years of dnf system upgrades. I want to prune the database.
I want to remove entries prior to the last dnf system-upgrade. I’ve been through the dnf man pages but I don’t see how to trim or prune the database. Searching on Google is also failing to return relevant results.
sqlite> SELECT * FROM trans;
1|1553601932|1553602307|0:da39a3ee5e6b4b0d3255bfef95601890afd80709|739:c4ad7ee39a57027f0dbd59471352536e2432c296|29|-1||1|
2|1553603827|1553604430|739:c4ad7ee39a57027f0dbd59471352536e2432c296|778:55fecf768e2873024c83a9355a9d4822ef405af8|29|1000|-y update|1|
3|1553604476|1553604492|778:5bbbe422c529141d0a7c6deb9c767a7a4a17aeb9|793:e821f151bc353b4c631b9ce5de840cd25e0eb19c|29|1000|-y install clang gcc gcc-c++ make automake autoconf libtool pkg-config lcov|1|
...
Based on my dnf history:
root@skylake# dnf history | grep system-upgrade
1464 | system-upgrade upgrade | 2021-04-30 15:29 | D, E, I, O, U | 2484 EE
1286 | system-upgrade upgrade | 2020-10-28 14:35 | D, I, O, U | 2344 EE
1112 | system-upgrade upgrade | 2020-05-04 18:42 | D, E, I, O, U | 1862 EE
...
I think I can remove entries 1-1463.
Going back to to the database:
sqlite> .schema trans
CREATE TABLE trans (
id INTEGER PRIMARY KEY AUTOINCREMENT,
dt_begin INTEGER NOT NULL, /* (unix timestamp) date and time of transaction begin */
dt_end INTEGER, /* (unix timestamp) date and time of transaction end */
rpmdb_version_begin TEXT,
rpmdb_version_end TEXT,
releasever TEXT NOT NULL, /* var: $releasever */
user_id INTEGER NOT NULL, /* user ID (UID) */
cmdline TEXT, /* recorded command line (program, options, arguments) */
state INTEGER NOT NULL /* (enum) */
, comment TEXT DEFAULT "");
So it looks like this should do the trick:
sqlite> DELETE FROM trans WHERE id <= 1463;
sqlite> DELETE FROM trans_item WHERE trans_id <= 1463;
sqlite> DELETE FROM trans_with WHERE trans_id <= 1463;
sqlite> DELETE FROM console_output WHERE trans_id <= 1463;
sqlite> VACUUM;
sqlite> SELECT COUNT(*) FROM trans;
35
And now when I look at my dnf history:
skylake:~$ dnf history
ID | Command line | Date and time | Action(s) | Altered
--------------------------------------------------------------------------------
1500 | remove kernel-0:5.12.6-30 | 2021-05-30 23:17 | ?, E | 5 EE
1499 | -y autoremove | 2021-05-30 23:17 | Removed | 1
1498 | -y upgrade | 2021-05-30 21:58 | I, U | 91 EE
1497 | -y upgrade | 2021-05-26 04:18 | Upgrade | 36
1496 | -y remove kernel-0:5.12.5 | 2021-05-25 19:01 | ?, E | 5 EE
1495 | -y update | 2021-05-25 18:56 | Install | 4
1494 | -y remove kernel-0:5.11.2 | 2021-05-24 16:44 | ?, E | 9 EE
1493 | -y upgrade | 2021-05-24 04:15 | Upgrade | 21
1492 | -y upgrade | 2021-05-23 04:17 | I, U | 21
1491 | -y upgrade | 2021-05-22 04:19 | Upgrade | 12
1490 | -y upgrade | 2021-05-21 04:20 | Upgrade | 17
1489 | -y upgrade | 2021-05-20 04:21 | I, U | 17
1488 | -y autoremove | 2021-05-19 12:43 | Removed | 4
1487 | -y remove kernel-0:5.11.1 | 2021-05-19 12:41 | ?, E | 9 EE
1486 | -y upgrade | 2021-05-19 04:21 | Upgrade | 11 EE
1485 | -y upgrade | 2021-05-18 04:20 | Upgrade | 10
1484 | -y upgrade | 2021-05-17 04:19 | Upgrade | 9
1483 | -y upgrade | 2021-05-16 04:18 | I, U | 20
1482 | -y upgrade | 2021-05-15 04:14 | Upgrade | 43
1481 | -y upgrade | 2021-05-13 04:14 | I, U | 18 EE
1480 | -y autoremove | 2021-05-13 04:13 | Removed | 3
1479 | -y remove kernel-0:5.11.1 | 2021-05-12 16:21 | Removed | 8 EE
1478 | -y update | 2021-05-12 15:41 | Upgrade | 8 EE
1477 | -y upgrade | 2021-05-12 04:15 | Upgrade | 6
1476 | -y upgrade | 2021-05-10 04:18 | Upgrade | 8
1475 | -y upgrade | 2021-05-09 04:13 | I, U | 32
1474 | -y upgrade | 2021-05-08 04:19 | I, U | 19
1473 | -y upgrade | 2021-05-07 04:21 | I, U | 79
1472 | -y upgrade | 2021-05-06 04:12 | O, U | 58 EE
1471 | -y upgrade | 2021-05-05 04:12 | Upgrade | 32
1470 | -y upgrade | 2021-05-03 04:11 | I, U | 22
1469 | -y upgrade | 2021-05-02 04:21 | Upgrade | 6
1468 | -y upgrade | 2021-05-01 04:14 | Upgrade | 24
1467 | -y remove kernel-0:5.11.1 | 2021-04-30 16:26 | Removed | 8 EE
1466 | autoremove | 2021-04-30 16:22 | Removed | 34
1465 | remove adwaita-qt4-0:1.1. | 2021-04-30 16:22 | Removed | 12
1464 | system-upgrade upgrade | 2021-04-30 15:29 | D, E, I, O, U | 2484 EE
And the before and after stats:
# Before the pruning
root@skylake# ls -l /var/lib/dnf/history.sqlite
-rw-r--r--. 1 root root 16846848 May 30 23:12 /var/lib/dnf/history.sqlite
# After the pruning
root@skylake# ls -l /var/lib/dnf/history.sqlite
-rw-r--r--. 1 root root 6344704 May 30 23:14 /var/lib/dnf/history.sqlite