damage to pkg's sqlite data base

[andrew clarke]

On Wed 2015-05-13 19:00:27 UTC+1000, Peter Jeremy (peter at rulingia.com) wrote:

> On 2015-May-13 18:12:44 +1000, andrew clarke <mail at ozzmosis.com> wrote:
> >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 
> 
> That assumes you have syslog messages back to when you started using pkg.
> syslog was never intended to provide an audit trail.

True. I'd been wondering about why the pkg developers didn't add
logging functionality, but it looks like I just needed to add this to
/etc/syslog.conf:

!pkg
*.*                                             /var/log/pkg.log

then run:

sudo touch /var/log/pkg.log
sudo service restart syslogd

I think that's correct. I'm not sure the syslog.conf entry above will
include pkg-static - maybe it should be "!pkg*" instead? I must admit
I find the syslog.conf man page to be not very helpful.

$ cat /var/log/pkg.log 
May 13 20:58:25 blizzard pkg: jive-1.1 deinstalled
May 13 20:58:29 blizzard pkg: jive-1.1 installed

I tend to think pkg.log creation & use should be a normal thing for a
new FreeBSD install.

> >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
> 
> Assuming that they aren't corrupt.  But that's better than nothing.  Note
> that the backup is taken every day, whether or not there has been any
> change to the pkg database, so you have 2 days of backups, not the last
> two revisions.

Yes, that's a deficiency in the periodic script. Probably not
difficult to improve either, with the correct use of /usr/bin/diff.

> On 2015-May-13 18:17:12 +1000, andrew clarke <mail at ozzmosis.com> wrote:
> >Actually I was wrong about this. The pkg command has the sqlite3
> >interpreter built-in, accessed via "pkg shell", that opens local.sqlite
> >by default:
> 
> Some experimenting suggests that none of the pragma commands work in
> "pkg shell", so you probably will need to find a copy of sqlite3.

Oh dear, that's unfortunate.

Regards
Andrew