damage to pkg's sqlite data base

[Matthew Seaman]

On 05/12/15 07:17, Scott Bennett wrote:
>      I'm not familiar with sqlite commands, nor with the structure of the
> pkg data base, nor any of several other relevant topics.  But I do have
> questions:
> 
> 	1) how might the damage have happened?

Could be any number of things, but in principle the corruption is
usually due to some IO request that didn't complete successfully.  That
can happen if pkg(8) gets killed at exactly the wrong moment, or if your
pkg database is on an NFS mount and the locking isn't working properly.

> 	2) how might such an event be avoided/prevented in the future?

It's pretty rare judging by the lack of complaints we've had.  So long
as your disk hardware is OK, and you're keeping the pkg DB on a local
disk it should be reliable enough that you don't need to take any
special measures.  However the prudent sysadmin will always have
backups, and there are a couple of periodic scripts that will backup
your package database for you.  One, as I recall, just copies the
local.sqlite file to /var/backup, while the other uses 'pkg backup' to
create a database dump.

> 	3) most urgently, how can I *fix* it?

If your backups don't sort it for you, and you aren't happy to go into
sqlite and mend things by using SQL directly, then you can fix it in the
true heroic fashion:

   1) Make sure that you have packages available for everything
installed on your system, either in whatever repo you're using
(preferred), or by creating pkg tarballs of all the packages installed
on your system:

    # mkdir /tmp/pkg-backup
    # pkg create -a -o /tmp/pkg-backup

(That might be affected by the corruption you've experienced.  Watch out
for any error messages.)

   2) Generate a list of all the *non-automatic* packages you have
installed:

    # pkg query -e "%a == 0" "%n-%v" > /tmp/pkg-list

   3) Move aside your corrupted pkg database:

    # mv /var/db/pkg/local.sqlite{,.save}

   4) Reinstall pkg(8) -- this will reinitialise your database:

    # /usr/sbin/pkg bootstrap -f

   5) Reinstall all the packages you had previously installed:

    # xargs pkg install < /tmp/pkg-backup

      or -- if and only if you could create tarballs of everything
without error -- you can reinstall from those packages:

    # cd /tmp/pkg-backup
    # sed -e 's,$,.txz,' < /tmp/pkg-list | xargs pkg add

This should restore your system to its previous state preserving the
automatic flags on your packages.  It should install any dependencies
required and mark them as automatic.  You will want to list all the
packages installed before and after and compare the two as a sanity
check.  If there are packages that you used to have installed but are no
longer listed, then you'll have the old package files left orphaned on
your drive, which it would be a good idea to clean up.  Doing that I
leave as an exercise for the student.

> 	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?

It's not something that needs doing that often. The general approach
will be -- as shown above -- to create a new package database and
reinstall everything.  You may or may not want to wipe the contents of
/usr/local before you do that (obviously save your config files from
/usr/local/etc and any precious web content from /usr/local/www, etc.
etc.), depending on how badly the database was damaged, how up-to-date
the system was and whether you have a good idea of what packages you had
installed previously or not.

	Cheers,

	Matthew



-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 949 bytes
Desc: OpenPGP digital signature
URL: <http://lists.freebsd.org/pipermail/freebsd-ports/attachments/20150512/6e4728e2/attachment.sig>