Re: [weewx-user] Re: table archive_day_barometer already exists
Thanks Dave, appreciate the share, I'll give it a try as well. Cheers Colin On Sat, 15 Dec 2018, 11:51 Dave, > > Thanks for doing this and sharing it. I plan to try it out over the > weekend. I'll let you know how it works in my setup! > phil > > On Friday, December 14, 2018 at 5:46:26 PM UTC-5, Dave Harper wrote: >> >> Phil (and anyone else interested), >> >> The script is now written and I have spent most of the day testing it. >> It has currently been running for about three hours now and seems to be >> running correctly. I'm attaching the script and a set of instructions on >> how to configure and run it. The script creates and maintains a "last >> known good" copy of the weewx.sdb database at whatever interval the user >> specifies. It also attempts to be as robust as possible, notifying the >> user (via email) of any exception conditions rather than simply "failing >> silently". If you (or anyone else) runs it, I'd be interested in getting >> feedback as to any issues discovered. >> >> Dave >> > -- > 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+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[weewx-user] Re: table archive_day_barometer already exists
Dave, Thanks for doing this and sharing it. I plan to try it out over the weekend. I'll let you know how it works in my setup! phil On Friday, December 14, 2018 at 5:46:26 PM UTC-5, Dave Harper wrote: > > Phil (and anyone else interested), > > The script is now written and I have spent most of the day testing it. It > has currently been running for about three hours now and seems to be > running correctly. I'm attaching the script and a set of instructions on > how to configure and run it. The script creates and maintains a "last > known good" copy of the weewx.sdb database at whatever interval the user > specifies. It also attempts to be as robust as possible, notifying the > user (via email) of any exception conditions rather than simply "failing > silently". If you (or anyone else) runs it, I'd be interested in getting > feedback as to any issues discovered. > > Dave > -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[weewx-user] Re: table archive_day_barometer already exists
Phil (and anyone else interested), The script is now written and I have spent most of the day testing it. It has currently been running for about three hours now and seems to be running correctly. I'm attaching the script and a set of instructions on how to configure and run it. The script creates and maintains a "last known good" copy of the weewx.sdb database at whatever interval the user specifies. It also attempts to be as robust as possible, notifying the user (via email) of any exception conditions rather than simply "failing silently". If you (or anyone else) runs it, I'd be interested in getting feedback as to any issues discovered. Dave -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. Instructions.pdf Description: Adobe PDF document #! /usr/bin/env python3 # # # sdbCheck.py # # # # The primary purpose of this script is to create and maintain a "last known # # good" copy of the weeWX database. To do this, it makes a copy of the weeWX # # database in the script starting directory (waiting, if necessary, until the # # database is not being updated). The integrity of the copy is checked using # # sqlite3 and, if it is good, it replaces the previous copy in the ./Backup# # directory. If a problem is found an email is sent to the user. A small log # # file is also created indicating the results of the run. # # # # The script is intended to be run periodically by the operating system using # # whatever support the OS provides (cron, Task Scheduler, etc.). It is also # # intended to run as "safely" as possible without user intervention, so the# # first thing the script does is to verify that the previous run happened when # # it was supposed to and that it completed correctly. If not, an email is # # sent to the user.# # # # There are a number of script options that can be configured by the user, # # such as email address, period of weeWX updates, etc. These are listed below # # and must be set prior to operation. To assist in verifying that they are# # set correctly, the script can be manually started on the command line with # # anything as a parameter. When the script is started, it checks the length # # of sys.argv and, if it is more than 1, it will perform a configuration check # # and then exit after printing the results on the screen and sending a test# # email. # # # # For support issues, please contact d...@dlharper.net. Feel free to modify # # and / or redistribute this script. If you add a useful feature, please let # # me know. # # # # Import the modules that will be used throughout this script. # import sys import os import os.path import time from datetime import datetime import shutil from shutil import copyfile # The following options can be used to tailor the script to a specific # # environment. # WEEWX_DIR = "" # location of the weewx.sdb file WEEWX_UPDATE = -1 # number of seconds between weewx.sdb updates SCRIPT_UPDATE = -1 # number of seconds between runs of this script INSTALL_DIR = "" # full path to where this script is installed EMAIL_ADDR = ""# login address on email host EMAIL_PASSWD = ""# email host login password EMAIL_HOST = "" # email host EMAIL_PORT = -1
[weewx-user] Re: table archive_day_barometer already exists
Phil, No problem. Just starting it now and as soon as it's done and tested, I'l post a copy. Dave On Wednesday, December 12, 2018 at 3:43:46 PM UTC-6, kutz...@gmail.com wrote: > > I'd appreciate you sharing a copy of the script with me after you write > it. > -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[weewx-user] Re: table archive_day_barometer already exists
Dave, That sounds like a great feature addition. I'd appreciate you sharing a copy of the script with me after you write it. In my implementation, the rsync "skin" gives me an offline copy of the database each night (or more or less often) and I can set a scheduled job to run your script against the offline copy automatically. If if finds that the database is corrupted, I can go back to an earlier database snapshot created by the Synology software. I like it! phil On Wednesday, December 12, 2018 at 4:16:43 PM UTC-5, Dave Harper wrote: > > Phil, > > One of the things I will be implementing in my new backup strategy is a > check to make sure the database isn't corrupted. This can be done using > command line parameters to sqlite3. When run manually, it looks something > like: > pi@Weather:~ $ sqlite3 weewx.sdb 'pragma integrity_check' > ok > pi@Weather:~ $ > I will be including this in a new Python script which will send me an > email if it detects corruption. Otherwise, it will archive that database > as "known good". This should give me an early warning if something goes > wrong. > > Dave > > > On Wednesday, December 12, 2018 at 10:36:09 AM UTC-6, kutz...@gmail.com > wrote: >> >> In the meantime, I can increase the time between rsync copies, as my >> Davis console should buffer about a week's worth of archive data. That >> would give me more time to react to any database corruption. >> >> phil >> >> -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[weewx-user] Re: table archive_day_barometer already exists
Phil, One of the things I will be implementing in my new backup strategy is a check to make sure the database isn't corrupted. This can be done using command line parameters to sqlite3. When run manually, it looks something like: pi@Weather:~ $ sqlite3 weewx.sdb 'pragma integrity_check' ok pi@Weather:~ $ I will be including this in a new Python script which will send me an email if it detects corruption. Otherwise, it will archive that database as "known good". This should give me an early warning if something goes wrong. Dave On Wednesday, December 12, 2018 at 10:36:09 AM UTC-6, kutz...@gmail.com wrote: > > In the meantime, I can increase the time between rsync copies, as my Davis > console should buffer about a week's worth of archive data. That would > give me more time to react to any database corruption. > > phil > > -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[weewx-user] Re: table archive_day_barometer already exists
What I do is outlined in the weewx wiki here: https://github.com/weewx/weewx/wiki/Using-the-RSYNC-skin-as-a-backup-solution I added the following to weewx.conf: *[[RSYNCSQL]]# This backs up the sqlite databases to Diskstation skin = RsyncHTML_ROOT = /home/weewx/archive server = 10.0.10.225user = weewxpath = /volume1/homes/weewx/archive_backup# every night at midnight, run this reportreport_timing = @daily delete = 0* The nice thing is that the backup is run synchronously within weewx ensuring the database is not being written to during the copy. From here - on my Synology NAS - I can set up a cron-driven backup script as you do, or utilize a purpose-written backup program like Duplicity. I haven't done that last part yet, so I need to catch any corruption within 24 hours or my copy is overwritten by the corrupted database file. But, I plan to implement a backup that gives me multiple snapshots to choose from if I need to do a restore. I'm pretty sure Synology has a couple of backup packages that I could use. In the meantime, I can increase the time between rsync copies, as my Davis console should buffer about a week's worth of archive data. That would give me more time to react to any database corruption. phil On Wednesday, December 12, 2018 at 2:34:24 AM UTC-5, Dave Harper wrote: > > Gary, > > The backup strategy I use is a Python script I wrote that is started by > cron every night. I had read that there could be a problem if the database > is copied while it's being updated. The database is updated once a minute > by weeWX so the script monitors the mtime of the weewx.sdb file, waits for > the timestamp to change then waits about 15 seconds more before copying it > to a subdirectory off my home directory. I also copy the /etc/weewx > directory into the same backup subdirectory and then the script calls > Duplicity to do the actual backup. Duplicity works by starting with a full > backup the first time around and then doing incrementals from then on. A > restore starts with the original full backup and then applies all > incremental changes to get to the target restore date. To keep a restore > process within reason, I only do a month of incrementals and on the first > of each month the script creates a new monthly directory on the NAS. This > strategy has seemed to work well over the past year or so and looking into > why it failed this time around will be one of the top priorities for > tomorrow. > > Dave > -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[weewx-user] Re: table archive_day_barometer already exists
Good stuff, seems well thought out. Hope you find the issue. Gary On Wednesday, 12 December 2018 17:34:24 UTC+10, Dave Harper wrote: > > Gary, > > The backup strategy I use is a Python script I wrote that is started by > cron every night. I had read that there could be a problem if the database > is copied while it's being updated. The database is updated once a minute > by weeWX so the script monitors the mtime of the weewx.sdb file, waits for > the timestamp to change then waits about 15 seconds more before copying it > to a subdirectory off my home directory. I also copy the /etc/weewx > directory into the same backup subdirectory and then the script calls > Duplicity to do the actual backup. Duplicity works by starting with a full > backup the first time around and then doing incrementals from then on. A > restore starts with the original full backup and then applies all > incremental changes to get to the target restore date. To keep a restore > process within reason, I only do a month of incrementals and on the first > of each month the script creates a new monthly directory on the NAS. This > strategy has seemed to work well over the past year or so and looking into > why it failed this time around will be one of the top priorities for > tomorrow. > > Dave > -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[weewx-user] Re: table archive_day_barometer already exists
Gary, The backup strategy I use is a Python script I wrote that is started by cron every night. I had read that there could be a problem if the database is copied while it's being updated. The database is updated once a minute by weeWX so the script monitors the mtime of the weewx.sdb file, waits for the timestamp to change then waits about 15 seconds more before copying it to a subdirectory off my home directory. I also copy the /etc/weewx directory into the same backup subdirectory and then the script calls Duplicity to do the actual backup. Duplicity works by starting with a full backup the first time around and then doing incrementals from then on. A restore starts with the original full backup and then applies all incremental changes to get to the target restore date. To keep a restore process within reason, I only do a month of incrementals and on the first of each month the script creates a new monthly directory on the NAS. This strategy has seemed to work well over the past year or so and looking into why it failed this time around will be one of the top priorities for tomorrow. Dave -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[weewx-user] Re: table archive_day_barometer already exists
Dave, Good that it was fixed, given the database was somehow corrupted there was probably something in there to do with the daily summaries was upsetting weeWX. Fortunately --reconfigure did the trick otherwise it was going to get messy. You are also fortunate that you have a station that logs records and allows downloading later, that helps prevent data loss. Of course having an effective database backup strategy in place is important and as you have just found out knowing that you can recover from your backups is equally as important. Not sure what approach you have taken, but some folks in the past have had some issues when they were copying their .sdb file whilst weeWX was still running. One of the strengths of SQLite is having everything in a single file, it makes backups very easy but that is also its Achilles heel; SQLite doesn't like sharing access to the file. You might want to do a bit of googling of the forums regards backups to see some of the approaches used. I used to take a precisely scheduled copy of the .sdb when weeWX was unlikely to be using it and never had a bad backup. I have since moved on to using MySQL/MariaDB and now just dump the database to file. Backups are really horses for courses. Gary On Wednesday, 12 December 2018 12:59:35 UTC+10, Dave Harper wrote: > > Gary - I am in your debt. The --reconfigure option seems to have done the > trick. It took a while to generate the new file but, once it was done, > weeWX was able to accept the database. It then spend another large chunk > of time rebuilding the daily summaries and, once that was done, it started > downloading the updates from my console. It has loaded almost 7 hours > worth and has about 3 more to go but everything seems to be going smoothly > and there is no reason to think it won't finish normally at this point. > I've been running on a dedicated Raspberry Pi and a while back, in > anticipation of this crash, I built another Pi that boots from a 250GB hard > drive and installed everything on that. Now that I have a working database > again, I will be switching over to that one tomorrow and implementing > another level of backups in addition to the NAS I have now. I really > appreciate the time you've spent helping me get up and going again. > > Regards, > Dave > -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[weewx-user] Re: table archive_day_barometer already exists
Gary - I am in your debt. The --reconfigure option seems to have done the trick. It took a while to generate the new file but, once it was done, weeWX was able to accept the database. It then spend another large chunk of time rebuilding the daily summaries and, once that was done, it started downloading the updates from my console. It has loaded almost 7 hours worth and has about 3 more to go but everything seems to be going smoothly and there is no reason to think it won't finish normally at this point. I've been running on a dedicated Raspberry Pi and a while back, in anticipation of this crash, I built another Pi that boots from a 250GB hard drive and installed everything on that. Now that I have a working database again, I will be switching over to that one tomorrow and implementing another level of backups in addition to the NAS I have now. I really appreciate the time you've spent helping me get up and going again. Regards, Dave -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[weewx-user] Re: table archive_day_barometer already exists
OK, a bit more serious than first looks. Make sure you have a backup of your current db then try using wee_database with the --reconfigure option. This will give you a new copy of your database with _new appended to the file name. Move your original db aside, rename the _new version and see how that goes. If that still fails we may have to get our hands dirty. Gary Sent from my Samsung mobile -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[weewx-user] Re: table archive_day_barometer already exists
Thanks to you both for your suggestions. Gary, I followed your instructions with the following results: 1) Stopped weeWX and verified in /var/log/syslog that it was stopped 2) Used the wee_database utility to drop the daily summaries. The system responded with: Dropping daily summary tables from 'weewx.sdb' ... No daily summaries found in database 'weewx.sdb'. Nothing done. 3) Tried the --rebuild-daily. That produced the following list of errors: Traceback (most recent call last): File "/usr/bin/wee_database", line 793, in main() File "/usr/bin/wee_database", line 153, in main rebuildDaily(config_dict, db_binding, options) File "/usr/bin/wee_database", line 251, in rebuildDaily with weewx.manager.open_manager_with_config(config_dict, db_binding, initialize=True) as dbmanager: File "/usr/share/weewx/weewx/manager.py", line 1017, in open_manager_with_config return open_manager(manager_dict, initialize) File "/usr/share/weewx/weewx/manager.py", line 1006, in open_manager manager_dict['schema']) File "/usr/share/weewx/weewx/manager.py", line 140, in open_with_create dbmanager = cls(connection, table_name=table_name, schema=schema) File "/usr/share/weewx/weewx/manager.py", line 1152, in __init__ self._initialize_day_tables(schema, _cursor) File "/usr/share/weewx/weewx/wxmanager.py", line 35, in _initialize_day_tables weewx.manager.DaySummaryManager._initialize_day_tables(self, archiveSchema, cursor) File "/usr/share/weewx/weewx/manager.py", line 1179, in _initialize_day_tables cursor.execute(DaySummaryManager.sql_create_str % (self.table_name, _obs_type)) File "/usr/share/weewx/weedb/sqlite.py", line 39, in guarded_fn raise weedb.TableExistsError(e) weedb.TableExistsError: table archive_day_barometer already exists I also played with some of the other options to see what they would do (using the --dry-run option just in case) but they all ended up printing a list of errors that terminated in: weedb.OperationalError: No day summary schema for table 'archive' in database 'weewx.sdb' Still digging... -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [weewx-user] Re: table archive_day_barometer already exists
It's always good when 2 people come back with the same answer :) On Wed, 12 Dec 2018, 14:06 Whoops. Someone who really knows crossposted at the same time I did. Happy > to see my advice was good! > phil > > On Tuesday, December 11, 2018 at 8:04:57 PM UTC-5, kutz...@gmail.com > wrote: >> >> I'm not a database nor a weewx expert. But I'd try using the wee_database >> utility trying first the --rebuild-daily option first (you could limit >> it to only today). If that fails, I'd then do a wee_database --drop-daily >> followed by a wee_database --rebuild-daily. See the Utlilities Guide on >> this: http://weewx.com/docs/utilities.htm#wee_database_utility. Make >> sure to stop weewx before using these commands and keep a backup of the >> database if my advice hoses it :-). >> phil >> >> On Tuesday, December 11, 2018 at 7:03:48 PM UTC-5, Dave Harper wrote: >>> >>> Over the past year I've had numerous cases of a corrupted database. I >>> learned early on to have a good backup scheme in place and it has served me >>> well - up until today. After I noticed that the web page was no longer >>> updating I checked the /var/sys/logfile and found the message >>> "DatabaseError: database disk image is malformed". Shortly after that I >>> discovered my backups for the past month were not working, so I've been >>> faced with trying to repair the weewx.sdb file. I've found several posts >>> describing how to recover from problems like this and ended up using the >>> repairsdb.sh script that I found in one of them. That fixed the >>> "malformed" error and I am now able to load the database but when weewx >>> starts it now aborts with the error that "table archive_day_barometer >>> already exists". I had never used sqlite3 prior to weewx and am not really >>> familiar with what to try now. Anybody have any ideas I can try? >>> >>> Thanks >>> Dave >>> >> -- > 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+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[weewx-user] Re: table archive_day_barometer already exists
Whoops. Someone who really knows crossposted at the same time I did. Happy to see my advice was good! phil On Tuesday, December 11, 2018 at 8:04:57 PM UTC-5, kutz...@gmail.com wrote: > > I'm not a database nor a weewx expert. But I'd try using the wee_database > utility trying first the --rebuild-daily option first (you could limit it > to only today). If that fails, I'd then do a wee_database --drop-daily > followed by a wee_database --rebuild-daily. See the Utlilities Guide on > this: http://weewx.com/docs/utilities.htm#wee_database_utility. Make sure > to stop weewx before using these commands and keep a backup of the database > if my advice hoses it :-). > phil > > On Tuesday, December 11, 2018 at 7:03:48 PM UTC-5, Dave Harper wrote: >> >> Over the past year I've had numerous cases of a corrupted database. I >> learned early on to have a good backup scheme in place and it has served me >> well - up until today. After I noticed that the web page was no longer >> updating I checked the /var/sys/logfile and found the message >> "DatabaseError: database disk image is malformed". Shortly after that I >> discovered my backups for the past month were not working, so I've been >> faced with trying to repair the weewx.sdb file. I've found several posts >> describing how to recover from problems like this and ended up using the >> repairsdb.sh script that I found in one of them. That fixed the >> "malformed" error and I am now able to load the database but when weewx >> starts it now aborts with the error that "table archive_day_barometer >> already exists". I had never used sqlite3 prior to weewx and am not really >> familiar with what to try now. Anybody have any ideas I can try? >> >> Thanks >> Dave >> > -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[weewx-user] Re: table archive_day_barometer already exists
I'm not a database nor a weewx expert. But I'd try using the wee_database utility trying first the --rebuild-daily option first (you could limit it to only today). If that fails, I'd then do a wee_database --drop-daily followed by a wee_database --rebuild-daily. See the Utlilities Guide on this: http://weewx.com/docs/utilities.htm#wee_database_utility. Make sure to stop weewx before using these commands and keep a backup of the database if my advice hoses it :-). phil On Tuesday, December 11, 2018 at 7:03:48 PM UTC-5, Dave Harper wrote: > > Over the past year I've had numerous cases of a corrupted database. I > learned early on to have a good backup scheme in place and it has served me > well - up until today. After I noticed that the web page was no longer > updating I checked the /var/sys/logfile and found the message > "DatabaseError: database disk image is malformed". Shortly after that I > discovered my backups for the past month were not working, so I've been > faced with trying to repair the weewx.sdb file. I've found several posts > describing how to recover from problems like this and ended up using the > repairsdb.sh script that I found in one of them. That fixed the > "malformed" error and I am now able to load the database but when weewx > starts it now aborts with the error that "table archive_day_barometer > already exists". I had never used sqlite3 prior to weewx and am not really > familiar with what to try now. Anybody have any ideas I can try? > > Thanks > Dave > -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
