Ryan Lambert: Load and query Pi-hole data from Postgres

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
pgspider/sqlite_fdw extension
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_fdw for more about Postgres’ Foreign Data Wrappers.

PostgreSQL