weewx copy data from one weewx.sdb to another

[Dirk Uhlich]

Hi,

i've searched some time, but no luck or wrong search method.

At my weather station Vantage Pro2 with Raspberry the USB-Stick died or is dying. It stoped working but I was able to save some data.

I managed to install everything on a new USB but now data of one day is missing.

Running second pi with weatherstick has this data but here's my problem:

How do I manage to copy one day of Data (Timestamp 1728000600 to something like 1728069000) from one weex.sdb to my new one with missing data.

I know stopping weewx is mandatory, but I've no experience with sqlite.

Important to know:actual weewx.sdb contains data sind 2021 until yesterday ...

Maybe anybody could help.

Thanks a lot

Dirk

[Tom Keffer]

You did not say how you did the install, but I'll assume you used the package installer with apt. This means your database is located at /var/lib/weewx/weewx.sdb. Adjust as necessary.

On the machine that has the one day of data, dump the database into a text file:

sqlite3 /var/lib/weewx/weewx.sdb

sqlite> .output /var/tmp/weewx.sql

sqlite> .dump

sqlite> .quit

Copy the file /var/tmp/weewx.sql to the new machine somehow, perhaps on a USB stick. I'll assume you put it in /var/tmp/weewx.sql on the new machine.

On the new machine:

# Make a backup:

sudo cp /var/lib/weewx/weewx.sdb /var/lib/weewx/weewx.sdb.backup

# Merge:

sudo sqlite3 /var/lib/weewx/weewx.sdb < /var/tmp/weewx.sql

You will get many errors, including that the "table archive already exists", and "UNIQUE constraint failed". You can ignore these.

-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/307fed91-f842-4a79-99d2-7de6f34f5336n%40googlegroups.com.

[Dirk Uhlich]

Hi Tom,

thanks. You are right, I intalled using apt, so my databases are located in /var/lib/weewx

I manaeged to save data since beginning of my runnging system since the 03.10.2024   ==> lets name it weewx.sdb-2024

On my second pi with weatherstick I was doing some tests and tried to make a display with LCD ==> data starting some day in past but pi is still running ==> lets name weewx.weather (here I'll find my missing day)

Now my goal was to use all data out of weewx.sdb-2024 adding only one day of weewx.weather (ignoring all data before 03.10.2024 (and now also all data starting 05.10.2024))

Maybe also merging two complete databases could work, but im sure, there will be more than one year of duplicates - if this will work, I'll give it a try.

Is it possible to cut away all data before or starting a given timestamp? TThis could reduce errors?

Just to ask, will database after merging be repaired in backgroud or why it's possible to ignore all errors?

Thanks a lot and have a nice weekend.

Dirk

[wfs...@gmail.com]

I didn't test this but using sqlite3 you should be able to do something like:

attach "/var/lib/weewx/weewx.sdb" as current;
attach "/var/lib/weewx/old.sdb" as old;
insert into current.archive select * from old.archive where date(datetime, 'unixepoch', 'localtime') == '2024-03-10';

I would copy both databases and use those copies in the statements above so you don't risk messing up the

originals.  

Don't supply a file name on the command line, just start sqlite3, it will say "connected to a transient in-memory database".

Then execute the sql statements above, supplying the correct filenames in the ATTACH statements.

You will have to rebuild or update the dailies afterwards.

Walt

[Dirk Uhlich]

Thanks a lot, everything worked

I did something between, what means i shrinked database with has data including missing day to only this day. While this I learned that filesize remains until I use "VACUUM" to deflate file.

  File after this operation is only 2.3MB instead of somethinge like 400MB ==> name here weews_1day-of-data.sdb

Than I followed Tom's advice.

  sqlite3 /var/lib/weewx/weewx_1day-of-data.sdb

  sqlite> .output /var/tmp/weewx_1day-of-data.sql

  sqlite> .dump

  sqlite> .quit

  Copied to my new RPI and did

  sudo cp /var/lib/weewx/weewx.sdb /var/lib/weewx/weewx.sdb.backup

  # Merge:

  sudo sqlite3 /var/lib/weewx/weewx.sdb < /var/tmp/weewx_1dy-of-data.sql

And it worked at first try.

So again, thanks a lot.

Dirk