migrate an sql DB to another machine?

[Eric Gammeter]

Hi-  

I have been running weewx 3.8.2-1 for a year on my main debian PC-  It ran SQL and created a weewx.sdb file (888832kb).  Now I would like to migrate that database file onto a RaspberryPi PC and operate weewx on the Rpi...My question is this:  Is there a way to migrate my weewx.sdb file into the RPi weewx application?  

Many thanks- /E

[Pat]

The sqlite sdb file can be moved just like any normal file. 

Stop weewx first, then copy the file to the new machine, and start weewx on the new machine after its copied. It should work right away. Check the logs to make sure. 

Always smart to keep a backup copy of the database just in case anything goes wrong. 

[Eric Gammeter]

Well-  is the sdb file compatible with the sqlite that is running on the Rpi PC?  SQL vs sqLite.  

On Tuesday, August 28, 2018 at 11:31:27 AM UTC-4, Eric Gammeter wrote:

[Pat]

The sdb file is sqlite. If you want to verify, you could install the sqlite3 tool and run this command 

echo .tables | sqlite3 weewx.sdb

weewx only supports sqlite and mysql. Double check to make sure which database you are using by checking weewx.conf, [DataBindings] [[wx_binding]] database. if it's archive_sqlite then weewx.sdb is the file you want to copy

[Thomas Keffer]

I would add that sqlite files are supposed to be portable across all machines, including big- and little-endian, 32- and 64-bit. So, as Pat says, it's just a simple matter of moving your file.

-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.
For more options, visit https://groups.google.com/d/optout.

[Eric Gammeter]

OK Thanks Pat and Tom!    I got the sdb file migrated over from the old PC onto my RPi PC-  it is working and recording new data just fine:  HOWEVER (you knew that was coming didn't you?)  I am *not* seeing any of the weather data for January 2018 thru July 2018.  Curious.  I have no idea what is up (or down).  When I look at the yearly summary (at bottom of ) index.html file from the 'old' pc all the data for the year is there!  When I look at the same summary index file on the Rpi-  only August appears.  This is certainly no big deal mind you-  I'm just hoping I can access all the data in the sdb file for the year 2018.     

On Tuesday, August 28, 2018 at 11:31:27 AM UTC-4, Eric Gammeter wrote:

[vince]

On Wednesday, August 29, 2018 at 2:18:11 PM UTC-7, Eric Gammeter wrote:

OK Thanks Pat and Tom!    I got the sdb file migrated over from the old PC onto my RPi PC-  it is working and recording new data just fine:  HOWEVER (you knew that was coming didn't you?)  I am *not* seeing any of the weather data for January 2018 thru July 2018.  Curious.  I have no idea what is up (or down).  When I look at the yearly summary (at bottom of ) index.html file from the 'old' pc all the data for the year is there!  When I look at the same summary index file on the Rpi-  only August appears.  This is certainly no big deal mind you-  I'm just hoping I can access all the data in the sdb file for the year 2018.     

You might want to query your database to see if there are archive records for that range.

Jan-03-2018 is 1515000000 seconds since the time epoch

Jul-30-2018 is 1533000000 seconds since the time epoch

To see if there are records in between those ranges to do a quick test....

echo "select datetime(dateTime,'unixepoch','localtime'),dateTime from archive where dateTime > 1535574000 and dateTime < 1535580000 limit 10" ; | sqlite3 weewx.sdb

 

Hopefully it prints out 10 lines that look something like the following (I used different dates in my test below)....

2018-08-29 13:25:00|1535574300

2018-08-29 13:30:00|1535574600

2018-08-29 13:35:00|1535574900

2018-08-29 13:40:00|1535575200

2018-08-29 13:45:00|1535575500

2018-08-29 13:50:00|1535575800

2018-08-29 13:55:00|1535576100

2018-08-29 14:00:00|1535576400

[Eric Gammeter]

Thank you Vince-  I get the following reply:

bash: syntax error near unexpected token `|

On Tuesday, August 28, 2018 at 11:31:27 AM UTC-4, Eric Gammeter wrote:

[vince]

On Thursday, August 30, 2018 at 11:40:18 AM UTC-7, Eric Gammeter wrote:

Thank you Vince-  I get the following reply:

bash: syntax error near unexpected token `|

oops - put the semi-colon inside the quotes before the close quote...

echo "select datetime(dateTime,'unixepoch','localtime'),dateTime from archive where dateTime > 1535574000 and dateTime < 1535580000 limit 10;" | sqlite3 weewx.sdb

 

[Eric Gammeter]

Thanks all-  I think I misread/misunderstood the size of the original sdb file-  I thought it was big enough to contain all the archived data for the year-  I expected it to have all the 2018 data- but it didn't turn out that way.  End of this story.  /Eric    

On Tuesday, August 28, 2018 at 11:31:27 AM UTC-4, Eric Gammeter wrote:

[vince]

On Thursday, August 30, 2018 at 2:15:25 PM UTC-7, Eric Gammeter wrote:

Thanks all-  I think I misread/misunderstood the size of the original sdb file-  I thought it was big enough to contain all the archived data for the year-  I expected it to have all the 2018 data- but it didn't turn out that way.  End of this story.  /Eric    

Unless your disk filled on the old system, it should have just grown as needed.  There's nothing pre-allocated re: size of a sqlite3 db....