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

[tom_l]

@firstof9

if you were able to get to the shell

There are ways of doing this. Simplest is to use the Community SSH Addon in unprotected mode. Not for the inexperienced. You can easily mess your installation up this way.

[Michael_Davydov (Nix)]

I was under the impression that ots possible to change sqlite db location, no?

But yes, while Pi is plenty powerful there are a lot of issues related to databases and sd cards. There are even whole systems for pi (DietPi) that are designed to overcome this by keeping all logs in ram only to lessen sd card usage to practically zero.

I think best option would be to look for Pi alternatives and mention some on the intro page, something that has good onboard memory instead of an sd card. There are some options. I also believe that you can buy some x86 mini pc from aliexpress for about the same as Pi+accessories would cost

[firstof9]

If it drops you to root of the hypervisor OS that’s what you’d need so you could rsync the data.

Alternatively one could use the loopback filesystem and just mount the image as well then share that via NFS, this might be the easier solution for a network booting HassOS.

I might give this a try myself…

[Michael_Davydov (Nix)]

Actually if database is the only issue that bogs down the sd card and there’s no built-in option to change it’s location one could then use a symlink. It’s like a shortcut in windows, but much more powerful in a way that it’s treated not just like a shortcut but an actual reference to the file that.

1. Shutdown hass
2. Move sqlite.db file to a USB stick or drive
3. ln -s /path/to/sqlite.db.file /path/to/original/folder

[tmjpugh]

Large qty sensors,switches, etc …move DB

A few sensors,switches, etc…no need
Been running on RasPi/SD 1.5yr. installed outside in horrible environment. Only controls 1 switch and sensor.

previously burned out 2 SD card on main install with lots of switch/sensors before moving DB the later off RasPi

It’s use based

[krash (Harry)]

@Michael_Davydov

• Shutdown hass
• Move sqlite.db file to a USB stick or drive
• ln -s /path/to/sqlite.db.file /path/to/original/folder

Is this doable in hass.io on an RPi?

---

Anyway,
It’s been extensively discussed around the community that it’s a essensial to implement an easy and straightforward method to move the DB and log files out of the sd card.
In that case one could just swap a thumb drive (in case of fail) and be up and running in seconds.

On a side note, i proposed this, which is a useful feature, and a temporary solution maybe.
(go give it a vote):

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…

[capstan1 (Capstan)]

Thanks for your answer!
I was referring to the symlink to usb, though. Don’t have a NAS and want to stay Pi only for now, as I don’t need anything more then HA at the moment.

[Alexander_Stepanov]

So I was right to go for Intel NUK from the beginning

[firstof9]

You’d use something like ln -s <database filename> /mnt/usb_drive/<database filename> in the config directory.

[alex2]

@Alexander_Stepanov

So I was right to go for Intel NUK from the beginning

Definetely! Congrats on a smart and sustainable choice.

@Michael_Davydov

I think best option would be to look for Pi alternatives and mention some on the intro page, something that has good onboard memory instead of an sd card. There are some options. I also believe that you can buy some x86 mini pc from aliexpress for about the same as Pi+accessories would cost

Yes, I totally agree. Now here are some lower budget Pi alternatives that I think could be worthwhile:

Atomic Pi - a much hyped (for it’s cost effectiveness) development board with a Z8350 CPU, 2GB RAM, 16GB eMMC, Wifi+Bluetooth (35 USD + shipping + power supply) These boards are on limited supply though. Coincidentally available right now at ameridroid.

Z8350 based Mini PCs - different Producers from China (e.g. Beelink) sell this system. Pretty much the same as an Atomic Pi, but smaller, more readily accesible interfaces, and available with 4GB RAM/32GB SSD, has a “neat” case and includes the power supply (~100 USD shipped from china)

Pre-owned Intel NUC - here in Germany you can find worthwhile configured ones on ebay starting at 150 USD

It is worth noting though, that setting up any of these options is far more involved than setting up Hass.io on a RaPi. The atomic Pi comes with Linux preinstalled, whereas the Mini PCs come with Windows - Linux can be installed, but it might be a little fiddly.

[tom_l]

XCY mini pcs are competitively priced compared to NUCs too.

[Michael_Davydov (Nix)]

@krash

Is this doable in hass.io on an RPi?

Yes, it is. Hass.io uses docker and maps it’s internal container folders to folders on the host system, this is how you keep all your data between hass updates. So your database is also on the filesystem, everything is located, by default, here: /usr/share/hassio

@krash

it’s a essensial to implement an easy and straightforward method to move the DB and log files out of the sd card.

