How to recreate local.sqlite?

OK. I ran into a recent problem with pkg(8). Where it corrupted it's sqlite3(1) database of the locally installed ports /var/db/pkg/local.sqlite. I am aware of the option in pkg(8) to (re)create the database from a backup, pkg backup -r /path/to/db/backup.

But given that pkg(8) itself borked the database, and that I need to know I can reliably recreate the database, if/when pkg(8) can't/won't. I was wondering if anyone might know the incantation for doing it with sqlite3(1).

I've already learned MySQL, and PostgreSQL. But sqlite(1) is quite a bit different. :(

Thank you for all your time, and consideration.

Last edited:
wblock@. Yes. I have the backup. I've unpacked, and examined it to insure it's the one I want. But would very much like to how to feed it to sqlite3(1).

It (pkg(8)) no longer knows most of the previously built/installed ports have been built, and installed. It happened during the course of the build/install of a meta-port, in the ports tree. :(

The backup is ~30 ports shy of what is currently installed. But it's a whole lot easier to reconcile, than nearly starting over. Which is what I'll need to do if I can't (re)install the backup.

I figure I can simply move the bad copy of local.sqlite aside. Then recreate it via some sqlite3(1) incantation.

I hope my need(s) were clearer this time. :)

Thank you both, for your replies.

Well, I am sort of in the same situation. A v9 failed to convert using pkg2ng so I copied a local.sqlite from another machine that had just a few more ports installed and that had been converted. pkg install -- to reinstall all or most important ports -- to register them properly -- wants to install [most of the time, not always ] clearly extra ports, after I deleted a few to save space, as if registering a newer version of one installed needed something not in its build-depends-list nor run-depends-list. In a few years or sooner, something may transpire where it is more reliable in that particular instance, or I may come across a guide to fix it. Maybe even from this thread.
pkg-backup(8) shows options to dump and restore the database. Untested by me, though.
Thank you, wblock@, for your thoughtful reply.
Indeed it does. I even mentioned that in the OP. :)
But, as I also mentioned; I was hoping to find out if it's possible to do it with sqlite3(1). The database engine used to make, and keep the data itself. Point being; if pkg(8) is unable to do the job. How else would I, or anyone else recover?
Thus far, the prospects for anyone recovering from such a scenario look pretty bleak. :confused:
Sigh. Looks like I'll need to go to sqlite3(1) school, to learn yet another SQL language.

Thank you again, wblock@, for taking the time to reply.

Look at the contents of the backup file. It looks like it has all the SQL commands to recreate the database.
Hello, wblock@, and thanks for the reply.
Indeed. I noticed that too. But was unsure how to move forward. The best I can figure is
cd /var/db/pkg
mv ./local.sqlite ./_bad.local.sqlite
sqlite3 local.sqlite ATTACH PKG.sql
But am unsure. Still reading sqlite3(1), and it's associated documentation. Hoping to get the correct incantation.

Thanks again, wblock@.

OK. Just got brave (or crazy), and gave it a go. Here's what I did
cd /var/db/pkg
mv ./local.sqlite ./_BAD.local.sqlite
with the backup file in this same directory, as pkg.sql. I then did
sqlite3 local.sqlite
Which gave me the sqlite3(1) prompt
I then issued
sqlite> .read pkg.sql
after some churning. The sqlite3(1) prompt returned. I examined the contents of the directory in another terminal, and discovered there was a new local.sqlite. With a size that I would expect from a DB with as many ports as I had built on this server. So I simply issued
sqlite> .quit
While this all looks promising. I won't know until I perform some more investigation. But thought it prudent to at least update my progress here. For others that might be following this, and feeling inclined to reply.

