Home Assistant Add-on: PostgreSQL + TimescaleDB

Share your Projects!
,
83

Hahaaaa… yeah well, it’s a real database for sure.
Systems like SQL Server and Oracle all have their own backup methods, so
pg_dump / pg_restore are the tools for Postgres.

Please read the docs carefully, because pg_dump can be used with the -Fc switch to compress the backup file. This might be beneficial.

84

Hello,
I installed both of the add-ons and this is probably a silly question.
When I open pgAdmin 4 I don’t see any servers. I basically see “Servers” on the left, but when I click it’s empty.
I also have the DSMR reader installed and set up, so I want to see whether I can open this database.
How can I fix this?

85

Hi @rednas!

Thank you for using the addon!
Check the first post of this thread. There is more information there.
Basically, you should be able to connect using this: server: 77b2833f-timescaledb port 5432

86

@Expaso do you plan on releasing a update to this Addon anytime soon? the addon’s main Repo looks to have been updated to use TimescaleDB 2.0.1 and Postgres 12.5 but the current release is still on 1.7.4

87

Hi @antbarney,

I was planning to, but covid and some other bad things happened, I’m sorry for the delay.
I am expectring to have the next release ready within 2 weeks from now. I will bump timescale as much as I can.
I was planning on bumping Postgres to 13 also, but this feels like a mile too far for now.

88

Good job. I’m tempted to install this addon, hope this to bring some stability to my HA server (a N4100-based little pc. Currently it’s becoming unavailable every 2-3 days…).

89

Well, I run it on a PI 4 , including grafana and all that stuff. Albeit I boot from SSD …
Works like a champ!

90

Hello, i’ve a question regarding downsampling and compress data on ltss database. How do you do it ?

91

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:

92

Thanks for this reply. Did you implement them ?

93

Hi @edouardkleinhans , Yes I use both downsampling and compression in my own setup.

94

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!

image
image1575×562 56.1 KB

3 Likes
95

can you share your configuration ?

96

Getting an upgrade error with the latest release; github bug added.

97

Please keep an eye on the issue tracker: The upgrade is quite a large one, so backup before upgrading.

98

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!

99

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

100

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

101

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
102

Hello everyone,

this add-on was brought to my attention and I have two questions:

  1. 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?
  2. 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