I love PiHole! It’s an amazing service. Not only can you block ads and other weird or malicious websites, you can also set up local DNS and make things much better for your homelab!

However, when I updated to V6 I faced issues upon issues. Kept thinking my internet was dying on me, the microSD was dying, the pi was dying, the ssd was dying. Nope, it was just pihole v6 dying.

Well, like the idiot I am, I turned off my backups as I needed to set them up again, but I completely forgot to do it! This was back in December 2023, ouch.

I updated my pihole without taking a manual backup either, so if I want to keep my 7GB database, I need to do this manually.

The plan

Setup a new pihole v5 container with the image I had before
Stop the container and copy all old configuration files and the database
Pray it works
I figure out it doesn’t work
Manually move over all configurations from files I no longer have
Manually move over all the lists(this should be easy)
Manually move over all my local dns setup and custom white/blacklist & etc
Manually de-migrate the gravity and ftl database
Smash my keyboard in anger from things still not working
Mourning the lost databases and swearing with my fist up to the sky that I will keep them until I find a way to fix this curse

Files I have, by time

I don’t remember what all these are, but january is when I updated to V6 so that seems like a start. I seem to be lucky, and I haven’t made many changes since the upgrade. You might not be as lucky.

I decided on:

setupVars.conf.update.back
adlists.list
dns-servers.conf
hosts
local.list
dnsmasq.conf
everything migration_backup_v6
old_db (holds the oldest ftl db I found)
gravity_old.db (though I don't think this is necessary?) (Future Titor here, I was very wrong)

Let’s copy them over

newdir=/volumes/pihole-v5/etc-pihole/
cp setupVars.conf* $newdir/setupVars.conf
cp adlists.list $newdir
cp dns-servers.conf $newdir
cp -R hosts $newdir
cp local.list $newdir
cp dnsmasq.conf $newdir
cp -R migration_backup_v6/* $newdir
cp old_db/* $newdir
cp gravity_old.db $newdir/gravity.db

The test

Let’s see what happens if we just run it like this…
Permission denied

chmod -R 777 $newdir

Aaand

  [✗] Unable to copy data from /etc/pihole/gravity.db to /etc/pihole/gravity.db_temp

  Parse error near line 12: no such table: OLD.domain_audit

Parse error near line 18: table adlist has 10 columns but 12 values were supplied

Runtime error near line 20: FOREIGN KEY constraint failed (19)

   [✗] Unable to create gravity database. Please try again later. If the problem persists, please contact support.

Oh boy, incompatible databases. Expected…
Gravity is generated from the lists though, so maybe this isn’t too big of a deal, I don’t even know why I copied this over. Let’s delete.

Alright, worked! Let’s check the settings. I won’t show you these, but everything was the exact same! I had dhcp turned off so I might have been missing some files you need to copy if you use it. This is what works right out of the box as long as you have the previous conf files that got saved during migration:

System – worked
DNS – worked
DHCP(off) – worked
Web Interface/API – worked
Privacy – worked
Local DNS Records – worked

Caveats being I was missing one local dns record, but I added it after the migration. I haven’t made other changes since the v6 migration, so I can’t really help you retrieve those. Technically they should all be in the new settings file, maybe someone will make a reverse migrator for it. Or, if you pester me enough I might make one, but it might be faster to just manually move things over.

Fixing Gravity

Onto adlists, it seems I have a single one. Checking the adlists file, it seems that there’s nothing else in it. This really wasn’t updated since 2022, huh…I figured that was when I last added a list, I haven’t done that since setting it up. Oh well.

I noticed I missed a folder, listsCache. Stopped the container, rsynced it over. No luck, must be from the new pihole. Deleted it.

I noticed a migration_backup folder was made with adlists.list in it. These were moved somewhere else, in the gravity or FTL databases.

Let’s add one manually and see what happens

grep 'https://raw.githubusercontent.com/PolishFiltersTeam/KADhosts/master/KADhosts.txt' *
grep: gravity.db: binary file matches

So I was very wrong, the gravity.db is absolutely needed as that’s where the lists were moved.

Now I had 3 options:

Manually move over adlists
Search for an older gravity db file that I might have that might work
De-migrate the gravity database back to its previous version.
Obviously, I will go with option 3.

Let’s see the schemas of these DBs
New Schema
Old Schema
Let’s do a simple notepad++ compare

We notice that address TEXT is no longer UNIQUE in the new one
They added 3 new lines to the adlist table creation

abp_entries INTEGER NOT NULL DEFAULT 0,
type INTEGER NOT NULL DEFAULT 0,
UNIQUE(address, type)

Made a new table, and some other small stuff. Should be easy to reverse migrate!

Let’s make a copy first

 cp /vo*/pihole/etc-pihole/gravity.db ./new_db.db
 cp new_db.db work.db

And let’s test on it. Here’s a live comment as I do it, though you won’t read it live:

sqlite3 work.db
---
DROP TABLE IF EXISTS antigravity;
ALTER TABLE adlist DROP COLUMN abp_entries;
Error: near "DROP": syntax error
-- Oopsie, not the right sqlite version...we also can't drop the constraint it seems so we need to do a workaround. Let's drop the views first
DROP VIEW IF EXISTS vw_gravity;
DROP VIEW IF EXISTS vw_adlist;
DROP VIEW IF EXISTS vw_antigravity;
-- Rename the table and recreate it 
ALTER TABLE adlist RENAME TO adlist_old;
-- Recreate it
CREATE TABLE adlist (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    address TEXT UNIQUE NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT 1,
    date_added INTEGER NOT NULL DEFAULT (cast(strftime('%s', 'now') as int)),
    date_modified INTEGER NOT NULL DEFAULT (cast(strftime('%s', 'now') as int)),
    comment TEXT,
    date_updated INTEGER,
    number INTEGER NOT NULL DEFAULT 0,
    invalid_domains INTEGER NOT NULL DEFAULT 0,
    status INTEGER NOT NULL DEFAULT 0
);
-- Add the data back
INSERT INTO adlist (id, address, enabled, date_added, date_modified, comment, date_updated, number, invalid_domains, status)
SELECT id, address, enabled, date_added, date_modified, comment, date_updated, number, invalid_domains, status
FROM adlist_old;
-- Drop the old table
DROP TABLE adlist_old;
-- Recreate the views
CREATE VIEW vw_gravity AS
SELECT domain, adlist_by_group.group_id AS group_id
FROM gravity
LEFT JOIN adlist_by_group ON adlist_by_group.adlist_id = gravity.adlist_id
LEFT JOIN adlist ON adlist.id = gravity.adlist_id
LEFT JOIN "group" ON "group".id = adlist_by_group.group_id
WHERE adlist.enabled = 1 AND (adlist_by_group.group_id IS NULL OR "group".enabled = 1);

CREATE VIEW vw_adlist AS
SELECT DISTINCT address, id
FROM adlist
WHERE enabled = 1
ORDER BY id;
-- Drop the new stat table
DROP TABLE IF EXISTS sqlite_stat1;
-- Add back the missing domain audit table
CREATE TABLE domain_audit
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    domain TEXT UNIQUE NOT NULL,
    date_added INTEGER NOT NULL DEFAULT (cast(strftime('%s', 'now') as int))
);