Ugh... Well that (the above) didn't do it.
Issuing pkg info resulted in
pkg: sqlite error while executing CREATE TABLE licenses (id INTEGER PRIMARY KEY, license
TEXT NOT NULL UNIQUE );CREATE TABLE pkg_licenses_assoc (package_id INTEGER
(package_id, license_id));CREATE VIEW pkg_licenses AS SELECT origin, license FROM
packages INNER JOIN pkg_licenses_assoc ON = pkg_licenses_assoc.package_id
INNER JOIN licenses ON pkg_licenses_assoc.license_id =;CREATE TRIGGER
IGNORE INTO licenses(license) values (NEW.license);INSERT INTO
pkg_licenses_assoc(package_id, license_id) VALUES ((SELECT id FROM packages where
origin = NEW.origin), (SELECT id FROM categories WHERE name =;END; in file
pkgdb.c:2252: table licenses already exist
Thanks for the good times, pkg(8). But I think it's time I replace you with something I can depend on.

OK. Just for fun. I gave it a try
# pkg backup -r /var/db/pkg/pkg.sql
Restoring database:
Restoring: 100%
pkg: sqlite error while executing backup step in file backup.c:101: not an error
pkg: sqlite error -- (null)
OK fine. I'll just feed you the original, in its unaltered (packed) form.
# pkg backup -r /var/db/pkg/pkg.sql.xz
Restoring database:
Restoring: 100%
pkg: sqlite error while executing backup step in file backup.c:101: not an error
pkg: sqlite error -- (null)
Equally unsatisfying. Interesting too. Because this backup, and the additional one, also created by periodic(8) both have the same size, and I know they were copies of fully working, and perfectly valid databases. I queried them both, when they were still active. There was absolutely no evidence of problems. Yet neither of them will be accepted by pkg(8), or, perhaps more accurately; sqlite3(1). :(
I was afraid this day would come. Now there appears to be no salvation. :mad:

Thanks for the reply, wblock@. Even if the results were not what I had hoped for.

After the first "non"-error, did you try pkg info or something similar to see if that was really a problem?
OK appears I might have gotten to a usable state, using the snapshot of the /var/db/pkg kept along with the local.sqlite backup. It's called pkgdb.bak.tbz, or pkgdb.bak2.tbz, depending how many backup revisions you keep. I simply unpacked the file, which contained the entire tree, including /var. Then I packed up the contents of the pkg/ folder. Deleted the contents of my current pkg/ folder. Then, finally, unpacking the backup copy in there.

It's all too early to tell. But my initial impression, seems promising.

In the end. What still really bothers me; is why the stated method of restoring one's local.sqlite doesn't seem to be a valid path. Anyone have any additional thoughts? This seems to be a pretty significant discrepancy, no? Also a bit unnerving.

Either a bug in the man page or a bug in the program. Ask on freebsd-ports, or enter a bug report. Clarification on some of the manual pages would be very welcome.
Thanks for the confirmation. I had intended to ask on the lists, but wasn't sure if ports@ would be the best, or not. If I get a confirmation on the list(s). I'll submit a pr(1).

Thanks for all your help, and feedback, wblock@.

mv /var/db/pkg/local.sqlite /var/db/pkg/local.sqlite.backup
xzcat /var/backups/pkg.sql.xz.7 | sqlite3 /var/db/pkg/local.sqlite
sqlite3 /var/db/pkg/local.sqlite
sqlite> PRAGMA user_version=30;
sqlite> .quit

Worked for me.
Thanks @pdb - that:

mv /var/db/pkg/local.sqlite /var/db/pkg/local.sqlite.backup
xzcat /var/backups/pkg.sql.xz.7 | sqlite3 /var/db/pkg/local.sqlite

brought me back my corrupted local.sqlite which was not working with

pkg backup -r /var/backups/pkg.sql.xz /var/db/pkg/local.sqlite

as it seems that backups themself were corrupted, too.
pbd, icecoke and others

I had the same problem by link, but database wasn't correct after this, and pkg(8) wasn't installing:
1st of all, I've ran:
mv /var/db/pkg/local.sqlite /var/db/pkg/local.sqlite.backup1
2)Then I installed older version of pkg (it possible, if you'll rename old file /var/db/pkg/local.sqlite to any backup name).
pkg install -y pkg
Type [Y]es.
3) Then I have done this:
mv /var/db/pkg/local.sqlite /var/db/pkg/local.sqlite.backup2
xzcat /var/backups/pkg.sql.xz | sqlite3 /var/db/pkg/local.sqlite
sqlite3 /var/db/pkg/local.sqlite
sqlite> PRAGMA user_version=30;
sqlite> .quit

4) But database wasn't opening, because it was older version of pkg, than it was before updating.
This cmd helped me to repair database of installed packages:
This cmd helped me to repair database of installed packages:
That command doesn't do anything anymore. It was used to convert the old package registrations to PKGNG. As there are no old package registrations on 10 and above this command won't do anything.

Probably the primary reason why you're getting the utimesat error is because you're trying to run a recent pkg(8) on an unsupported FreeBSD version.
Hmm. Looks like there isn't much to be done if pkg sh*its the bed? None of the steps defined above (dumping and restoring the local.sqlite db) work any better now than as noted above.
I have been forced to reinstall pkg from source and then use
portmaster --no-confirm `cat ~installed-ports`
to rebuild it all. Looks like pkg has reached the same level of maturity as the old portupgrade/pkgdb combo…