Odd… well yes and no I guess. But @baz123 happy you seem to be up and running! This all is far above my pay grade, but it does seem like the developer were poking around issues that seem related to your issue. This first post about Postgresql and second about marina seems to show there were problems with the database scheme change I noted in one of prior posts. I am postulating that I did not have problems since I was on Postgresql prior to this change in schema and you unfortunately were moving to Postgresql right in the middle of various versions treating these relation conditions differently…
https://github.com/home-assistant/core/issues/40115
https://github.com/home-assistant/core/issues/42402
It is becoming more and more difficult to ‘roll the dice’ on upgrades. I do not have a fully isolated test environment for my ‘home’ nor do I really want to put the effort and money into having something were I can test a HA upgrade before putting it into production. So for now, I use the ‘sit back and watch other suffer and fix’ method . However, as this project grows in dimensions, the chances of a screwing up my database history, or corrupting my install to where it will be a real pain to recover becomes a much greater possibility.
The ‘breaking changes’ is a must read, but I do not think that has complete coverage. For example, there was a database schema change when I upgrade from 0.118 to 2020.12.1, but I have yet to find what changed. And luckily it caused no issues for my little ‘tweak’ to the system to copy the records to my archive database.
To your point about InfluxDB and Postgresql, I agree that having a time series tool to analyze sensor data in HA is a key tool if you want to ‘operate’ your home effectively. Combined with how we started this conversation, if you cannot look back over, I think, at least two years of your home’s data, you are just guessing at what the effect of lighting, insulation, solar, HVAC changes make.
I again rationalized my move out of InfluxDB to Postgresql, partly because Postgresql is so extensible. There is a time series layer call Timescale that seems to give Postgresql much if not all of InfluxDB’s traits and abilities. Especially for the size of data that a home generates, we are not even close to pushing either of these tools. I have not installed Timescale as yet, mostly because I have been able to solve 100% of questions to date with just Postgresql, Pandas, Juypter stack. Yes, I have had queries/calculations that have run 30 plus minutes, but I am not time constrained. And in these COVID times, that means a couple more ’s or ’s !!!
Interested in your thoughts on sensor analysis and tooling that is useful for home automation.
My other point about having to keep up on many tools as another reason why I moved away from InfluxDB. This comparisons of queries of TimescaleDB vs. InfluxDB in this article highlights my past head scratching and googling when trying to use InfluxDB:
https://medium.com/timescale/timescaledb-vs-influxdb-for-time-series-data-timescale-influx-sql-nosql-36489299877
Good hunting and Happy New Year!