Great, no errors! Now let’s copy the file over and start pihole again.

  [i] Preparing new gravity database...
  [✓] Preparing new gravity database

  [i] Creating new gravity databases...

  [✗] Unable to copy data from /etc/pihole/gravity.db to /etc/pihole/gravity.db_temp


Parse error near line 19: no such table: OLD.adlist_old

Noooo! What are you referencing?

With some help from chatGPT we figure that out. I didn’t even know about sql_master lol, good thing to keep in mind for finding out where data is used.

SELECT name, type, sql
FROM sqlite_master
WHERE sql LIKE '%domain_audit%' OR sql LIKE '%adlist_old%';

adlist_by_group|table|CREATE TABLE adlist_by_group
(
    adlist_id INTEGER NOT NULL REFERENCES "adlist_old" (id) ON DELETE CASCADE,
    group_id INTEGER NOT NULL REFERENCES "group" (id) ON DELETE CASCADE,
    PRIMARY KEY (adlist_id, group_id)
)
gravity|table|CREATE TABLE gravity
(
    domain TEXT NOT NULL,
    adlist_id INTEGER NOT NULL REFERENCES "adlist_old" (id)
)

Do we want on delete cascade? No because it was not there before and I don’t even know what a group is. If I delete a group it will delete all adlists in it? I guess it makes sense, but I don’t trust myself. Let’s first rename

