I did as outlined in the message you linked to. Not knowing at first how to get out of the sqlite mode caused some trouble. In the end I got it done. However in PiHole it made no difference. No long term history and it doesn't pick up device names, just IP's.
The end of the sql dump looks like this and it explains why that didn't restore anything.
...
INSERT INTO queries VALUES(1414492,1593353052,2,2,'normandy.cdn.mozilla.net','192.168.10.3','127.0.0.1');
/****** CORRUPTION ERROR *******/
CREATE TABLE ftl ( id INTEGER PRIMARY KEY NOT NULL, value BLOB NOT NULL );
INSERT INTO ftl VALUES(0,5);
INSERT INTO ftl VALUES(1,1593353033);
INSERT INTO ftl VALUES(2,1590728441);
CREATE TABLE counters ( id INTEGER PRIMARY KEY NOT NULL, value INTEGER NOT NULL );
INSERT INTO counters VALUES(0,1414476);
INSERT INTO counters VALUES(1,239472);
CREATE TABLE network_addresses ( network_id INTEGER NOT NULL, ip TEXT NOT NULL, lastSeen INTEGER NOT NULL DEFAULT (cast(strftime('%s', 'now') as int)), UNIQUE(network_id,ip), FOREIGN KEY(network_id) REFERENCES network(id));
CREATE TABLE IF NOT EXISTS "network" ( id INTEGER PRIMARY KEY NOT NULL, hwaddr TEXT UNIQUE NOT NULL, interface TEXT NOT NULL, name TEXT, firstSeen INTEGER NOT NULL, lastQuery INTEGER NOT NULL, numQueries INTEGER NOT NULL, macVendor TEXT);
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('queries',1414476);
CREATE INDEX idx_queries_timestamps ON queries (timestamp);
ROLLBACK; -- due to errors
I will admit I have had to pull the power supply around the time of the attempt to change port. When I insert an SD Card reader in the USB port of the PI, it sometimes locks up totally. The simpler reader doesn't do it as often. In such case there is not much to do except pull the power. The SD card reader is how I make a backup copy of the SSD to SD card. The latest copy did save my ass ...
Maybe it's something else that prevents the PiHole from picking up the device names. Some local device names are recognised ok currently in a terminal session of my laptop. It could very well be that that is because of the Local DNS entries in PiHole I made for a few devices. Other devices still don't have names. They used to get name in the query log. Now they do not.
I had to make a lot of temporary changes to get internet back when the PiHole was in disarray. I am somehow well versed in DD-WRT on my R7800, but who knows I forgot something.
Conditional forwarding on my Pihole never worked nicely. It added the local domain to most queries in the log.
But I'll try that again, too.
I had PiHole GUI clean the network table. There is still a grave error. I think I need to remove the data base like you suggested and get a new one.
Error
e[K [✗] Failed to truncate network_addresses table
Database location: /etc/pihole/pihole-FTL.db
Output: Error: no such table: network_addresses
Allright! Deleting the /pihole-FTL.db database solved it. Now the network table picks up devices again. I have not yet seen names, just IP's.
A minute later it gives names for some devices. Even for one on VPN as mi-a3.pivpn. Just like it used to do.
Thanks a lot. I think this is solved. The rest, if there are hostnames missing, is other LAN and router stuff.