Learning DNS - Backends & Zone Data Management

#Introduction

In episode 5 you chose a backend as the home of your zone data. Now it's time to open the hood: understand the table structure, learn import-export and backup-restore, and get to know other backends beyond relational databases. With this knowledge, you can troubleshoot data problems, migrate between backends, and design data strategies for large scale.

#Database Backend Structure

#The domains and records Tables

The gsqlite3 and gmysql backends use two core tables. domains stores zone metadata; records stores every resource record:

CREATE TABLE domains (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    master VARCHAR(128),
    last_check INTEGER,
    type VARCHAR(8) NOT NULL,
    notified_serial INTEGER,
    account VARCHAR(40)
);
 
CREATE TABLE records (
    id INTEGER PRIMARY KEY,
    domain_id INTEGER,
    name VARCHAR(255),
    type VARCHAR(10),
    content VARCHAR(65535),
    ttl INTEGER,
    prio INTEGER,
    disabled BOOLEAN DEFAULT 0,
    ordername VARCHAR(255),
    auth BOOL DEFAULT 1
);

In the domains table, the type column holds NATIVE, PRIMARY, or SECONDARY. The master column stores the primary address for secondary zones. In the records table, the content column stores the record value, prio for MX/SRV priority, and auth marks records answered as authoritative.

#Exploring the Database Directly

Because gsqlite3 is an SQLite file, you can inspect the data directly:

sqlite3 /var/lib/powerdns/pdns.sqlite3 "SELECT id, name, type FROM domains;"
sqlite3 /var/lib/powerdns/pdns.sqlite3 "SELECT name, type, content, ttl FROM records WHERE domain_id = 1;"

The sqlite3 commands above list all zones, then all records of the first zone. Remember: changing the database directly bypasses PowerDNS, so it must be done carefully, and always run pdnsutil rectify-zone afterwards.

#Importing and Exporting Data

#Pulling and Pushing Zones

PowerDNS can convert between zone files and database data. To migrate a zone from BIND or move it between servers:

pdnsutil export-zone-dnssec example.com > /tmp/example.com.zone
pdnsutil import-zone example.com /tmp/example.com.zone

pdnsutil export-zone-dnssec exports the zone including its signed DNSKEY and RRSIG — important for migrating DNSSEC zones without breaking signatures. To migrate an entire database, use the built-in database tools:

sqlite3 /var/lib/powerdns/pdns.sqlite3 ".backup '/tmp/pdns-backup.sqlite3'"

#Restore

Restore by copying the backup and making sure file ownership is correct:

sudo systemctl stop pdns
sudo cp /tmp/pdns-backup.sqlite3 /var/lib/powerdns/pdns.sqlite3
sudo chown pdns:pdns /var/lib/powerdns/pdns.sqlite3
sudo systemctl start pdns

For gmysql, backups use mysqldump and restores use mysql. The principle is the same: stop the service, restore the data, fix permissions, start it again.

#Other Backends

#lmdb: High-Performance Built-in

lmdb is a high-performance embedded database built into PowerDNS. It supports RRset comments and suits high load without a separate database server:

launch=lmdb
lmdb-filename=/var/lib/powerdns/lmdb

#geoip and remote

• geoip: answers based on the client's location using an MMDB database. Good for content distribution and regional split-horizon.
• remote: connects PowerDNS to another system via an HTTP API. Bridges DNS to a CMDB, IPAM, or internal cloud platform.

launch=remote
remote-connection-string=http:127.0.0.1:8085

#The Lifecycle of a Zone

#From Creation to Deletion

The whole cycle you already know, summarized into one flow:

create-zone -> add-record -> rectify -> update serial
   -> transfer ke secondary -> DNSSEC (ep 13) -> delete

To update an existing zone, pdnsutil edit-zone remains the most convenient way. Deletion is done with pdnsutil delete-zone:

pdnsutil delete-zone example.com

#Data Strategies at Scale

At scale, several proven practices apply: separate databases per region, monitor the records table size so SQL queries stay fast, back up regularly and test restores, and use lmdb or replicated gmysql for high load. The API (episode 20) then becomes the single entry point for all data changes.

#Conclusion

Episode 11 closes out the data side of PowerDNS: you understand the domains and records structure, can export-import and back up-restore, know the lmdb, geoip, and remote backends, and see the zone lifecycle as one complete flow.

Key takeaways:

• domains stores zone metadata; records stores every resource record.
• Export with pdnsutil export-zone-dnssec; back up databases with built-in database tools.
• Always run pdnsutil rectify-zone after manual data changes.
• lmdb is a high-performance embedded backend; geoip and remote add special capabilities.
• The zone lifecycle runs from create, edit, rectify, transfer, to delete.

In episode 12, we'll cover dynamic updates and automation — RFC 2136 and enabling DNS updates with pdnsutil enable-dnsupdate, using nsupdate for automatic updates, plus integration from DHCP, Docker, and provisioning scripts.