weewx.sdb backups revisited

[WindnFog]

Reading the list archive, the documentation, and searching the Internet about the "best" way to backup SQLite databases, I conclude cold backups are safer than a "hot" cp or rsync. That said, I am doing a hot rsync (takes 2-3 seconds) to another LAN system and a cloud server at 01:18 AM daily. Since I only write archive records to the database at XX:00, XX:05, XX:10, etc., I *think* this is safe. The documentation says:

"Do not make a 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."

I prefer not to stop/start weewx every day, but I will if necessary. Are their situations where some part of weewx could be modifying the database between the archive writes every 5 minutes? I have done a couple of restores over the past 4-5 years with no issues, but am I playing with fire?

What are you guys doing vis-à-vis weewx.sdb backups?

- Paul VE1DX

[Thomas Keffer]

Unless you are running some unusual service, there should be no situations where weewx is modifying the database between archive events.

-tk

--
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/dc168739-054a-4b6d-950c-6e9aa7f83e74%40googlegroups.com.

[Paul Dunphy]

Thanks,Tom!  I'm not doing anything out of the ordinary, so I shall keep doing what I've been doing.

Take care, Paul VE1DX

You received this message because you are subscribed to a topic in the Google Groups "weewx-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/weewx-user/UWHHpwkoVJ8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zECs61uvq5%3DGKJUbTtcPjyPAKWB0L9LpNgzGZooornCW_A%40mail.gmail.com.

[vince]

On Tuesday, November 12, 2019 at 7:48:21 AM UTC-8, WindnFog wrote:

What are you guys doing vis-à-vis weewx.sdb backups?

 

The long discussions in the list from a couple years ago were far too detailed (and good) to repeat from memory.

FWIW, I've been doing the same thing for many many years.  My repo is at https://github.com/vinceskahan/vds-weewx-backup-data if you want to see my script.

In short my process is:

• copy weewx.sdb to a scratch directory, then gzip the 'copy'.
  
• scp the file weekly (manually) to my Macbook, which syncs to the cloud into Dropbox
• I 'have' verified hundreds of daily stashed backups and they're all good, for my case (spinning disk, not a pi with SD card)

The sqlite3 incantation to check your backup is ok is at https://serverfault.com/questions/8048/how-can-i-verify-that-a-sqlite-db3-file-is-valid-consistent

[Greg Troxel]

WindnFog <foc...@gmail.com> writes:

> Reading the list archive, the documentation, and searching the Internet
> about the "best" way to backup SQLite databases, I conclude cold backups
> are safer than a "hot" cp or rsync. That said, I am doing a hot rsync
> (takes 2-3 seconds) to another LAN system and a cloud server at 01:18 AM
> daily. Since I only write archive records to the database at XX:00, XX:05,
> XX:10, etc., I *think* this is safe. The documentation says:

Not to contradict the advice you've gotten which all seems great, but
you can also do

sqlite3 weewx.sdb .dump > weewxdb.txt

which gets you a text representation that was extracted with database
commands and thus should be ok (fully in a before or after state with
respect to a database transaction) even with concurrent access. You
would then back up that file. This scheme is common with the postgresql
world, as I understand it.