HOW TO Restore a PKG db in FreeBSD 12

How I restored my FreeBSD package SQLite database.

In my case, I’d removed sqlite3 and ended up with a zero’d out /var/db/pkg/local.sqlite.

Restoring this database apparently cannot be done with
pkg backup -r {BACKUP FILE}

Reading online gave me one clue, and that is: sqlite3 creates a backup DUMP, but that dump file is not readable by pkg backup -r.

Instead, you have to pump the dump back through sqlite (after unxz):

# cp /var/backups/pkg.sql.xz /tmp
# unxz /tmp/pkg.sql.xz
# cat /tmp/pkg.sql | sqlite3 /var/db/pkg/local.sqlite

Verified the package database:

# pkg audit
# pkg check -d -a
# pkg check -s -a
# pkg prime-list

And to take it one step further, what I’d initially wanted to accomplish is to install kanboard.

# pkg install php80-kanboard

The install worked and appeared to automagically install other dependencies it didn’t choose to install before I goofed up sqlite.

C’est tout.