"pkg backup -r" results in corrupted database

[t4ko-kun]

After restoring a pkg backup every pkg command results in pkg: sqlite error while executing ALTER TABLE packages ADD licenselogic INTEGER NOT NULL DEFAULT(1); in file pkgdb.c:2483: no such table: packages.

The backup was initially created with pkg backup -d without any error message so i don't know what could have gone wrong.

Using pkg 1.14.5 from the quarterly repo.

[Oxyaxion]

Same problem with pkg 1.16.1

Fresh install, install somes packet

pkg backup -d mybackup
pkg backup -r mybackup

pkg: sqlite error while executing ALTER TABLE packages ADD licenselogic INTEGER NOT NULL DEFAULT(1); in file pkgdb.c:2489: no such table: packages

[Darkemon]

The same problem with pkg 1.16.3 FreeBSD 12.2-RELEASE amd64. To reproduce I do following:

pkg backup -d /tmp/pkg.backup
# disable access to the Internet 
route delete default
pkg update
pkg backup -r /tmp/pkg.backup
pkg info # now it fails

To avoid this problem I just copy /var/db/pkg instead of using pkg backup.

[infra-hdc]

The same problem

$ pkg backup -d /tmp/pkgng_db.dump
Dumping database:
Backing up: 100%
$ su -
#  pkg backup -r /tmp/pkgng_db.dump
Restoring database:
Restoring: 100%
# pkg update
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
# pkg audit
pkg: sqlite error while executing ALTER TABLE packages ADD licenselogic INTEGER NOT NULL DEFAULT(1); in file pkgdb.c:2320: no such table: packages
root@inw-vm028:~ # freebsd-version
13.1-RELEASE-p3
# uname -a
FreeBSD inw-vm028 13.1-RELEASE-p3 FreeBSD 13.1-RELEASE-p3 GENERIC amd64
# pkg --version
1.18.4
# cp /tmp/pkgng_db.dump /root

IMPORTANT UP: To restore local DB, which corrupted while restoring, there is a workaround:

# cd /var/db/pkg
# mv local.sqlite local.sqlite-bak20221110-001
# cp /root/pkgng_db.dump local.sqlite

Thus, there is a working database:

# pkg update
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
# pkg upgrade
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
Checking for upgrades (0 candidates): 100%
Processing candidates (0 candidates): 100%
Checking integrity... done (0 conflicting)
Your packages are up to date.
# pkg install sqlite
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 1 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
        sqlite: 2.8.17_5

Number of packages to be installed: 1

The process will require 2 MiB more space.
386 KiB to be downloaded.

Proceed with this action? [y/N]:

[bapt]

pkg backup command was misleading for many and was removed entirely from next pkg.