I was ready to give up on Home Assistant... until I moved the database!

[Dixey]

I’m running on a Pi3b+ for a year now with no issues other than self inflicted gun shots to the foot. I keep a VERY small log used only when debugging 'cause I really don’t care when the guest bedroom light was last turned on. I am, however, about ready to grab an additional spare Pi I’ve got laying around to turn into a database dedicated to my Pi/HA… I’ll try hardwiring the network ports and maybe using some ductape to make me a little silver brick.

[RobDYI]

This might be worth a try to reduce your database, it worked for me.

Allow services to be excluded from Recorder: and the database

I tried to find out how to add this to the official documentation but I am unsure how. Please add to the official documentation as I think its a really important feature that will make most peoples HA run better. I would even suggest state_change only events be recorded as default.

[apop]

Good to know - I’ve wondered the same thing myself as I’ve thought about migrating my DB to another computer at home that’s on most of the time. If HA fails gracefully in the event that the machine is off for a bit, that might be a good way to go…

[dmatik]

Same here. I moved my DB to the one running on my NAS and since then everything running smoothly.

[123 (Taras)]

Then it might be worthwhile to create an automation that detects this situation (recorder is unable to log data to inaccessible database) and notify someone of it.

Otherwise, it’s conceivable that Home Assistant will merrily continue to function without a database thereby creating gaps in every sensors’ history.

• Offloading the database from the SD card to another medium, a more robust medium, is a worthwhile exercise.
• Offloading it to the cloud seems like an exercise in finding new ways to create failure scenarios (re: SmartThings).

[Michael_Davydov (Nix)]

@krash

So,
The first steps as i imagine they should be:

1. Format a usb drive as NTFS (im on a windows machine, hope that’s no issue)
2. Remove the aws address and reboot hassio to make it create a new DB file.
3. Move the db file to a flash drive (and delete the original)
4. Go to Hassio-> System → Hardware and see where my Flash drive is:
   
5. Guessing that in order to access the fstab simple ssh access would not suffice so ill need to use this guide right?

Well, I’m not sure what you mean by ‘simple ssh access’. I’m not sure how exactly ssh is setup on hassos, but you need to connect to your host (rpi) over ssh, and then switch to root user if it’s not already. From that guide it looks like they did not set up a password and only allow access with authorized keys… well, it’s good for security but I don’t think it’s worth it unless you expose ssh port to wan.

You don’t need to connect to container’s ssh, just the host, and edit fstab on the host. Whatever you do, you don’t need to do anything inside any of hassio containers, unless its something very temporary. Changes made inside containers are not persistent.

1. If you’re gonna just use it on linux only then it’s better to format it with ext4, ntfs has some quirks and permission issues on linux, will just save you from some pain down the road. Or at least fat32, but not sure about it
   2-3-4 yes
   You can use blkid command to display your disks and their UUIDs and /dev/sdX paths, and it’s probably better to use UUID to mount it instead of /dev/sdX path.

Now that I see how hard they made it all on HassOS I think that they should really just put in a config value to change log path, lol. I’m so glad I run it on generic Debian VM.

Hell I think you’d be better off just installing Debian on your RPI and running hass.io install script I linked earlier. Then you’ll have easy ssh access, you’ll be able to install Webmin to have a GUI for server administartion, and you’d be able to format and mount flash driver through Webmin. Even better you can install ubuntu or debian with Desktop and do it all from there.

[krash (Harry)]

And that’s where i always loose it
Hassio “simple” ssh only has several commands you can use, and i doubt it will let you edit any files.
As for going the debian-> hass-io it seems like too much of a pain to do it compared to straightforward hassio installation.
I’ll need someone already on hassio to walk me through it step by step, until then, ill keep depending on amazon.

[tom_l]

@123

In the event AWS is inaccessible, as in the case if Internet access is interrupted, how well does Home Assistant handle the error? Without the ability to log anything, does it stoically continue working or does it simply stop?

Going by what happens when HA is unable to write to the dB due to a corruption, (have seen many instances of this) it continues operating, logging an error in the HA log for each failed write.

[Michael_Davydov (Nix)]

@krash

And that’s where i always loose it
Hassio “simple” ssh only has several commands you can use, and i doubt it will let you edit any files.
As for going the debian-> hass-io it seems like too much of a pain to do it compared to straightforward hassio installation.
I’ll need someone already on hassio to walk me through it step by step, until then, ill keep depending on amazon.

Well, not to panic Just approach it step by step and it’s much easier than you’d think. It’s always scary to see when you see a long list of instructions, but if you just give it some more time it’s much easier I’ll try to make it simple here.

Part 1 - Going Ubuntu

So first of all I’d like to address the “it’s less straightforward” thing. It’s not. It’s almost the same. What you need to do is:

Step 1 - install Ubuntu

