Save DB on USB stick/drive (Hassio)

[krash (Harry)]

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 (Harry)]

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

Add GUI for recorder and history inclusions, exclusions and settings

Hey guys, I believe that the recorder and history components although very helpful have not gotten any love on the recent GUI changes. This makes (primarily new) users (including me at some points) ignore or avoid setting them up correctly as it works out of the box and they would rather not fiddle with something they don’t understand. People just ignore their DBs and they are getting huge (I recently saw one over 50gb full of nothing interesting). Also the posts of people asking help to und…

[jatgm1]

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

[jatgm1]

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

[benroose]

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

1. 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.

2. 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
   

   2. 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
   

   3. 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.

3. 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)

4. 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
   

   3. Now remove the USB drive from your desktop system.

5. 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.

6. 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
   

   3. Shut down the home assistant core either using the web UI or the CLI command:

   ~ ha core stop
   

   4. 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
   

   5. 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.

   6. 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.

   7. Start Home Assistant or reboot entire Raspberry Pi system. Example on the CLI:

   ~ ha core start
   

   8. 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
   

   9. 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