Switch from SQLite DB to MariaDB

[Stephen Rogers]

Hi there, my first post as groups member.  I am a software developer working primarily in Javascript/Vue/Express/MariaDB stack.

I have been running WeeWx for almost 2 years, on Raspberry Pi 3/Raspbian Linux, using SQLite for data storage.

I now want to be able to routinely pull specific dimensions of data (starting with temperature) from the WeeWx database and integrate that data into my own analytics program.  SQLite, as far as I know, cannot be remotely accessed so I will need a different database that can.

I'd like any advice you can provide on the most expedient way to accomplish my goal.   Example idea:

1.  install MariaDB on the WeeWx Pi
2.  reconfigure WeeWx to use MariaDB using essentially the same steps as a fresh install (should I do a fresh WeeWxinstall?)
3. Pull data from SQLite and migrate it to MariaDB (best utility for doing this?) so I have all the almost 2 years of collected data available.

I could also dump the SQLite data and reconfigure a different approach for making the new data available to my application, e.g. MQTT.

Appreciate any advice you WeeWx veterans may have for me.

[Greg Troxel]

Of course, make 6 backups before you do anything.

I could also dump the SQLite data and reconfigure a different approach for
making the new data available to my application, e.g. MQTT.

If sometime wants to get current data as it happens, MQTT is great. I
use that to send temp/etc. to Home Assistant. HA then puts this is a
database, but HA has no way to query weewx's database. And, if the net
is down, there is no backfill. It sounds like you really want to do
database queries over time, and if so that's not really a good match for
MQTT.

1. install MariaDB on the WeeWx Pi
2. reconfigure WeeWx to use MariaDB using essentially the same steps as

a fresh install (should I do a fresh WeeWxinstall?)

3. Pull data from SQLite and migrate it to MariaDB (best utility for

doing this?) so I have all the almost 2 years of collected data available.

You are not he first to want to do this, but it is, as I read the docs,
much easier: See

http://www.weewx.com/docs/utilities.htm#wee_database_utility

and specifically --transfer.option.

[gjr80]

Hi,

To move from using SQLite to MariaDB you might want to have a read of the Transfer from sqlite to MySQL wiki entry. As far as I know the approaches covered there are still valid.

Gary

[Stephen Rogers]

Thanks guys.  Seems straightforward, and I will post results of my experience doing this!

Stephen

[tim lambert]

Hi Stephen,

I started out with weeWX in late December using SQLite, then transitioned to MySQL on a remotely hosted server -- simply backed-up the weewx.conf and made the necessary changes to point to MySQL.  Data migration from SQLite to MySQL was seamless.

However, I began to notice significant delays with archiving data and reporting data -- thanks to the feedback from this forum, I concluded the problem was the remote MySQL server.   I validated the conclusion using a clone of my Production install and establishing a MySQL DB on an SSD connected to the Dev Pi 4.   Now that I was confident I had a resolution to the performance issue, I installed a 128GB thumb-drive on the Production Pi and setup MySQL to use the thumb-drive, then modified the WeeWX to point to the local MySQL DB.  Once I confirmed weeWX was functioning without issue, and inserting data into the local MySQL DB, I simply did an export from the remote MySQL DB and imported the data into the local MySQL DB.

I'm actively working an approach to get the local MySQL data to the remote (public accessible) server to allow for some other analytics/data aggregation -- simply trying to avoid routing traffic through the router to hit the local DB directly.

Good Luck!

-- Tim

[Stephen Rogers]

Tim, thanks so much for sharing your "arrows in the back" experience.  

As I am still in development of my analytics product, just having ability to easily grab data across my LAN as I am testing will suffice, so having the local MariaDB as the new WeeWx store is the first step.

After that we are heading the same direction, i.e., towards using the local generated data in a web based analytics product.  I am intrigued to consider MQTT for the ongoing push of data to the cloud, after doing bulk copy of local SQL data to the cloud / analytics database.  WeeWx has a MQTT extension so trying it should be pretty easy.

I will chime back in as I find my way on this.

Stephen