A couple of weeks ago I made some configuration changes that went awry on my home NAS, and had to do a complete reinstall on the boot drive. No data was lost, and I had a configuration backup, so I was able to get back on the air quickly. But the package system was unusable. 'pkg update' worked, but anything referencing the local package database failed.
'pkg shell' quickly revealed the problem: the database was empty and unconfigured. It didn't even have the empty tables pkg needs. I couldn't re-bootstrap pkg; I couldn't install it from a port because I had no compiler, and I couldn't install a compiler because I had no package system.
Someone who had a similar problem reported success by copying the database elsewhere and deleting the original, then feeding the copy into 'pkg shell'. This was the clue I needed.
Score one for open source! I found the function that initializes the local DB, a function named pkgdb_init() in pkg/libpkg/pkgdb.c. This function assembles a string of SQL commands and feeds them to SQLite. I copied the source code, reformatted it as a text file, and did 'sudo pkg shell < pkg-db-rescue.txt'. It worked - I now have a functioning pkg command.
I've attached the SQL file here. Be aware that this worked for FreeBSD 12.1; older or newer releases may require a different schema or a different version number.
I hope someone else finds this useful.
'pkg shell' quickly revealed the problem: the database was empty and unconfigured. It didn't even have the empty tables pkg needs. I couldn't re-bootstrap pkg; I couldn't install it from a port because I had no compiler, and I couldn't install a compiler because I had no package system.
Someone who had a similar problem reported success by copying the database elsewhere and deleting the original, then feeding the copy into 'pkg shell'. This was the clue I needed.
Score one for open source! I found the function that initializes the local DB, a function named pkgdb_init() in pkg/libpkg/pkgdb.c. This function assembles a string of SQL commands and feeds them to SQLite. I copied the source code, reformatted it as a text file, and did 'sudo pkg shell < pkg-db-rescue.txt'. It worked - I now have a functioning pkg command.
I've attached the SQL file here. Be aware that this worked for FreeBSD 12.1; older or newer releases may require a different schema or a different version number.
I hope someone else finds this useful.