move existing database to VM meteobridge. **solved**

[Wheetie]

Hi All,

I've installed the new Meteobridge VM in my Proxmox server and it seems to be working well. Thank you! However, I can't figure out how to transfer my existing database from my RPi. Does anyone know how to move an existing database and restore it to a new VM installation so the older data is still present?

I've tried passing through my backup USB to the Proxmox VM, but it doesn't automount or show up with df -h. I've tried to connect via SFTP, but I'm not able to connect to either the VM, or my RPi. I think this issue is probably related to a mismatch of the RSA key exchange handling, but I'm not able to log into Luci to troubleshoot. It seems this option has been disabled in the x86 VM version?

I've also tried to upload files via the new upload feature in Maintenance -> Media Access -> 'Upload Files', but the upload crashes. I'm not sure the backup folder is the correct place to upload this anyways. When I try to export the new database, it saves someplace locally in the VM because I can see it in the restore option, but it doesn't show up when I click the 'Download or Delete Files' field.

And I've always struggled getting smb to work, and maybe that's because I have 3 Meteobridge instances on my network, so maybe the hostname is not able to resolve properly because they are the same for all 3?

Any ideas? Am I missing something?

Thanks for the help,

Wheetie

[Mattk]

Maintenance>Media Access>Upload Files is a database function.

Have you tried Database>Import/Export?

[Wheetie]

HI Mattk,

I was looking at that, but I want to export the entire database. And this tab seems to either do increments or time windows.

I think maybe I have figured it out. The backed up database of the RPi is not compressed, whereas the backup on the VM output (which it stores locally, not to a USB) is a .gz compressed file. So I have compressed the RPi database and am now uploading it via the Admin -> Maintenance -> Media Access -> 'Upload Files' -> backup.

It's going really slow because it's a pretty large file, but appears to be working - hasn't crashed yet! I'll know in a little bit if this is the solution. It does show up in the VM Database -> Admin -> 'Restore Database From'. But it's a little strange because there doesn't seem to be a way to delete these backup database files?

Maybe there is a better way?

Thanks,
Wheetie

Screenshot 2024-07-17 at 10.35.15 PM.png (80.8 KiB) Viewed 2394 times

[Mattk]

I believe it does take some time depending on the file size hence the option to export the database in parts.

And I see I left a word out, that should have been Maintenance>Media Access>Upload Files is NOT a database function.

[Wheetie]

This does work to transfer the database, but there appears to be inconsistent export / import behavior across my devices (RPi3, RPi4, and VM) so it's a bit confusing. For example, only the RPi4 displays the Backup directory with the exported sql-files in the 'Files to download or Delete'.

Screenshot 2024-07-18 at 1.15.01 AM.png (144.49 KiB) Viewed 2383 times

Also, do you know if it's problematic to transfer a database from an RPi to a VM? I notice that the RPi databases includes more sensors (for hardware monitoring, etc) and these are not included in the VM database. Will this matter?

[admin]

I would recommend to use the new but rather slow export / import SQL feature. Backup/Restore does a binary copy of the database file and that might not work between all CPU architectures.

[Wheetie]

I would recommend to use the new but rather slow export / import SQL feature. Backup/Restore does a binary copy of the database file and that might not work between all CPU architectures.

I tried to do the SQL import / export feature, but I get a bunch of errors:

Screenshot 2024-07-18 at 6.11.52 AM.png (59.92 KiB) Viewed 2371 times

Also, any idea why the "Backup' header and corresponding files for download or delete don't show up in my RPi3 or new VM Meteobridge, but they do on my RPi4?

[admin]

Logger errors can be ignored, they just tell that data is dropped during database import.
As the error messages are not shown in full extent I cannot comment on that.

[Wheetie]

I tried again, and it failed twice. The error messages go off the screen, so I'm not able to capture them with the screenshot as the logger window is a fixed size.

Here I tried to import the database, which failed. So I deleted the database, and re-imported it from the sql-dump:


Error: near line 53145185: no such table: wind1lowbat_hour
Error: near line 53145186: no such table: wind1lowbat_hour
Error: near line 53145187: no such table: wind1lowbat_hour
Error: near line 53145191: table thb0press_hour already exists
Error: near line 54528004: table th0dew_hour already exists
Error: near line 54568582: table wind1maxdir_hour already exists
Error: near line 57282502: table thb0seapress_hour already exists
Error: near line 58665294: table th0heatindex_hour already exists
Error: near line 60121281: table wind1wind_hour already exists
Error: near line 60159167: table th0hum_hour already exists
Error: near line 62875779: table thb0temp_hour already exists
dbase (18.07.2024 15:43:06): process terminated normally.
dbase (18.07.2024 15:43:07): process started.
dbase (18.07.2024 15:43:07): database erased on user command.
dbase (18.07.2024 15:43:07): database sync done (0 tables, 0/0 writes).
system (18.07.2024 15:43:26): SQL database import started: sql-202407180748.gz
system (18.07.2024 15:49:00): SQL database import failed at line : Error: near line 1929690: database is locked
Error: near line 2476638: database is locked
Error: near line 2476639: database is locked
Error: near line 2476640: database is locked

Screenshot 2024-07-18 at 4.00.03 PM.png (106.45 KiB) Viewed 2360 times

[admin]

Looks like some database job kicks-in in parallel and locks the database. I will inspect this on my system.

Could you please provide me the gzipped sql you tried to import? I tested with smaller ones and that worked fine.

[Wheetie]

Looks like some database job kicks-in in parallel and locks the database. I will inspect this on my system.

Could you please provide me the gzipped sql you tried to import? I tested with smaller ones and that worked fine.

Thanks Boris - I think I have it working now. Based on what you said, I removed all my stations (Meteostick Vantage with 3 transmitters, and 2 Purple Air sensors), and then deleted the new database. I also turned off all the Events just to be sure. Then I imported the SQL database, which worked without any errors in the Logger Tab. Next, I reconstructed the database, made sure the data imported properly, and then added back my transmitters. So far, it seems to be working.

Maybe this was how we were supposed to transfer the original sql database? I'm sorry if I missed it, and skipped ahead to just uploading the .conf file and the sql database. I happy to provide my gzipped 560MB SQL file if you still would like to troubleshoot further.

Thanks,
Wheetie

[admin]

Maybe this was how we were supposed to transfer the original sql database? I'm sorry if I missed the it, and skipped ahead to just uploading the .conf file and the sql database. I happy to provide my gzipped 560MB SQL file if you still would like to troubleshoot further.

Yes, it should be that easy. So I am still interested in your SQL file to give it some import tests here. You reach me at "info(at)meteobridge.com".

[Wheetie]

OK, Sent you an email. Let me know how or where to upload my sql.gz file.

[admin]

Just released update should have the problem solved. Please give it a try.

[Wheetie]

Yes, this new update works for me. I was able to import the sql-file without any errors on a fresh VM with weather stations defined. So this is resolved.

However, I still do not see any of the files under the "Backup" header using the 'Download or Delete Files' selection. It works fine on the RPi3, but both the RPi4 and the VM do not list the files. So you can't download or delete any of the backup files on the RiP4 or VM, making it really difficult to transfer the databases or sql.gz to another instance or save for backup on another computer or server.