Running 12.3-Stable and pkg 1.17.5_1. When Restoring the pkg.sql from /var/backups (handled by /usr/local/etc/periodic/daily/411.pkg-backup) Restore fails. When backing up via pkg backup -d and restoring this there is no error. Tried this on 3 machines, same behaviour. === root@nbsdrosen (pts/8) /var/db/pkg 11(2) -> pkg -ddddd backup -r pkg.sql DBG(1)[5616]> pkg initialized DBG(4)[5616]> Pkgdb: running 'PRAGMA user_version;' DBG(4)[5616]> Pkgdb: executing 'PRAGMA foreign_keys = ON;' DBG(4)[5616]> Pkgdb: executing 'PRAGMA mmap_size=268435456;' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO mtree(content) VALUES(?1)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR REPLACE INTO packages( origin, name, version, comment, desc, message, arch, maintainer, www, prefix, flatsize, automatic, licenselogic, mtree_id, time, manifestdigest, dep_formula, vital)VALUES( ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, (SELECT id FROM mtree WHERE content = ?14), NOW(), ?15, ?16, ?17 )' DBG(4)[5616]> Pkgdb: preparing statement 'UPDATE deps SET origin=?1, version=?2 WHERE name=?3;' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO deps (origin, name, version, package_id) VALUES (?1, ?2, ?3, ?4)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO files (path, sha256, package_id) VALUES (?1, ?2, ?3)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR REPLACE INTO files (path, sha256, package_id) VALUES (?1, ?2, ?3)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO directories(path) VALUES(?1)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_directories(package_id, directory_id, try) VALUES (?1, (SELECT id FROM directories WHERE path = ?2), ?3)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO categories(name) VALUES(?1)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_categories(package_id, category_id) VALUES (?1, (SELECT id FROM categories WHERE name = ?2))' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO licenses(name) VALUES(?1)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_licenses(package_id, license_id) VALUES (?1, (SELECT id FROM licenses WHERE name = ?2))' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO users(name) VALUES(?1)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_users(package_id, user_id) VALUES (?1, (SELECT id FROM users WHERE name = ?2))' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO groups(name) VALUES(?1)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_groups(package_id, group_id) VALUES (?1, (SELECT id FROM groups WHERE name = ?2))' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO script(script) VALUES (?1)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_script(script_id, package_id, type) VALUES ((SELECT script_id FROM script WHERE script = ?1), ?2, ?3)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO option (option) VALUES (?1)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_option(package_id, option_id, value) VALUES (?1, (SELECT option_id FROM option WHERE option = ?2),?3)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO shlibs(name) VALUES(?1)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO pkg_shlibs_required(package_id, shlib_id) VALUES (?1, (SELECT id FROM shlibs WHERE name = ?2))' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO pkg_shlibs_provided(package_id, shlib_id) VALUES (?1, (SELECT id FROM shlibs WHERE name = ?2))' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO annotation(annotation) VALUES (?1)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR ROLLBACK INTO pkg_annotation(package_id, tag_id, value_id) VALUES (?1, (SELECT annotation_id FROM annotation WHERE annotation = ?2), (SELECT annotation_id FROM annotation WHERE annotation = ?3))' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO pkg_annotation(package_id, tag_id, value_id) VALUES ( (SELECT id FROM packages WHERE name = ?1 ), (SELECT annotation_id FROM annotation WHERE annotation = ?2), (SELECT annotation_id FROM annotation WHERE annotation = ?3))' DBG(4)[5616]> Pkgdb: preparing statement 'DELETE FROM pkg_annotation WHERE package_id IN (SELECT id FROM packages WHERE name = ?1) AND tag_id IN (SELECT annotation_id FROM annotation WHERE annotation = ?2)' DBG(4)[5616]> Pkgdb: preparing statement 'DELETE FROM annotation WHERE annotation_id NOT IN (SELECT tag_id FROM pkg_annotation) AND annotation_id NOT IN (SELECT value_id FROM pkg_annotation)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_conflicts(package_id, conflict_id) VALUES (?1, (SELECT id FROM packages WHERE name = ?2))' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_provides(package_id, provide_id) VALUES (?1, (SELECT id FROM provides WHERE provide = ?2))' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO provides(provide) VALUES(?1)' DBG(4)[5616]> Pkgdb: preparing statement 'UPDATE packages SET manifestdigest=?1 WHERE id=?2;' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO config_files(path, content, package_id) VALUES (?1, ?2, ?3);' DBG(4)[5616]> Pkgdb: preparing statement 'UPDATE config_files SET content=?1 WHERE path=?2;' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_requires(package_id, require_id) VALUES (?1, (SELECT id FROM requires WHERE require = ?2))' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO requires(require) VALUES(?1)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT OR IGNORE INTO lua_script(lua_script) VALUES (?1)' DBG(4)[5616]> Pkgdb: preparing statement 'INSERT INTO pkg_lua_script(lua_script_id, package_id, type) VALUES ((SELECT lua_script_id FROM lua_script WHERE lua_script = ?1), ?2, ?3)' Restoring database: Restoring: 100% pkg: sqlite error while executing backup step in file backup.c:98: not an error pkg: sqlite error -- (null) Files created by pkg backup -d (pkg_backup.sql) and created by the periodic (pkg.sql) differ: -rw-r--r-- 1 root wheel - 78270464 4 März 11:42 pkg_backup.sql -rw-r--r-- 1 root wheel - 51963917 4 März 03:04 pkg.sql
This couldnt work this way as i noticed now. The 'pkg backup' command and the 411.pkg-backup script produce 2 different backup archives. The right way to restore a backup from the periodic is: rm /var/db/pkg/local_sqlite xzcat /var/backup/pkg.sql.xz | pkg shell I cant find these documented anywhere, this should be noted in either 411.pkg-backup or the pkg-backup manpage.