Is there a way to migrate the database from SQLite to PostgreSQL without manually migrating the data?
What do I mean?
My SQLite file has reached 8 GB in size and I’m afraid that somewhen in the future I will reach the limit of what SQLite can handle.
So, I would like to migrate to (dedicated) PostgreSQL.
But I do not want the hassle of moving data myself.
So, what I would hope to achieve: Both systems coexsit while new data is written to PostgreSQL only and eventually the SQLite will shrink in size because data is expiring and deleted.
Alternatively: It would be great if I would be able to simply add the PostgreSQL setup to the pool and then wait for Homeassistant to migrate the data on its own in the background.
Is there any way to make either approach possible?
Changing the database type is very unlikely to help, as HA has been tweaked to work the best with the default.
You are MUCH better off moving that data to a separate database like influxDB or something. Then the HA database can be nimble and be fast with what you need it for, collecting and automations.
That is not addressing the issue because it would require to do what I want to avoid: Moving data manually.
Or am I missing something?
I do not have a performance issue. I have an issue of an ever growing database and I’m pretty sure that PostgreSQL can handle this quite fine while SQLite will reach some kind of limit rather soon.