Save DB on USB stick/drive (Hassio)

krash · March 12, 2019, 3:09pm

I ended up moving the DB to the amazon free RDS service, didnt look into local db any more

elRadix · March 12, 2019, 3:17pm

can you provide a little how to ?

krash · March 12, 2019, 3:18pm

Sure, just give me some time to write it up.

elRadix · March 12, 2019, 3:28pm

great, how is the performance overall ?

krash · March 12, 2019, 3:30pm

to be honest, I dont use extensive history data, for graphs and stuff, just some motion detectors i want to see if they were triggered and some things i need their state saved during restarts. It’s working like the local db in my case, havent seen any problem.

Coedy · March 12, 2019, 3:33pm

Looking forward to the How-to.

krasatos · March 12, 2019, 3:56pm

I won’t have the time to do it anytime soon I’m afraid, I’m at work.
Maybe the reddit thread that helped me can help you out meanwhile https://www.reddit.com/r/homeassistant/comments/a1xf3q/-/eatkplm

iLLiac4 · March 12, 2019, 4:03pm

Hi. Has somebody been able to use the nuw feature that moves the database to ssd/usb drive?
https://github.com/home-assistant/hassos/issues/164

labelin the partition on ssd with hassos-data and renamig the one from sd card does not seem to work.
I renamed it in gparted.

Do someone knows how this feature actually works, if at all in hassio?

elRadix · March 12, 2019, 4:05pm

if you ust can share your recorder config, I have already created db in Amazon rds which was not so difficult

krash · March 13, 2019, 8:12am

Sorry, was on mobile and it seems like cloud9 is officially non-mobile friendly, here’s my recorder config:

recorder:

  db_url: mysql://user:pw@url:port/dbname?charset=utf8

You can get the URL and port from the endpoint section in your rds console, RDS> Databases> dbname:

Also, since i dont have a static ISP ip, i had to leave the inbound/outbound security groups open. I know it’s not safe but i have not figured out how to fix this. Let me know if you know a solution to that

elRadix · March 13, 2019, 8:39am

Thank you, I’ll check later this week.

Looking on the web I found the below url which you could use to protect your connection. I have not tried it.

krash · March 13, 2019, 8:42am

Looks like exactly what i need, will check it out too. Thanks man!

capstan1 · May 16, 2019, 8:26am

So does anyone have any news on this? The second SD card in 5 months just died on me and I need to change smth. because this just isn’t acceptable.

krash · May 16, 2019, 8:30am

Have not found something yet.
And i think that this option is very important.
If we could move DB and Logs to a usb stick, we would have minimal usage of the sd card.
And if the stick went bad you could just switch it out for a new one and keep running in seconds.
I’m guessing it’s some foundamental hassos issue that wont allow it? idk

oncleben31 · May 16, 2019, 9:24am

Didn’t find any solution yet. This is the last reason who keep me on using previous hass.io version. I have a mini SSD plugged on my Raspberry Pi.

krash · May 16, 2019, 9:26am

I moved my DB to amazon RDS, and the card never failed since.
But i would still like to have something local, both for security and speed.

krash · May 16, 2019, 9:38am

Meanwhile, i proposed a feature that would make recorder/history setting up a little easier and hopefully, indirectly remove some strain on the SD card.
Go upvote it if you like it

jatgm1 · August 10, 2021, 10:57am

So how do we move the db to a flash drive?

jatgm1 · October 18, 2021, 6:35pm

Is this possible yet? Because I would really love to make sure my storage devices last as long as possible.

benroose · January 31, 2022, 10:38pm

I believe I’ve cracked this issue and now have Hassio running on a MicroSD with the sqlite database stored separately on a local USB drive!

Issue Background
The underlying problem here is one of persisence. USB drives plugged into a Raspberry Pi are accessible from the underlying hassio operating system, but the /etc/fstab file for defining persisent mountpoints is read only in the hassio OS as part of the mounted root overlay (as seen when the mount command is run in the hassio CLI via SSH). A new mountpoint entry in /etc/fstab for a USB drive or partition will not survive a reboot. This is why Tinkerer’s blog post “Home Assistant - moving logs and database off the SD card” from October 2017 does not work in hassio [1]. However, there is a way to create automatic mountpoints for USB drives using a new udev rule as developed by eklex with its deployment into hassio as discussed by Paolo Antinori [2] and [3]. Below I’ve defined the steps I used.