ALTER TABLE adlist_by_group RENAME TO adlist_by_group_old;
ALTER TABLE gravity RENAME TO gravity_old;
CREATE TABLE adlist_by_group (
    adlist_id INTEGER NOT NULL REFERENCES adlist(id),
    group_id INTEGER NOT NULL REFERENCES "group"(id),
    PRIMARY KEY (adlist_id, group_id)
);
INSERT INTO adlist_by_group
SELECT * FROM adlist_by_group_old;
DROP TABLE adlist_by_group_old;

CREATE TABLE gravity (
    domain TEXT NOT NULL,
    adlist_id INTEGER NOT NULL REFERENCES adlist(id)
);


INSERT INTO gravity
SELECT * FROM gravity_old;
DROP TABLE gravity_old;

Now after copying it over…

And BOOM, we’re back baby!

Now we prepare the sql file so others can use it too

Reverse migration sql file

BEGIN TRANSACTION;

  

DROP TABLE IF EXISTS antigravity;

DROP VIEW IF EXISTS vw_gravity;

DROP VIEW IF EXISTS vw_adlist;

DROP VIEW IF EXISTS vw_antigravity;

ALTER TABLE adlist RENAME TO adlist_old;

  

CREATE TABLE adlist (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    address TEXT UNIQUE NOT NULL,

    enabled BOOLEAN NOT NULL DEFAULT 1,

    date_added INTEGER NOT NULL DEFAULT (cast(strftime('%s', 'now') as int)),

    date_modified INTEGER NOT NULL DEFAULT (cast(strftime('%s', 'now') as int)),

    comment TEXT,

    date_updated INTEGER,

    number INTEGER NOT NULL DEFAULT 0,

    invalid_domains INTEGER NOT NULL DEFAULT 0,

    status INTEGER NOT NULL DEFAULT 0

);

  

-- Add the data back

INSERT INTO adlist (id, address, enabled, date_added, date_modified, comment, date_updated, number, invalid_domains, status)

SELECT id, address, enabled, date_added, date_modified, comment, date_updated, number, invalid_domains, status

FROM adlist_old;

--  Drop the old table

DROP TABLE adlist_old;

-- Recreate the views

CREATE VIEW vw_gravity AS

SELECT domain, adlist_by_group.group_id AS group_id

FROM gravity

LEFT JOIN adlist_by_group ON adlist_by_group.adlist_id = gravity.adlist_id

LEFT JOIN adlist ON adlist.id = gravity.adlist_id

LEFT JOIN "group" ON "group".id = adlist_by_group.group_id

WHERE adlist.enabled = 1 AND (adlist_by_group.group_id IS NULL OR "group".enabled = 1);

  

CREATE VIEW vw_adlist AS

SELECT DISTINCT address, id

FROM adlist

WHERE enabled = 1

ORDER BY id;

-- Drop the new stat table

DROP TABLE IF EXISTS sqlite_stat1;

-- Add back the missing domain audit table

CREATE TABLE domain_audit

(

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    domain TEXT UNIQUE NOT NULL,

    date_added INTEGER NOT NULL DEFAULT (cast(strftime('%s', 'now') as int))

);

  

ALTER TABLE adlist_by_group RENAME TO adlist_by_group_old;

ALTER TABLE gravity RENAME TO gravity_old;

CREATE TABLE adlist_by_group (

    adlist_id INTEGER NOT NULL REFERENCES adlist(id),

    group_id INTEGER NOT NULL REFERENCES "group"(id),

    PRIMARY KEY (adlist_id, group_id)

);

INSERT INTO adlist_by_group

SELECT * FROM adlist_by_group_old;

DROP TABLE adlist_by_group_old;

  

