Restore from backup - how can energy data be preserved?

I recently had to restore from a back up to fix an undiagnosed issue with my database size increasing
details here :My Database size jumped x10 recently - help diagnosing why - #3 by steve_jo

I use samba backup to backup regularly to my NAS and it full restored a 4 day old back up fine.

But I’m missing my energy dashboard data for the missing days - I guess not that surprising as I’ve rolled back to 4 days ago.

How can I preserve the useful energy dashboard data when I have to restore from a backup? What should I have done? Asking so I will know next time.

Running HA on RPi 4 4GB
Home Assistant 2023.6.3
Supervisor 2023.06.4
Operating System 10.3
Frontend 20230608.0 - latest

Thanks

1 Like

So you want to roll back your database to reduce its size but keep the data :thinking:

1 Like

No, not what I said.
I restored from a backup to try and fix a problem - or at last help me understand what was is going on.

What my post asked was “how can you restore from a backup without losing useful energy dashboard data?”

Normally I would say make a copy of your database before applying the backup, like this, Database Restore after Snapshot restore - #2 by tom_l

But your other topic that you linked to says the reason you rolled back was your increasing db size.

I didn’t read the other thread, but I can certainly relate. I assume you had some entity or entities spamming the database with frequent state changes or some such. Not all that uncommon, unfortunately. Ideally, each new entity should be reviewed for its impact on the database when it’s added, and excluded if you don’t need to keep all the state changes for whatever your keep_days duration is. Unfortunately the UI doesn’t make this easy.

In this case it sounds like your best hope is manually inspecting the database and using SQL to delete the offending records, while leaving the ones you want to keep. Not a simple process, even for those with database administration experience.

I personally think it was a mistake to add “energy” data to the existing Recorder database. Good design practices would suggest keeping archival and short-term data separate, at a minimum. Retention periods should also be more flexible than one keep_days parameter for all entities. But none of that is within my control, so we just have to work within the limitations we’re given.

1 Like

OK - that answers my question - thanks.

Yes - there was something weird and newish happening that was increasing the data base size by anything up to a 100MB in a few hours overnight. I had no idea what was going on but wondered if it was a glitch/update/config change so tried rolling back a few days to see if the increase still happened. It didn’t happen last night - db size has been steady ever since the restore - I’m hoping that in due course purge will get rid of whatever ‘rubbish’ has been added and my usual 100MB db size will be restored. If it isn’t then no doubt I’ll be back :grinning:

Have to say I agree. Nice to know I can get the long term data back if I’m on the ball but in my opinion system should provide an option to preserve the long term data when restoring.

Thanks both

1 Like