#Database setup/access/settings

1 messages · Page 1 of 1 (latest)

flat dune
#

Hi, I set up a containerized HA as a sniff test before I jump in whole hog. I'm coming from a totally home built system (I'm a software engineer by day). My question here is, is there some kind of documentation as to the type of database HA is using and the structure and access to that database? My system I'm coming from I'd have the 5 minute temperature of all my sensors stored in perpetuity and I wanted my new system with HA to do something similar (e.g. not average out/throw away old data). So in HA I set up a sensor and it clearly has some history because I can bring it up but I just wanted to find out what that data looks like over time if I wanted to go back to it. E.g. run some sql (or whatever database it is) query to look at what the temperature on some sensor was on a particular day last year.

stiff skiff
#

Yes, HA uses SQLite by default, and LTS is how it keeps (summarised) long term details

#

You probably want to send the data to something like InfluxDB instead though

somber ravenBOT
stiff skiff
flat dune
#

Ah ok cool, so an add on would kinda copy all the data out elsewhere?

stiff skiff
#

Well, copy out just what you want, to a time series database

flat dune
#

ah perfect, that is the documentation link I could not find

stiff skiff
#

Yeah, that kind of stuff is semi-hidden

#

The theory being that most people shouldn't ever touch it

flat dune
#

haha absolutely that makes perfect sense

#

interesting that it says the "default" db is SQLite- that sorta implies you can change it?

stiff skiff
#

Yes, you can

#

But you shouldn't

#

HA is optimised for SQLite, and other platforms can have unexpected problems

#

(like recently they found that at a certain point MySQL/MariaDB defaults to full table scans, when SQLite doesn't)

flat dune
#

interesting, yeah I "grew up" on SQL so have used a few different flavors of it. would be interesting to me to just create a database on my already running postrgresql so I could get at the data easily

#

does postgresql specifically have any of the issues you mentioned?

stiff skiff
#

Not that I know of

#

And the devs are pretty good at fixing them when they're found

flat dune
#

I also certainly wouldn't mind jumping in on such fixes and contributing. cool

#

Very much appreciate the direction, thanks @stiff skiff !

stiff skiff
#

Oh, and for changing what HA uses, that's in the recorder docs