Backing up weewx.sdb

[Jonathan Ryshpan]

My weewx database, which is on OaklandWeather,  is shared via nfs with my main system, on which key files and folders are backed up daily using rsnapshot.  The files being backed up include weewx.sdb. I have just noted this in the WeeWx User Manual:

Do not make the copy of the SQLite database while in the middle of a transaction! Schedule the backup for immediately after an archive record is written, and then make sure the backup completes before the next archive record arrives. Alternatively, stop WeeWX, perform the backup, then restart WeeWX.

What are the bad consequences of backing up during a transaction? Is there just one corrupt entry, or is the whole backup corrupt? Does anyone know a way to control the backup process so that this particular file in not copied during a transaction?

-- 

Many Thanks - Jonathan Ryshpan <jon...@pacbell.net>

	The best is the enemy of the good -- Voltaire

[michael.k...@gmx.at]

I have an archive_interval of 300s, the database is usually updated within the first 20s after each 5th minute. The cron jobs that backup my sdb files run at minute 1 of every hour/day/week. I've never came across any problems, which doesn't mean this way to work will work always that smoothly. But: having an hourly backup, a daily backup, and a weekly backup done this way, chances are, I'll find a working copy anywhere :)

[John Kline]

You might find these shell script fragments helpful.  If you call retry_backup_copy With arguments of the database to backup (weewx.sdb) and a destination file to backup to, the code will check to see if the cksum changed during the copy and also run an integrity check on the copy.  If the cksum changed or the integrity check fails, the copy is retried.

make_backup_copy() {

        db="$1"

        tmp_db_copy="$2"

        check_sum="`/usr/bin/cksum $db | cut -d ' ' -f 1`"

        cp "$db" $tmp_db_copy

        check_sum_after="`/usr/bin/cksum $db | cut -d ' ' -f 1`"

        if [ "$check_sum" != "$check_sum_after" ]; then

                echo "$db changed during cp!"

                return 1

        fi

        integrity_check="`echo "pragma integrity_check;" | sqlite3 "$tmp_db_copy"`"

        if [ "$integrity_check" != "ok" ]; then

                echo "$tmp_db_copy failed integrity check!"

                return 2

        fi

        return 0

}

retry_backup_copy() {

    db="$1"

    tmp_db_copy="$2"

    retval=99

    attempts=0

    while [ "$retval" -ne 0 ]; do

            attempts="`/usr/bin/expr $attempts + 1`"

            if [ "$retval" -ne 99 ]; then

                    echo "make_backup_copy attempt $attempts"

            fi

            make_backup_copy "$db" "$tmp_db_copy"

            retval="$?"

    done

}

On Mar 13, 2023, at 4:38 AM, michael.k...@gmx.at <michael.k...@gmx.at> wrote:

I have an archive_interval of 300s, the database is usually updated within the first 20s after each 5th minute. The cron jobs that backup my sdb files run at minute 1 of every hour/day/week. I've never came across any problems, which doesn't mean this way to work will work always that smoothly. But: having an hourly backup, a daily backup, and a weekly backup done this way, chances are, I'll find a working copy anywhere :)

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/2be69d2d-c287-449a-aaa4-825b998118edn%40googlegroups.com.