Where to start for Database Logging

Configuration
1

Hi All,

Not really sure where to start with this. I got broken into one the weekend and had a bit of trouble getting the details for times of entry etc for the police report… i ended up having to right it all down on the stairs.

BAscially, i want to make sure that all my history is logged correctly, plus i want to take some of the load off my SD Card, and store it on my DS918+ NAS.

I’ve read about InfluxDB, and i havent had huge amount of experience with SQL, but i figure i can learn.

Any ideas on:

  1. Resources for setting up and running a database on a NAS
  2. How to integrate into HASS so i can store, export, analyse my data.

My HASS instance is running on a Pi4 4gb.

Cheers.

2

Have you upgraded the memory on your DS918+ from the default 4Gb to 8Gb? (or more)

If so you could run your HA fully on the NAS under Synology’s Virtual Machine Manager (VMM). The VM for HA should have a minimum of 2Gb allocated to it.

InfluxDB / Grafana would then have access to the NAS mass storage!

Hope this helps?

3

First things first. InfluxDB is not and cannot be your recorder DB. HA does not have the ability to use InfluxDB as its recorder. You can use the InfluxDB with Grafana to store long term data for trends and home data, but it will not be readable by HA.

Now that said, there is a way to push data from HA into an InfluxDB. Others have done it. It will not necessarily decrease the reads/writes to your SD card though because you still have to initially store the data in a DB for HA.

So let’s break things down. To reduce wear on your SD card, you want to move your recorder DB off your HA instance. This means installing MariaDB or MySQL on a separate machine (your NAS) and link HA to it. This is different than the MariaDB add-on in HA supervised.

Once your recorder DB is off your RPi, you then want to configure it properly. Things like your recorder configuration should record everything, but for a short amount of time (3 days, 7 days, etc). But history and logbook should be configure to show just the things you care about (motion, door, lights, switches, etc). These are the logbook and history built into HA (side menu) and when you click on an entity to bring up its card.

Now for configuring InfluxDB, I have not done it yet, but others have. You configure InfluxDB to grab everything out of your recorder DB (which is why you want to record everything in the first place). I believe then you can filter what you want to actually store long term. Combine this with Grafana and you have a fairly powerful home history trend and insight.

Now since you have a DS918+, this supports docker. You could theoretically run everything you do now on your RPi, but on your NAS (make sure you have enough memory). HA in a docker, MariaDB in a docker, InfluxDB and Grafana in a docker. and anything else you run (assuming you have HA Supervised on your RPi) as an add-on can be run stand alone as a docker on your NAS and linked to HA through an integration. This will absolutely save your RPi SD card because at this point, you won’t be using it anymore. just take a look at my setup (signature), I run everything in docker on a 6 year old NAS with 8GB memory. Plus then backups and troubleshooting might be easier. I’m not saying you have to change your setup. If you’re comfortable with how you have it installed now and just want to change a few things, that’s fine. I’m just showing options given the hardware you have, direction you say you want to go, and my experience.

1 Like
4

Thank,

I had thought about moving the whole instance into a Docker or Virtual MAchine, but I’m concerned about setup and compatablity. Not to mention support of updates etc…

I also have a ConbeeII for my zigbee gear that would need to be plugged into via a USB. Although, i had also though of trying to put the Zigbee on a remote device that could be placed in a better location the the back of the Pi near the Wifi.

I’ll do a bit more digging.

5

Sorry to sidetrack the original question, but docker HA is basically supervised HA, just you are the supervisor. So updates are done when you want them to be done (or you can setup a watchtower docker container to do it automatically). Yes there may be a little more configuration in your part to get things up and running such as mapping the USB device to the container with proper permissions, mapping volumes for configuration files, port/networking configuration, etc. But if you can find the docker compose file for an add-on, then it’s just a matter of massaging it or adapting it to work with your docker install and NAS.

I know ZWave has a few ways in software to “decouple” the hardware from your HA install. Unfortunately, I don’t use zigbee so I don’t know if the same is true for it but I want to say it exists. Worst case scenario, You could still run a HA install on your RPi and use MQTT statestream or another method of sending data between two HA instances.

Getting back to your original question on databases, I would at bare minimum setup MariaDB on your NAS by either installing the MariaDB 10 package from the Synology Package Center Community repo, or spin up a docker container for MariaDB. Then follow the recorder documentation for HA to switch over to using it instead of the default DB on your SD card. Yes you will lose current history when you switch over (not sure if there is a way to migrate data from SQLite to MySQL/MariaDB). Then for starters just tell the recorder to record everything for however long you want. For example, I keep 28 days of data and I record hundreds, if not close to a thousand different entities and my DB hovers around 7.5GB - 8GB.