Test Environment Used
Home Assistant (hassio):
Raspberry Pi 3B running hassio on a MicroSD card (HA version 2021.12.2)
[I suspect this will work with Raspberry Pi 4 with hassio on a MicroSD too, but I haven’t tested it.]

Desktop system:
Fedora 32 - this is a generic Linux system I’m using for USB drive preparation and SSH remote access into the home assistant system.
NOTE: you may need to adjust the below steps if your desktop system is Windows or Mac - if in doubt and you’re unfamiliar with different filesystems, then I’d suggest sticking with a single FAT32 filesystem on the USB drive since hassio likely does not have the necessary third-party drivers to handle NTFS (Windows) or HFS (Mac) formatted drives.

Steps to replicate this solution

Get a spare USB flash drive and plug into your desktop system
This could be the USB drive you plan to use for storing the database or a different USB drive. Warning: you will wipe the USB drive(s) if you follow these steps.
First preparation of USB flash drive on desktop system (my example Linux commands assume the user is already root, use sudo if required on your system.) I’m referencing Paolo Antinori’s post here [3]:
1. As per the documented hassio way to pre-load config, find your USB drive as a filesystem device (be careful since it may not be /dev/sdb1 on your system), format the USB drive (I formatted it as ext4) and use CONFIG as the filesystem label (using the correct label is very important). Example:
```
# lsblk
...
sdb           8:0    1  29.9G  0 disk  
├─sdb1        8:1    1  29.9G  0 part  
...
# mkfs -t ext4 -L CONFIG /dev/sdb1
Creating filesystem with 7831552 4k blocks and 1961712 inodes
...
Writing superblocks and filesystem accounting information: done
```
1. Mount the USB drive, create a directory on that partition called udev, and in that new directory create a file called 80-mount-usb-to-media-by-label.rules containing the entire content of the GitHub gist “80-mount-usb-to-media-by-label.rules” as created by eklex [2]. Example:
```
# mkdir /media/usb
# mount /dev/sdb1 /media/usb
# cd /media/usb
# mkdir udev
# nano udev/80-mount-usb-to-media-by-label.rules
[paste the raw content of elex's GitHub Gist code into your text editor and save the new file]
# chown 1000:1000 -R udev
# sync
```
1. Now remove the USB drive from your desktop system.
NOTE 1:
You will need to copy/paste the raw content from the GitHub Gist code. Use the “raw” button first and copy that. Don’t just copy the displayed content in the Gist link since you’ll likely also copy GitHub’s HTML formatting tags. Alternatively, you can download the file as a zip compressed file, unzip it, and move the downloaded .rules file into the udev directory on the USB flash drive.

NOTE 2:
I used nano as a command line text editor since it’s simple to use, but use whatever editor you are comfortable with. I’d recommend doing all this in a Linux desktop environment, but for Windows or Mac users, make sure to use a text editor which will not add extra invisible characters and with which you can customize the filename extension to .rules, i.e. use something like NotePad++.

NOTE 3:
The change ownership (chown) command may not be strictly necessary. When Home Assistant imports files from a CONFIG drive, I’m unsure if it is doing the import as hassio user or as root user. However, looking at the /etc/passwd file on hassio system, it seems the hassio user has UID/GID of 1000. So I wanted to ensure correct file permissions on the udev directory and 80-mount-usb-to-media-by-label.rules file, so the hassio user could access and copy the file.
Import the new udev rules file into hassio system.
1. Plug the USB drive into the Raspberry Pi running Hassio.
2. In the Home Assistant web UI (or mobile app) as an administrator user navigate to:
  Configuration -> Add-ons, Backups & Supervisor -> System
3. In the box Host, click on the three vertical dots (more action items) and click on Import from USB.
4. The new file should be imported into hassio as a persistent new udev rule for handling USB drive mounting.
5. Remove the USB drive from the Raspberry Pi.
NOTE:
I was not able to easily confirm the 80-mount-usb-to-media-by-label.rules file was imported correctly. However, I did notice in the host logs a new entry stating:
EXT4-fs (sda1): mounted filesystem with ordered data mode. Opts: (null)
Second preparation of USB flash drive on desktop system (my Linux example commands assume the user is already root, use sudo if required on your system.)
1. Plug the USB drive back into your desktop system (or use a different USB flash drive if preferred for storing the home assistant database files).
2. Partition and format the USB flash drive to your own specifications. Below I’ve left it as a single partition for simplicity, but you could use the parted command for splitting the USB drive into multiple partitions.
  DO NOT LABEL WITH “CONFIG” ANY PARTITION YOU WISH TO AUTOMOUNT IN HOME ASSISTANT (eklex’s udev rule will not mount a partition with the label CONFIG). I’m using the label HA-USB in this example but you can choose your own label. Example:
