Hello, i’ve a question regarding downsampling and compress data on ltss database. How do you do it ?
Hi edouardkleinhans!
Compression and down sampling can both be used.
Please check out the respective tutorials from timescale:
For downsampling, you first create some Continuous Aggregates (views) with the granularity of the downsampling required.
These Aggregates are basically materialised views. I.e., they are created apart from the underlying data and contain the sums or averages , mins en Max’s for your downsampling.
After this step, you can drop (gradually phase out) your original data with a retention policy:
Thanks for this reply. Did you implement them ?
Heads-up for everyone waiting for the new version:
All changes have been made.
I am currently beta-testing on my own setup…
The addon will automatically upgrade your setup to Postgres 14.2, and TimesaleDB 2.6.0
Getting the upgrade process just right, including all these timescale and postgis extensions requires a lot of tweaking and testing…
Stay tuned!
3 Likes
can you share your configuration ?
Getting an upgrade error with the latest release; github bug added.
Please keep an eye on the issue tracker: The upgrade is quite a large one, so backup before upgrading.
I get some errors while using this add-on.
First I had this error when I went to the webpage:
UTC [1222] FATAL: the database system is shutting down.
And also a lot of those errors in the log.
After a restart I now get these errors in the log:
2022-04-04 05:28:29.977 UTC [789] FATAL: password authentication failed for user "postgres"
2022-04-04 05:28:29.977 UTC [789] DETAIL: User "postgres" has no password assigned.
Connection matched pg_hba.conf line 99: "host all all 0.0.0.0/0 md5"
And this on the webpage:
OperationalError: connection to server at "77b2833f-timescaledb" (172.30.33.6), port 5432 failed: FATAL: password authentication failed for user "postgres"
What to do? I haven’t changed anything, but somehow it stopped working!
Hi, @rednas we can follow this issue here: Password authentication failed for user "postgres" after upgrade to 2.0.0 · Issue #14 · Expaso/hassos-addon-timescaledb · GitHub
Hi everyone.
Just looking into getting this plugin for the postgresDB (but the added benefits+possibilities does look good), but I am just wondering about the pg_hba.conf.
I suppose that the “init_commands” is present because of any docker containers, and so the config is not stored long-term (is this correct?).
In which case, if I wanted to change the “pg_hba.conf” to remove md5 and only use scram-sha-256, would I have to put the command in there?
… I assume it’s the standard location (/etc/postgres/pg_hba.conf) - but it’s not really specified…
–
Whizz
Hi @old486whizz,
It’s indeed a docker container. That’s how Home Assistant addons work.
But the data IS stored long-term. (otherwise your data would not survive a reboot).
From the containers perspective, the folder /data is non volatile, thus stored on disk.
The pg_hba.conf is located at: /data/postgres/pg_hba.conf
Does this answer your question?
1 Like
Hello everyone,
this add-on was brought to my attention and I have two questions:
- Why the need for ltss? If I am not mistaken, the recorder supports PostgreSQL, so would it not be sufficient to simply define PostgresSQL there with the mathinc include/exclue statements?
- Is it possible to directly plot the data from the database in “normal” dashboard cards such as ApexCharts, HistoryGraph etc. etc.? Or does this only work with Grafana?
Thank you for you support
Alex
1 Like
Hi
has anyone found a solution to the problem that User "postgres" has no password assigned. after upgrade?
I can see the issue is closed on GH but the suggested solution seems to be to downgrade – which I not find satisfying.
The comment by @Expaso also stresses that all databases need to be listed in the plugin config. How would I know which ones these are?
I would appreciate if all steps are clearly spelled out.
I had a look for the /data/postgres directories but when I ssh into my HASSIO installation the /data directory does not include any such directories.
I had a look into the /addons directory but that is empty.
Help would be appreciated.
I managed to find the data folder by logging in via port 22222 (following these instructions: Debugging the Home Assistant Operating System | Home Assistant Developer Docs). Then found the DB files in /mnt/data/supervisor/addons/data/77b2833f_timescaledb.
Now that I can move the postgres data folder - how do I trigger either downgrade or restart the upgrade?
The addon is listed in version 2.0.1
I tried to restore a partial backup to version 1.1.6 but that failed with
'BackupManager.do_restore_partial' blocked from execution, system is not running - CoreState.FREEZE
Any ideas?
Hi Dan!,
Just restart the 2.0.1 addon when you moved your postgres12 folder to postgres.
It will just try the upgrade again. Make sure you have listed all timescale capable databases in the config before starting the addon.
Yes, that works! Many thanks!
Second attempt to try to update from 1.6
AGain, failures 
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
loadable_libraries.txt
Failure, exiting
[12:42:31] ERROR: PostgreSQL could not upgrade! Please inspect any errors in the lines above!
[12:42:31] ERROR: Upgrade was not succesfull.
[cont-init.d] init.sh: exited 1.
[cont-finish.d] executing container finish scripts…
[cont-finish.d] 99-message.sh: executing…
Oops! Something went wrong.
We are so sorry, but something went terribly wrong when
starting or running this add-on.
Be sure to check the log above, line by line, for hints.
[cont-finish.d] 99-message.sh: exited 0.
[cont-finish.d] done.
[s6-finish] waiting for services.
[s6-finish] sending all processes the TERM signal.
[s6-finish] sending all processes the KILL signal and exiting.
Any help would be appreciated, total new here
@poudenes Could you please share you backup procedure? Unfortunately I have no clue how to set it up.