Very cool. Thanks for the find. I can see so many possibilities with this. rich On Monday, 23 February 2026 at 16:56:28 UTC-5 Vince Skahan wrote:
> Just one more update for Tomasz here. I did some more research and found > that sqlite3 has a 'sqlite3_rsync' utility that might be worth a look. It > is very similar in setup to rsync over ssh but handles sqlite3 db > specifically. See https://sqlite.org/rsync.html for details > > Unfortunately it does not come with the debian sqlite3 packages I can > find, so you'd need to compile from sources and install into someplace in > $PATH on client and server computers (I copied mine to /usr/local/bin), but > compiling it is fast and simple to do. > > > git clone https://github.com/sqlite/sqlite.git sqlite-sources > cd sqlite-sources > make sqlite3_rsync > sudo cp sqlite3_rsync /usr/local/bin # get it into local $PATH > > > # for the remote server, scp the binary over there into the target account > $PATH if it's the same arch > > # or compile+install there too if the server arch differs (ie, raspi > client => AWS server) > > > # also set up ssh for passwordless ssh > # (run ssh-keygen locally, add .pub to authorized_keys far side or use > ssh-copy-id) > > Here are some examples of how to use it: > > > # example initial run for a small source db > sqlite3_rsync -v \ > /home/vagrant/weewx-data/archive/weewx.sdb \ > vagrant@localhost:/home/vagrant/weewx-data/archive/replicated.sdb > *sent 1,008,859 bytes*, received 22 bytes, 3,577,592.20 bytes/sec > total size 1,007,616 > > # example running it again a few minutes later > sqlite3_rsync -v \ > /home/vagrant/weewx-data/archive/weewx.sdb \ > vagrant@localhost:/home/vagrant/weewx-data/archive/replicated.sdb > *sent 168,227 bytes*, received 4,763 bytes, 609,119.72 bytes/sec > total size 1,007,616 speedup is 5.82 > > > For my test I'm sqlite3_rsync(ing) from a source db to a target db on > localhost within one vm since I can't remember how to set up vagrant to let > me ssh between client vms. > > If it's me, I'd just set up a cron job on each source client system to run > this command occasionally to catch up the remote server copy. I do not > know how well it would work for 'many' systems all trying to replicate onto > the central server if all the clients trigger at once. Having the cron > job on each client trigger perhaps a few minutes differently might be wise, > but maybe all of them starting up in parallel would work. I didn't test > that. > > For reporting, likely lots of ways to do that on the server. I suppose you > could just install but not start up weewx there and instead run 'weectl > report run' occasionally from cron to generate the output HTML. You'd > likely need to add additional database bindings on the server weewx.conf so > the report generator would know which db to read when running the reports. > Something like the following perhaps. > > [DataBindings] > > #--- the usual binding --- > [[wx_binding]] > database = archive_sqlite > > table_name = archive > manager = weewx.manager.DaySummaryManager > schema = weewx.schemas.wview_extended.schema > > #--- client1 --- > [[client1_wx_binding]] > database = archive_sqlite_client1 > > table_name = archive > manager = weewx.manager.DaySummaryManager > schema = weewx.schemas.wview_extended.schema > > #--- client2 --- > [[client2_wx_binding]] > database = archive_sqlite_client2 > > table_name = archive > manager = weewx.manager.DaySummaryManager > schema = weewx.schemas.wview_extended.schema > > [Databases] > > #--- the usual db definitions --- > > [[archive_sqlite]] > database_name = weewx.sdb > database_type = SQLite > > [[archive_mysql]] > database_name = weewx > database_type = MySQL > > #----- remote client systems ---- > > [[archive_sqlite_client1]] > database_name = client1.sdb > database_type = SQLite > > [[archive_sqlite_client2]] > database_name = client2.sdb > database_type = SQLite > > > Might be worth a look when you start experimenting on real systems.... > > > On Monday, February 23, 2026 at 12:23:38 AM UTC-8 Tomasz Lewicki wrote: > >> Thank you all very much, especially Vince and Rich, for your tips and >> detailed descriptions. I will familiarize myself with the subject, first >> experimenting on my own installation with one or two stations, and when my >> project takes off (first I need to secure external funding for it), I will >> operate on a larger scale. >> >> niedziela, 22 lutego 2026 o 21:25:22 UTC+1 Vince Skahan napisał(a): >> >>> You need to figure out your requirements. >>> >>> - there are over 100 possible elements in a default weewx schema >>> - which measurements do you want to display and compare among your >>> stations ? >>> >>> If you're just looking for the most typically used items (outTemp, wind, >>> windGust, windDir, rain) then MQTT would be very very easy to set up. >>> >>> > "I* haven't dealt with MQTT, but [...] I hope this protocol has the >>> ability to announce/send to a remote server."* >>> >>> That is EXACTLY how it works. The client(s) publish information to the >>> server. The server subscribes to whatever information it is configured to >>> care about. >>> >>> >>> 1. On your weewx client system, use Matthew's weewx-mqtt extension to >>> publish weewx data to the MQTT server (aka 'mosquitto broker'): >>> >>> [[MQTT]] >>> enable = true >>> client_id = mysite1 # optional >>> server_url = mqtt://192.168.1.69:1883/ # or the FQDN of the >>> broker computer >>> append_units_label = false # default=True >>> binding = loop,archive # or archive or loop >>> alone >>> topic = mysite1 # use a different >>> topic per client computer >>> log_success = false >>> log_failure = true >>> >>> (you can use Rich's MQTTpublish extension if you want - syntax is a >>> little different but similar) >>> >>> >>> 2. On the target server use Rich's MQTTsubscribe to subscribe to the >>> items you want, from the clients you want, and save them to the weewx db to >>> some column in your schema. >>> >>> This example runs it as a service to add incoming data from MQTT to the >>> normal data from my VP2 station, but it can run as a MQTTSubscribeDriver if >>> you want. If you run it as a driver on your central weewx server it will >>> just save data coming in from whatever client systems you configure to >>> publish to it. You'll likely need some custom set of columns in your >>> central weewx instance db but that's easy to add. >>> >>> [MQTTSubscribeService] >>> enable = true >>> host = 192.168.1.69 # or the FQDN of the broker computer >>> port = 1883 >>> keepalive = 60 >>> username = None >>> password = None >>> binding = loop >>> >>> [[message_callback]] >>> type = json >>> >>> [[topics]] >>> unit_system = US >>> ignore_start_time = True >>> ignore_end_time = True >>> >>> [[[mysite1/loop]]] # subscribe to data published by >>> the mysite1 weewx instance >>> [[[[outTemp_F]]]] # extract its outTemp item >>> name = extraTemp1 # save to the local db using >>> extraTemp1 >>> units = degree_F >>> >>> [[[mysite2/loop]]] # similarly for mysite2 >>> [[[[outTemp_F]]]] >>> name = extraTemp2 >>> units = degree_F >>> >>> 3. And of course set up whatever custom skin with tabular data and/or >>> graphs that you want for visualizing. >>> >>> >>> ==> Experiment a little with MQTT while you think about your >>> requirements. If you want to learn MQTT this is very well documented >>> online, but if you look at the Belchertown-from-scratch repo I mentioned >>> this week there is a page that should walk you step-by-step. See >>> https://github.com/vinceskahan/belchertown-from-scratch/blob/main/configure-websockets-no-encryption.md >>> >>> and ignore the Belchertown mentions therein. The rest shows how to install >>> and test mosquitto MQTT enough to get a feel for it. >>> >>> On Sunday, February 22, 2026 at 1:24:27 AM UTC-8 Tomasz Lewicki wrote: >>> >>>> First of all, thank you for the wide response and the whole list of >>>> ideas. For now, I am at the stage of developing a concept for how to build >>>> such a network and manage it, at least in terms of configuration files. I >>>> assume that all stations will have access to high-speed internet (this >>>> will >>>> be one of the installation requirements, apart from the terrain >>>> conditions), so the amount of data transferred from the stations to the >>>> central server - if I need one - will not be a bottleneck. >>>> >>>> I haven't dealt with MQTT, but since you say it's not difficult, all >>>> the better. I hope this protocol has the ability to announce/send to a >>>> remote server. >>>> >>>> I have come up with another solution, although I am not sure if it >>>> duplicates any of the proposed ones - as I mentioned, I have not delved >>>> into the subject yet. If I am reinventing the wheel, please let me know. >>>> At >>>> the stage of starting each station, I would add additional columns to the >>>> individual weewx.sdb database, which would be copies of other columns, >>>> e.g., archive_day_outTemp_stationID (where stationID is, of course, a >>>> unique key - the name of the station). Before sending the database to the >>>> server, a script would cut out only the *_stationID columns, save them to >>>> a >>>> new database, and send this truncated database to the central server. >>>> However, I see a problem here in the form of additional CPU overhead and, >>>> perhaps most importantly, an increased number of records on the microSD >>>> card (as I wrote in my first post, I want to have Weewx on Raspberry Pi). >>>> So transferring the entire database, even with additional columns, and >>>> processing it in one place may be a better solution. >>>> >>>> As for summaries, at first I didn't consider data visualization, only >>>> simple tabular summaries, e.g., maximum temperature in the entire area - >>>> station X, minimum temperature - station Y, etc. But I'm sure that over >>>> time, you'll want to have graphs and images :) >>>> >>>> niedziela, 22 lutego 2026 o 01:42:09 UTC+1 [email protected] >>>> napisał(a): >>>> >>>>> Here is my understanding of the problem. >>>>> >>>>> >>>>> There are multiple databases on different physical machines and it is >>>>> desired to create data visualizations that combine them. >>>>> >>>>> >>>>> Cat skin 1. >>>>> >>>>> A central visualization process that can access the remote data >>>>> sources. >>>>> >>>>> >>>>> Cat skin 2. >>>>> >>>>> Replicate the databases “asis” to a central location and run the >>>>> visualization off these replicas. >>>>> >>>>> >>>>> Cat skin 3. >>>>> >>>>> Replicate the data of interest into a central database and run the >>>>> visualization off this central database >>>>> >>>>> >>>>> Cat skin n. >>>>> >>>>> I am sure there are many more options. >>>>> >>>>> >>>>> My gut, the first decision is whether to try to access the remote data >>>>> sources from a central location (cat skin 1) or replicate the required >>>>> data to the central location (cat skin 2 and 3). Then proceed from there. >>>>> >>>>> >>>>> Then layered on top of (or underneath, since we have skinned the >>>>> cat?), the various technology that can be used..... >>>>> >>>>> On Saturday, 21 February 2026 at 19:00:24 UTC-5 Graham Eddy wrote: >>>>> >>>>>> the question, as i understood it, was to present from single copy of >>>>>> data already existing, rather than to store multiple copies of the same >>>>>> data in multiple databases. >>>>>> i use mqtt extensively for data acquisition, creating single truths >>>>>> in databases. then i have a presentation layer over the top of all to >>>>>> create a single (complex) portal (which has more than just weewx data). >>>>>> as vince and others point out, there is more than one way to ’skin a >>>>>> cat’ and it depends on your requirements >>>>>> *⊣GE⊢* >>>>>> >>>>>> On 22 Feb 2026, at 5:30 am, Vince Skahan <[email protected]> wrote: >>>>>> >>>>>> For just a few readings from just a few stations, MQTT is the >>>>>> simplest way: >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> -- 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 [email protected]. To view this discussion visit https://groups.google.com/d/msgid/weewx-user/de75bb6d-08d2-4a83-8559-f256a3b58d00n%40googlegroups.com.
