damage to pkg's sqlite data base

[andrew clarke]

On Wed 2015-05-13 00:12:51 UTC-0500, Scott Bennett (bennett at sdf.org) wrote:

> > Simply rename your (now) corrupt db, and copy the backup over.
> 
>      However, if I do that, then what happens to all the ports that have
> been updated or added since that version of the data base was backed up?
> I have run "portmaster -a" (with some additional options) quite a few
> times since the lang/gcc problem first appeared, so an old local.sqlite
> will no longer accurately reflect what is currently installed.

You can reinstall just those ports. Check /var/log/messages, eg.

$ grep pkg /var/log/messages
May 12 14:34:38 blizzard pkg: poudriere upgraded: 3.1.4 -> 3.1.6 
May 12 14:38:08 blizzard pkg: git-lite-2.4.0 installed
May 13 08:29:04 blizzard pkg: sqlite3 upgraded: 3.8.9_1 -> 3.8.10.1 
May 13 08:29:05 blizzard pkg: spamassassin reinstalled: 3.4.1_1 -> 3.4.1_1 
May 13 08:29:05 blizzard pkg: ca_root_nss upgraded: 3.18.1 -> 3.19 


> > >     4) I was unable to find any instructions for recreating a pkg data
> > >     base if the data base gets damaged/destroyed.  Is there a way to
> > >     do that that I missed?
> 
>      There must be a way to do this, right?  I mean, really, it's pretty
> fundamental that no new data base be put into production without a way to
> rebuild it.  The FreeBSD developers haven't really broken so ancient and
> basic a principle, have they?  So what's the trick?  What is the method
> to rebuild /var/db/pkg/local.sqlite from scratch based upon the currently
> installed ports/packages?

You can't rebuild it. You couldn't rebuild it in the years before
pkgng existed, either.

The "trick" is to revert to a known-good backup of the pkg database
that's generated daily by /usr/local/etc/periodic/daily/411.pkg-backup
in /var/backups/ :

-rw-r--r--  1 root  wheel   2207320 2015-05-13 04:20:30 pkg.sql.xz
-rw-r--r--  1 root  wheel   2196088 2015-05-12 04:21:24 pkg.sql.xz.2

The .sql.xz files are just a SQLite dump, in xz compressed format.

Regards
Andrew