I also battled with these issues for a while.
Interestingly, #60874 was merged in December 2021, but as far as I can tell, it only appears in pre-release 2021.12.0b5 and not in any release changelogs since. I probably have missed it somewhere.
Still, I’ve encountered this issue in March and April 2022 so it seems to be still not fixed entirely. Until then, I’ve written a bash script that creates a backup every night via a conjob. So far, it seems to work fine and I wanted to share it with you guys.
The following script
- Stops HA
- Copies the database from the docker environment to the local filesystem
- Performs an integrity check
- Dumps, recreates and copies the database back to HA if the integrity check returns issues
- Starts HA
For now, this seems to to the trick and I’ve not seen any corrupted databases for a few days now.
#!/bin/bash
BASEDIR="/opt/ha-backups/"
SOURCE="homeassistant:/config/home-assistant_v2.db"
TARGET="$BASEDIR$(date +%F\_%H%M%S)_home-assistant_v2.db"
echo "$(date +%F\_%H%M%S) Starting Backup" >> $BASEDIR"log.txt"
echo -n "$(date +%F\_%H%M%S) Shutting down HA: " >> $BASEDIR"log.txt"
ha core stop >> $BASEDIR"log.txt"
echo "$(date +%F\_%H%M%S) Copying database file" >> $BASEDIR"log.txt"
docker cp $SOURCE $TARGET
echo -n "$(date +%F\_%H%M%S) Checking for issues..." >> $BASEDIR"log.txt"
RESULT=$(sqlite3 $TARGET "pragma integrity_check")
if [ "$RESULT" != "ok" ]; then
echo "issues were found. Fixing..." >> $BASEDIR"log.txt"
echo
sqlite3 $TARGET -cmd ".output tmp.sql" ".dump"
rm $TARGET
sqlite3 $TARGET -cmd ".read tmp.sql" ".quit"
rm tmp.sql
echo
echo "$(date +%F\_%H%M%S) The backup was dumped and rebuilt. Now copying fixed backup back to HA." >> $BASEDIR"log.txt"
docker cp $TARGET $SOURCE
else
echo " no issues were found :)" >> $BASEDIR"log.txt"
fi
echo -n "$(date +%F\_%H%M%S) Starting HA: " >> $BASEDIR"log.txt"
ha core start >> $BASEDIR"log.txt"
My crontab for this looks like this:
# HomeAssistant Backup
0 4 * * * /opt/ha-backups/backup-and-fix
I hope this scripts are useful for other people as well until these issues are finally solved.