Full guide - Raspberry Pi | Ubuntu MATE I’m just making it all shorter here

0. Get monitor and keyboard ready, you’ll need them for the first run
1. Go to official raspberrypi website and see what OSes are readily available for flashing. https://www.raspberrypi.org/downloads/ Go for Ubuntu MATE if you’d like to connect Monitor and Keyboard and Mouse to RPI and set it all up this way. Go for Ubuntu Server if you’d like to administer it remotely.
2. Flash it on an SD Card using the same approach you did with HassOS - Win32DiskImager or balenaEtcher or whatever works for it. They have instructions here: Raspberry Pi | Ubuntu MATE
3. Connect monitor and keyboard to your Pi. Insert SD card. Power on.
4. Go over the install wizard where you create user and password

First step done.

Step 1.1. Install and setup SSH

SSH is not enabled by default, you need to install it using sudo apt install openssh-server. It will run on port 22 by default, and you will be able to login with your user and password. Do not port forward this outside of your lan for security purposes.

Step 2. Install Hass.IO

Log into your Ubuntu terminal over SSH or just open “Terminal” app if you’re using desktop.
To run commands with admin privilegies on ubuntu you use sudo prefix or you can log into root account using sudo su command.

Go over here and run commands that are listed there, line by line: Installation - Home Assistant

Done. You have installed Hass.Io on Ubuntu

Step 3. Webmin, optional

If you’d like to have a web UI to administer your RPI host you can use Webmin. It’s web panel that has the most features among anything I could find, alternatives being Ajenti or Cockpit, they all lack something. You can use webmin to browse filesystem, edit text files, format and partition disks, mount disks, control services and much much more.

To install Webmin you can follow Debian instructions (Ubuntu is derivative of Debian): Downloading and Installing | Webmin just make sure to run the dependency installation command first. They messed the order up in instructions On purpose I suppose, but anyway.

And there you go. You now have full control over your host and you’re not limited by anything hassOS does. I’ve been running it like this on a Debian VM for about a year now, no issues with updates or any important addons. Some addons dont work, sure, but those are like SSH addon… Why do you need an SSH addon if you can connect to your host without it? Other addons like MQTT, nginx, etc should all work fine with this method of installation.

PART 2 - Sticking with HASSOS

I started to write it up but then double checked the guide you linked, it should actually keep you covered on how to connect to HassOS host over SSH.

First step and task here is to get SSH access and be able to edit fstab file. For that you’ll need to run command nano /etc/fstab or sudo nano /etc/fstab and see if it works.

I’m starting to get a feeling that HassOS may be stripped down so much that it all might just be impossible to do after all. If they don’t include text editor like nano or vim or something AND dont include any package manager (like apt) then it’s just not worth it at that point and… Well, HassOS should then just die I suppose. Maybe when Hass is at 1.0 and all can be controlled through UI it might work, but not now.

Conclusion

I guess that what I can summarize after looking into this a bit more is that HassOS should only be used for very light installations and maybe initial look into Hass. It MUST be then replaced with some other installation method like the one described above. It may require additional steps and some learning curve to it, but it’s well worth it in the long run.

I hope they will resolve all these issues, like making adjustments to logging and db so it won’t bog SD card down, maybe modifying OS to log to RAM instead and dump it to file system once in a while, but not constantly? Maybe doing something with database so it only logs certain things by default, like sensors, and you need to enable everything else. Or just advising users to use some other Pi alternative, and not RPI. As much as I love RPI and use it for different projects - it’s not a good home for hass.

Disclaimer

This is all just my personal opinion, I’m not associated with Home assistant devs in any way, I’m just a long time user of hass with a modest hass system, linux amateur and tinkerer, who happens to run a Proxmox server with like two dozen services on it. I only entered linux like 3-4 years ago myself. But just can’t stop falling down this rabbit hole.

[clyra]

Hey,

This whole thing get me wondering if we could use a in-memory database so there’s no need to touch the SD card. And surprise, surprise! It seems that sqlite can use/create a database in memory only. See this. If this works as expected we should see a improvement in SD life and speed at the cost of losing the history every time you restart HA (sounds good to me ).

[clyra]

It seems that the our nice dev folks already thought about that! try this:

recorder:
  db_url: 'sqlite:///:memory:'

[Dixey]

Haven’t tried to make the mods yet but; I’m running HassOS 2.11 on a Pi3b+ and when I ssh as root into it I can edit via nano my /etc/fstab file.

[krash (Harry)]

@Michael_Davydov

I tend to get carried away when explaining something

Why would you even say that??

Thanks for taking the time to write it up! and at least clearing up that it cant be done on hassio.
I guess ill give the Ubuntu solution a try one morning next week with clear head.

Trhee quick questions that come in mind:

