I have used Pi-hole on our local network for a
few years now. It is running on a dedicated Raspberry Pi 3B attached
to the router (Netgear Nighthawk) to provide fast local DNS/DCHP while
blocking ads at the network level.
The built-in Pi-hole web interface allows for some basic querying/reporting
of the collected data, but it’s a bit limited and quite slow as the data
grows over time. My current
pihole-FTL.db database is 1.4 GB and contains 12 months of data.
$ ls -alh /etc/pihole/pihole-FTL.db -rw-r--r-- 1 pihole pihole 1.4G Jan 31 14:04 pihole-FTL.db
Pi-hole saves its data in a few SQLite databases with the FTL database
(Faster Than Light) being the most interesting.
While I could try to work with the data directly in SQLite, I strongly prefer
Postgres and decided this was a great time to give the
a try. This post goes over the steps I took to bring Pi-hole data into
Postgres from its sqlite data source.
See my previous post on using
file_fdwfor more about Postgres’ Foreign Data Wrappers.