```
# lsblk
...
sdb           8:0    1  29.9G  0 disk  
├─sdb1        8:1    1  29.9G  0 part  
...
# mkfs -t ext4 -L HA-USB /dev/sdb1
Creating filesystem with 7831552 4k blocks and 1961712 inodes
...
Writing superblocks and filesystem accounting information: done
# sync
```
1. Now remove the USB drive from your desktop system.
Automatically mount the USB drive in Home Assistant.
1. Plug the USB drive into the Raspberry Pi running hassio.
2. From the Home Assistant web UI or in the CLI via SSH, reboot the Raspberry Pi host system (yes, reboot the entire system, do not just restart the home assistant supervisor).
3. Wait for the Respberry Pi to reboot and Home Assistant to fully startup.
4. If all went well, then the USB drive will be automatically mounted at the location /media/[filesystem label], i.e. /media/HA-USB. If you created more than one partition on the USB Drive, then each partition will be mounted as a separate directory in /media. You can double-check your USB drive was mounted from the Home Assistant web UI in the Media Browser tab and look for your filesystem label.
Configure Home Assistant to store the sqlite database files on the USB drive.
1. Open the web terminal or SSH remotely into the Home Assistant system. I used the SSH add-on.
2. Check the USB drive is mounted in /media. For example, here I’m grepping the df command for the filesystem label I chose, HA-USB:
```
~ df | grep HA-USB
/dev/sda1             30702576     81420  29038464   0% /media/HA-USB
```
1. Shut down the home assistant core either using the web UI or the CLI command:
```
~ ha core stop
```
1. Create a new directory in /media/[filesystem-label] for storing the sqlite database files and ensure the directory is owned by the hassio user. Example:
```
~ mkdir /media/HA-USB/database
~ chown hassio:hassio /media/HA-USB/database
~ ls -l /media/HA-USB
total 20
drwxr-xr-x    2 hassio   hassio        4096 Jan 31 12:29 database
drwx------    2 root     root         16384 Jan 30 19:40 lost+found
```
1. OPTIONAL: if you want to keep your prior home assistant history, then use the following command (inserting your own filesystem label for HA-USB):
```
~ mv /config/home-assistant_v2.db /media/HA-USB/database/
```
NOTE:
If you do not use the above command, then home assistant will automatically create a new sqlite database on the USB drive upon start up.
1. Edit your home assistant configuration file and add (or edit) the following lines with you new location (I’m using the HA-USB label as a filepath, but your filepath may differ for your configuration):
```
recorder:
  db_url: sqlite:////media/HA-USB/database/home-assistant_v2.db
```
NOTE:
Make sure to include all four slashes after sqlite: since you are defining an absolute filepath as a location for the database files.
1. Start Home Assistant or reboot entire Raspberry Pi system. Example on the CLI:
```
~ ha core start
```
1. After home assistant has fully restarted, you can check the modification time of the database files to make sure home assistant is indeed using it (or if you’re creating a new database, then just make sure the files now exist), i.e.
```
~ ls -l /media/HA-USB/database
```
1. For double-confirmation, check the home assistant logs in the web UI and make sure you do not see any recorder error messages stating that home assistant cannot access the sqlite database.

If all worked correctly, you should now have Home Assistant (hassio) running from the MicroSD card with the sqlite database file offloaded to a separate USB flash drive! As a bonus, you now have an easy way to add USB storage devices for HA local access to media files, etc.

I hope this information helps others!
Ben

References:
[1] Tinkering with Home Automation: Home Assistant - moving logs and database off the SD card
[2] udev rule for Home Assistant OS (hassio) to mount USB drives into the Supervisor Media directory · GitHub
[3] Paolo Antinori’s comment #5 on blog thread “[SOLVED] Mount USB drive in Hassio to be used on the Media Folder with udev customization” was very helpful as a starting point!
[SOLVED] Mount USB drive in Hassio to be used on the Media Folder with udev customization - #5 by paoloantinori