Migrate database - lazy way?

Configuration
1

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?

1 Like
2

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.

3

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.

4

But HA will be slowed down by it the bigger you get…
You do you.

Honestly have you looked at that old data to make decisions you could have made without it?

I just delete my database every year. I’m more interested in my stuff working fast and snappy than having graphs of old data that is of no use.

5

I would very much prefer to stick to the original intend of the question.