I have Home Assistant on a Raspberry Pi 3 in my house to manage all my stuff and everything works great, and it uses a more powerful Mariadb server located in my garage.
Everything is protected with a UPS except the network link between the house and garage which uses a couple of Netgear powerline adapters. So in case of power outage (which happens a lot lately), everything continues to work except the DB link. Yeah I could move the Mariadb server back to the Raspberry Pi, but it’s too slow. Or move thinks to the same place but that’s not the point.
I was wondering if I can use a failover solution to a local Mariadb server on the Raspberry Pi (master to master replication), until the power/main DB link comes back.
I know it’s a bit overkill but what the heck it’s an opportunity to learn something.
I saw the beginning of a solution with Mariadb Galera combined with HAProxy, but if some of you guys experimented other solutions (or same solution) your feedback would be very helpful.
Just crashed my home assistant innodb database which I couldn’t recover, luckily I had a nightly backup.
Thought it was the best opportunity to get this galera cluster working once and for all, and I finally did.
Have you manage to find a solution to your challenge? I have a similar set up, just one difference is that during power cut my NAS with MariaDB shuts down much earlier than the HA system (due to much higher power consumption of the NAS). My HA is also running on a low power PC therefore I keep the DB on more powerful NAS but in case of power outage, after roughly 45-60 minutes my NAS will shut itself down.
I managed to get the mariadb galera cluster work ok for a few months and survived a few power outages… until it was completely out of sync as it couldn’t resume and sync the replication at some point. HA was stuck with the local mariadb instance.
But I found another solution as the reason of keeping the HA on the raspberry pi inside the house was mainly to keep the ZigBee dongle that was attached to it inside the house near the plethora of ZigBee devices I have.
The solution was to move HA into the NAS in the garage, and move the ZigBee stuff to a Sonoff ZigBee Bridge running Tasmota installed in the house.
I didn’t dig further with the galera cluster as it was too unstable for my use case, especially with the network going down during the power outage.
Many thanks for quick response!
I’ve also came across the mariadb galera cluster - this seems to be the right solution to the problem, although quite complex to set up and maintain. I’m inclining towards just accepting the situation, my NAS can run for 1 hour on UPS, alternatively I’ll invest in more powerful hardware for HA and move the DB there.