IMPORTANT NOTE: the usernames and passwords in this post are wrong!!! Replace with your own username/passwords
Hi all,
I recently went through this exercise so I thought I would share my solution. It may be simple, but I went searching for an answer and had to piece it together so I thought I would save someone else the trouble.
Scenario:
I have backups going back 3 years or so. However, my Home Assistant InfluxDB database was set to 14d retention policy for most of that time. I have backups but was unsure, how do I combine all the backups into one database so it has the history.
Solution:
Essentially, I restored my HA backups (one at a time) to a disconnected HA VM and then I took an InfluxDB backup with the following simple script
#!/bin/bash
influxd backup -database homeassistant -portable /share/influxdb/${1}
I installed the Samba addon, and configured it with a simple username/password.
I placed this in the share folder in the samba mount:
This folder is accessible to the containers running on HassOS.
I uploaded the Home Assistant backups I had and restored only the InfluxDB portion:
After restore was completed, from the Home Assistant VM I connected to the docker container:
docker exec -it addon_a0d7b954_influxdb bash
Once inside the container I ran
/share/influx_backup.sh 30Sept2021
This will take an Influx backup of the current HA backup. In my case, I was taking Influx backups by date so that’s how I named them.
Influx Backups Complete… Time to Restore
Ok so now I had a bunch of files in folders that looked like this (with shards dumped to files)
The next problem was how do I merge them all together. For this I opted to create a new database inside the InfluxDB Admin Panel
In my case I called it merged_ha. After I had my new database the next step was determining how to merge the files. Now your method may vary. Keep in mind that as it stands all of these databases from every single backup was called “homeassistant”.
Option 1:
instead of taking Influx backups of each Home Assistant backup you can simply do this from inside the influx container:
influx -username admin -password admin -database homeassistant -execute 'SELECT * INTO "merged_ha"."autogen".:MEASUREMENT FROM "homeassistant"."autogen"./.*/ GROUP BY *'
This will copy the contents of homeassistant.autogen into merged_ha.autogen. After each successful merge, you would then have to do another Home Assistant Restore of a backup.
Rinse Repeat
Option 2:
I used the Influx tooling to create a new database for each Influx Backup I took earlier.
In order to restore the backups I took into a new database I ran the following:
for db_backup in *; do influxd restore -portable -database homeassistant -newdb ${db_backup} ${db_backup}
At which point, we are ready to actually merge the databases.
Merging The Databases:
I wrote a small for-loop that generated this small script for me, however the syntax should be easily understandable:
influx -username admin -password admin -database 07sept2022 -execute 'SELECT * INTO "merged_ha"."autogen".:MEASUREMENT FROM "07sept2022"."autogen"./.*/ GROUP BY *'
influx -username admin -password admin -database 12Nov2022 -execute 'SELECT * INTO "merged_ha"."autogen".:MEASUREMENT FROM "12Nov2022"."autogen"./.*/ GROUP BY *'
influx -username admin -password admin -database 08Dec2022 -execute 'SELECT * INTO "merged_ha"."autogen".:MEASUREMENT FROM "08Dec2022"."autogen"./.*/ GROUP BY *'
And like that I have a combine database of my homeassistant instance for the last 2 years while not weighing down the active one.
This process definitely takes a large amount of time, if like me you are restoring 2 years worth of history so be patient.
Hopefully this helps someone else out there