pdns-sql-slave-prune

#!/bin/sh
# pdns-sql-slave-prune (part of ossobv/vcutil) // wdoekes/2017,2021,2023
#   // Public Domain
#
# Reads PowerDNS SQL backend SLAVE data for stale domains and prunes
# those.
#
# Usage:
#
#   # pdns-sql-slave-prune
#   Pruning SLAVE domain 'old-removed-domain.tld' (704)
#   ...
#
# It works by checking your PowerDNS SQL database backend for domains
# with:
# - type set to SLAVE
# - that haven't been 'last_check' updated in the last 3 days
#
# An extra check is performed before removal to confirm that the AVG of
# the last_check updates is at least two days ago. If the average is
# older, it aborts because the slaving might be broken.
#
#
# Tested with PowerDNS 4.0, but should work with PowerDNS 3.4 as well.
# Silence regular output by discarding stdout.
#
set -eu

test -z "${DBNAME:-}" && \
    DBNAME=$(find /etc/powerdns/ -name '*.conf' -type f |
        xargs grep -hE '^g(mysql|pgsql)-dbname=' | sed -e 's/[^=]*=//')
test -z "${DBTYPE:-}" && \
    DBTYPE=$(find /etc/powerdns/ -name '*.conf' -type f |
        xargs sed -ne 's/^g\(mysql\|pgsql\)-dbname=.*/\1/p')

case "$DBTYPE" in
mysql)
    query() {
        mysql --defaults-file=/etc/mysql/debian.cnf "$DBNAME" -BNe "$1"
    }
    SQL_EPOCH='UNIX_TIMESTAMP()'
    SQL_TRUE=1
    ;;
pgsql)
    if test $(whoami) != postgres; then
        cd /
        exec sudo -Hupostgres env DBNAME=$DBNAME DBTYPE=$DBTYPE $0 "$@"
        exit 2  # should not get here
    fi

    query() {
        psql -tAF "$(printf '\t')" -c "$1" "$DBNAME"
    }
    SQL_EPOCH='EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)'
    SQL_TRUE=t
    ;;
*)
    echo "NotImplementedError: DBTYPE=$DBTYPE" >&2
    exit 1
    ;;
esac

AVG_OLD_TIMESTAMP="($SQL_EPOCH - 2 * 86400)"  # '-2 days'
OLD_TIMESTAMP="($SQL_EPOCH - 3 * 86400)"      # '-3 days'


test_slave_running() {
    #return  # <-- DISABLE this check?
    if test "$(query "
            SELECT AVG(last_check) > $AVG_OLD_TIMESTAMP AS val
            FROM domains WHERE type = 'SLAVE';")" != "$SQL_TRUE"; then
        updated=$(query "
            SELECT name FROM domains WHERE last_check >= $AVG_OLD_TIMESTAMP
              AND type = 'SLAVE' ORDER BY name;")
        not_updated=$(query "
            SELECT name FROM domains WHERE last_check < $AVG_OLD_TIMESTAMP
              AND type = 'SLAVE' ORDER BY name;")
        if test -z "$updated"; then
            cat >&2 <<EOF
Slave updates are probably broken!
No domain appears to have been updated recently.

Stopping the pruning to avoid accidents..
EOF
        else
            updated_len=$(echo "$updated" | wc -l)
            not_updated_len=$(echo "$not_updated" | wc -l)
            # NOTE: Using tail instead of head in the sample, because we don't
            # want to see only PTR (in-addr.arpa.) ranges.
            cat >&2 <<EOF
Slave updates _might_ be broken.
$updated_len domains are updated, $not_updated_len are not.

Sample of updated domains:
- ...
$(echo "$updated" | sed -e 's/^/- /' | tail -n10)

Sample of not updated domains:
- ...
$(echo "$not_updated" | sed -e 's/^/- /' | tail -n10)

Perhaps you deleted a lot of domains -- more than half? (The not-updated ones.)
If so, you should disable this check temporarily, so the pruning can commence.

See the to-be-pruned candidates for yourself:

  SELECT name FROM domains WHERE last_check < $OLD_TIMESTAMP
    AND type = 'SLAVE' ORDER BY name;

Stopping for now..
EOF
        fi
        exit 1
    fi
}

old_slave_domains() {
    query "
        SELECT id, name FROM domains
        WHERE type = 'SLAVE' AND last_check < $OLD_TIMESTAMP
        ORDER BY name, id;"
}

prune_domain() {
    local id="$1"
    local domain="$2"
    if ! test "$((id-1))" -lt "$id"; then
        echo "Not an integer id for domain '$domain': $id" >&2
        exit 1
    fi

    echo "Pruning SLAVE domain '$domain' ($id)"
    query "
        DELETE FROM comments WHERE domain_id = $id;
        DELETE FROM cryptokeys WHERE domain_id = $id;
        DELETE FROM domainmetadata WHERE domain_id = $id;
        DELETE FROM records WHERE domain_id = $id;
        DELETE FROM domains WHERE id = $id;"
}

# Only prune data if most of the slaving works.
test_slave_running || exit 1

# Do some pruning.
TAB=$(printf '\t')
old_slave_domains | while read line; do
    id=${line%$TAB*}
    domain=${line#*$TAB}
    prune_domain $id $domain
done

# vim: set ts=8 sw=4 sts=4 et ai: