How to backup and restore /var/db/pkg/

[Cedric62]

I'm not sure where to begin with this, and maybe there is a chance that I don't understand things well enough to adequately describe what I've discovered, but I will try my best anyway.

To begin with, if I examine the contents of /var/db/pkg, below is what I find on my machine.

Simon@Asus:/var/db/pkg $ ls -la
total 97616
drwxr-xr-x   4 root wheel      512 Jul 24 21:05 .
drwxr-xr-x  17 root wheel     1024 Jul 15 20:58 ..
-rw-r--r--   1 root wheel 99889152 Jul 24 20:43 local.sqlite
drwxr-xr-x   5 root wheel      512 Jun 29 12:42 repos
drwxr-xr-x   2 root wheel      512 Jun 29 12:32 triggers

So, my understanding is that the above file called local.sqlite is a special database file used by the pkg package manager to keep track of which files have been locked. I believe that this must be where such information is stored, because in the past I neglected to include the contents to /var/db/pkg, and thus when I restored my operating system from a backup, the package manager was no longer able to remember which packages I had locked on my system, until I took the time to re lock them again, following the restore process.

Below is an example of how I'm considering to modify the script I use to backup my important files and packages, and this method has worked reasonably well for me for the past few months.
The two or three lines towards the bottom of the script referencing /var/db/pkg are new, and I have confirmed that they are able to create a copy of local.sqlite within the backup folder located at /diskbkp/backup/var/db/pkg/. Please see below:

# Check if the script is being run by root
if [ "$(id -u)" -ne 0 ]; then
    echo "This script requires superuser privileges."
    echo "Please enter the root password below:"
   
    # Re-run the script with su
    # -m preserves the environment
    # -c executes the following command
    exec su -m root -c "/bin/sh $0 $@"
fi

# Your actual script logic starts here
echo "Success! You are now running as: $(whoami)"
# Add your administrative commands below

# Acknowledge the input securely
echo "Password received (length: ${#password})"

# Use the variable $password for the intended secure operation
# Payload starts below
# USING RSYNC TO BACKUP AND RESTORE SYSTEM DIRECTORIES

# +++ Copy settings and home directory to backup: +++

mkdir -p /diskbkp/backup

# Prepare a list of installed packages with below command:
pkg prime-list > /diskbkp/backup/packagelist.txt

mkdir -p /diskbkp/backup/boot
mkdir -p /diskbkp/backup/usr/local
mkdir -p /diskbkp/backup/home
mkdir -p /diskbkp/backup/etc
mkdir -p /diskbkp/backup/var/cache/pkg
mkdir -p /diskbkp/backup/var/db/pkg

# COPY SYSTEM DIRECTORIES

rsync -avhpo --progress /boot/loader.conf /diskbkp/backup/boot
rsync -avhpo --progress /usr/local/etc /diskbkp/backup/usr/local
rsync -avhpo --progress /home /diskbkp/backup/
rsync -avhpo --progress /etc /diskbkp/backup/
rsync -avhpo --progress /var/db/pkg /diskbkp/backup/var/db/

pkg create -a -o /diskbkp/backup/var/cache/pkg
echo "Script finished."
exec /bin/sh

However, the below script which I use to restore my system is really what I'm wondering about, and I'm now particularly looking for advice on the section titled, RESTORE SYSTEM PACKAGES.

I believe that the first line within the section RESTORE SYSTEM PACKAGES should be able to restore the backed up file called local.sqlite, back to its original location at /var/db/pkg. The next line after that should re add all of my packages from a backup folder located at /diskbkp/backup/var/cache/pkg/*. I have been using the pkg add method to restore my packages for several months, and it seem to work well. However, now I'm wondering if the pkg add function will automatically update the recently restored database file (restored by the previous line) local.sqlite located within /var/db/pkg? Or, have I misunderstood how packages and package locks are handled?

#!/bin/sh
# Restore from USB:

# Install FreeBSD in the usual way then use below commands to restore settings & home directory:

# RESTORE SYSTEM DIRECTORIES
# KEEP THESE FIRST (System & Configurations)
rsync -avhpo --progress /diskbkp/backup/boot/loader.conf /boot
rsync -avhpo --progress /diskbkp/backup/usr/local/etc /usr/local
rsync -avhpo --progress /diskbkp/backup/home /
rsync -avhpo --progress /diskbkp/backup/etc /

# RESTORE SYSTEM PACKAGES
rsync -avhpo --progress /diskbkp/backup/var/db/pkg /var/db
pkg add /diskbkp/backup/var/cache/pkg/*

echo "Script finished.
"

Any information greatly appreciated.