Method I described to you is actually very straightforward and well-known for anyone familiar with Linux. Symlinks are used all over the place with Linux. And as you can see it’s quite easy, just copy database and run 1 command. But I agree that most likely majority of users come with only Windows experience - it should be documented for them and ideally moved into config options. But I can see why developers may not bother with it, because it’s so easy to achieve with native OS tools, so they probably just don’t consider it a priority.

@capstan1

was referring to the symlink to usb, though.

There’s some confusion there, I did not say anything about NAS. This command:

ln -s /path/to/sqlite.db.file /path/to/original/folder

Creates a symlink on local filesystem, anything that’s mounted. So you can do this on hassio host system:

ln -s /mnt/usbdrive/hassio/homeassistant/home-assistant_v2.db /usr/share/hassio/homeassistant/home-assistant_v2.db

or maybe

ln -s /mnt/usbdrive/hassio/homeassistant/home-assistant_v2.db /usr/share/hassio/homeassistant/

Not sure if filename is required in the destination path. (It’s source - destination order). So this way you’re creating a symlink from your existing file on the usb drive to the ‘virtual file’ placed in the config folder.

Just dont forget that you also need to mount a usb drive with /etc/fstab file.

@Alexander_Stepanov

So I was right to go for Intel NUK from the beginning

Damn right you were Though frankly if I only needed to run hass it would be a huge cringe for me to use a NUC because of all the power wasted. I hope you dont just run hass on it, there’s lots more you can use it for at the same time Good option would be to install Proxmox on it, run hass.io inside a VM, and then use other VMs and LXC containers for other services.

@firstof9

You’d use something like ln -s <database filename> /mnt/usb_drive/<database filename> in the config directory.

That’s what I told him Twice now. You messed the order though, we need to create a link from the file on the drive to the virtual file\symlink in config directory, not the other way around

@alex2

It is worth noting though, that setting up any of these options is far more involved than setting up Hass.io on a RaPi. The atomic Pi comes with Linux preinstalled, whereas the Mini PCs come with Windows - Linux can be installed, but it might be a little fiddly.

It is, but not THAT much more involved. Hass.io is installed on any host by running a single script. So just… install linux (Ubuntu), then follow these instructions: Installation - Home Assistant (just run those commands and that’s all).

Done.

Script can be used on debian as well, I do think there’s just a but more to installing docker on debian, I don’t quite remember. Not sure if it will work on other distros.

@tom_l

XCY mini pcs are competitively priced compared to NUCs too.

I’m actually surprised how a lot of people just jump onto NUCs genuinely believing they are the only option. Just google for a “net-top” or “mini PC” that’s what NUC is, they’ve existed long before NUCs appeared. There are lots of cheaper options, from Gigabyte, Zotac and other brands. And NUC is huge overkill for hass, it’ll run like on 2% of it’s performance capacity. Even Pi in truth only uses like 10% of it’s CPU for most hass installations, it’s just the SD card that’s the problem.

[krash (Harry)]

So much information

@Michael_Davydov

with only Windows experience

That’s me, unfortunately, but im trying t change it.
Ill look into the symlink solution.

[Michael_Davydov (Nix)]

@krash

So much information

I tend to get carried away when explaining something
But in short, all you need to do is mount usb drive in fstab file, move (not copy) database file to new\any location on usb drive, and create a symlink with 1 command.

@krash

but im trying t change it.

Thats good to hear A lot of people just prefer to not bother with anything that’s not familiar or not a one-click solution. Can’t blame all of them, though, not everyone has time for it.

[RazvanG (Razvan Gheorghiu)]

Having the same issues like alex, until i bought a Dell Wise pc for aprox 40$ from an outlet store ( https://www.discountelectronics.com/dell-wyse-dx0d-909634-01l-2gb-ram-thin-client/ ) with regular 4gb of ddr3 mem , dual core AMD, an adapter cable for an SSDor HDD, moded a smal fan inside. Made myself a great HA Linux Box, never had an issue with him for months, with dozens of sensors, mariadb, influx, portainer etc!

[firstof9]

@Michael_Davydov

That’s what I told him Twice now. You messed the order though, we need to create a link from the file on the drive to the virtual file\symlink in config directory, not the other way around

Whoops, sorry it was early

[bakerbug (Bill Baker)]

Can you link to the steps you used to move your database to AWS?

[123 (Taras)]

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?

[krash (Harry)]

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?

[krash (Harry)]

The recorder component gives errors but it does not affect any other operation of HA, iirc