1. Would the hassio over ubuntu solution be a lot heavier on the RPi than just hassio?
2. Couldnt i just run the ubuntu from a usb hdd/ssd hooked on the rpi in the first place to avoid the sd issues?
3. Since i would have a full operating system, couldnt i also host an sql (LAMP or something) on it and use that alongside with hassio?

[firstof9]

@krash

• Would the hassio over ubuntu solution be a lot heavier on the RPi than just hassio?
• Couldnt i just run the ubuntu from a usb hdd/ssd hooked on the rpi in the first place to avoid the sd issues?
• Since i would have a full operating system, couldnt i also host an sql (LAMP or something) on it and use that alongside with hassio?

1. The server version is stripped down to just the bare minimum, so it’s very light.

2. Yes you could connect a HDD/SSD via USB to mitigate the SDCard issue

3. Yes you could install your own DB along side HassIO, you could also run them all from docker this way as well.

[flamingm0e]

@Michael_Davydov

I’m starting to get a feeling that HassOS may be stripped down so much that it all might just be impossible to do after all.

Yes. It is.

@Michael_Davydov

dont include any package manager (like apt)

It doesn’t

@Michael_Davydov

Well, HassOS should then just die I suppose.

HassOS is an appliance. It is not intended as a general purpose distro.

[Michael_Davydov (Nix)]

@clyra

It seems that the our nice dev folks already thought about that! try this:

I actually did not even look into whether there’s an option to change the db path, assuming that multiple people claiming there isn’t looked for it already. Well, seems it was a mistake on my end Great thing, so there IS db path after all
But then there’s another issue - how to mount a USB drive in HassOS?

@krash

Thanks for taking the time to write it up! and at least clearing up that it cant be done on hassio.

I need to correct myself on that, it seems you can change db_path in it, so there’s just one thing to find out - how to mount a usb drive

@krash

Since i would have a full operating system, couldnt i also host an sql (LAMP or something) on it and use that alongside with hassio?

@firstof9

Yes you could install your own DB along side HassIO, you could also run them all from docker this way as well.

That said - you may not see much of a difference between sqlite and mysql\mariadb in terms of performance in this usecase. sqlite is very light and fast, and it’s only issue is that it does not support multiple clients writing to db very well. So if it’s only used by hass then it should be fine. As long as it’s not on sd card So i’d say until it gets to 500MB+ you should not worry about it. In the end even mysql database is also just a single file, there’s not magic there.

@flamingm0e

HassOS is an appliance. It is not intended as a general purpose distro.

Yeah I know. I’m sorry for sounding so harsh, I was feeling sarcastic at that point. It’s just that it lacks some very useful features, and I feel like it’s better (at this point) to run hass on a more general purpose\server distro. That said even some router firmwares have text editors and sometimes even package manager. So I see why it may not need package manager, but IMO it should have at least a text editor.

[clyra]

@Michael_Davydov

That said - you may not see much of a difference between sqlite and mysql\mariadb in terms of performance in this usecase. sqlite is very light and fast, and it’s only issue is that it does not support multiple clients writing to db very well. So if it’s only used by hass then it should be fine.

Not so sure about that. There’s evidence that mysql\mariadb has better performance. As my setup grows with more sensors/stuff I saw a increase in response time of the history graphs. Mysql solved that for the same hardware (not pi and not SD). Later I moved to postresql as mysql has it’s own quirks.

For small setups sqlite is ok, but you know how addictive HA can be :-).

[flamingm0e]

@Michael_Davydov

I feel like it’s better (at this point) to run hass on a more general purpose\server distro

Completely agree. I don’t use hassio at all. Home assistant in docker for me.

@Michael_Davydov

That said even some router firmwares have text editors and sometimes even package manager. So I see why it may not need package manager, but IMO it should have at least a text editor.

Pretty sure it comes with vi and nano, but it doesn’t matter if it doesn’t have support for ntfs-3g or exfat.

[Michael_Davydov (Nix)]

@clyra

Not so sure about that. There’s evidence that mysql\mariadb has better performance. As my setup grows with more sensors/stuff I saw a increase in response time of the history graphs. Mysql solved that for the same hardware (not pi and not SD). Later I moved to postresql as mysql has it’s own quirks.

For small setups sqlite is ok, but you know how addictive HA can be :-).

Hence my remark about roughly 500MB size Yes, for a bigger db it’s better to use something else.

@flamingm0e

Pretty sure it comes with vi and nano, but it doesn’t matter if it doesn’t have support for ntfs-3g or exfat.

Yeah

EDIT
I mean no. Why do you need ntfs or exfat? You can use ext4. It may be tricky formatting it from windows, but I think there are tools that can do that.

[Skinah (Matt)]

Cheaper than a NUC and only uses <5 watts at idle and has multiple
Sata and m2 slot

https://www.hardkernel.com/shop/odroid-h2/