CREATE TABLE gravity (

    domain TEXT NOT NULL,

    adlist_id INTEGER NOT NULL REFERENCES adlist(id)

);

  
  

INSERT INTO gravity

SELECT * FROM gravity_old;

DROP TABLE gravity_old;

  

COMMIT;

Still works perfectly fine!

Faster Than Light

Now, the bigger problem. FTL. We have nothing in our query log or audit log. Let’s make this our main pihole for a moment to grab a bunch of queries and see what happens…

Stop the V6 container
Start the v5 container with the correct ports
Things seem to work normally now and the audit log gets filled with stuff..
This is a problem. It means that the audit logs are either in the FTL database but not working yet, or they were in the gravity database which got obliterated

 WARN: Database /etc/pihole/pihole-FTL.db is read-only and cannot be used.

Ah, so what is it working on? The write ahead file? Let’s have it make its own database for now so we can compare.

Stop pihole
Delete the FTL database
Start pihole back up
Make copies of both databases
Make a migration script

Old Schema
New Schema
I the script, it was correct as far as I saw, but it kept complaining about being read only. After some fussing around with perms, it still does not work but with no indication as to why. It actually connected to the DB, and added new stuff to it, but the old things weren’t showing up.

I gave up for now. I deleted the database and let a new one generate. In hindsight I should have kept it to try and fix things, but when I have the time to try again I just need to re-run my reverse-migration script.

Maybe some data changed inside the tables? Maybe the reverse migration sql is wrong? I don’t know, too tired to further look into it.

Bummed that I couldn’t get the old data back in for FTL, but this already took too much time

Results

So far, this is running significantly faster than pihole-v6. This is most easily noticeable on smart TVs. On V6 I’ve had plex and jellyfin not open several times because of DNS resolution failure until switching back to V5. That local DNS that helps you set things up easily isn’t so nice now when it dies, is it? I wonder why even the local DNS is so slow…

Just memory usage alone went from an insane ~500MB to just ~85MB, and CPU generally sits between 0 and 2%

Why did I do this?

Don’t fix what isn’t broken, right? I updated a working pihole and got literal tens of hours lost debugging random shit around the house.

I haven’t had any issues at all with v5, it’s smartest to stick to this. I know there’s a whole lot of fixes in v6, but there’s just tens of small things that don’t apply to me. And yes, I did check the FTL release log. Other than server side pagination for query log there’s really not much that would improve speed in any meaningful way. I don’t even understand how the performance problems are happening with such small changes. Maybe some other core components were updated that I just didn’t get to, or maybe I didn’t notice some update in the changelog as I scrolled through it.

I know those small changes were no easy feat, but I also see them in the direction of changing everything and removing features just for the sake of change.

Pihole V5 was perfectly fine. It had its issues, but they should have been fixed rather than this huge update.

I’m not really a fan of huge updates, they always bring more trouble than it’s worth. Many times remove features and change the interface. I believe updates should be small and focused. Want to release a new feature? Do that. Want to improve something? Improve it. Don’t rewrite everything from the core.

Why does there need to be a new vX.0 every now and then where things are rebuilt? This is why software never finishes development.

Recently, Plex was completely ruined for me with their new “Experience”. Get it? It’s not an app anymore, it’s like throwing on a VR headset and sleeping with your neighbor’s wife, a rollercoaster or skydiving. I genuinely dislike Plex now and its devs. Pihole didn’t do anything wrong, it’s just slow right now and I don’t want to struggle with DNS until it’s fixed. Any jabs at pihole in this article are friendly and in humor, but Plex…oh boy. I’m not going to sperg about the horrible Plex update here though.

Conclusion

This was way easier than I expected. There was a lot of time spent on just investigating things, but the actual sql migration was a breeze. This was at least a very fun experiment.

As long as too much of the data wasn’t changed or removed in newer versions and as long as something uses sql you should be able to reverse migrate stuff. This is true for mysql databases too, maybe find out how to search for triggers views and whatnot in mysql if you have trouble.

I guess lesson learned, setup backups RIGHT NOW if you don’t have them set up

Downgrading PiHole V6 to V5