Home Assistant Add-on: PostgreSQL + TimescaleDB

Tags: #<Tag:0x00007f738ebb45f0> #<Tag:0x00007f738ebb4528>

Hi fellow Home Assistant Enthusiasts!

Today I announce the launch of 2 new HomeAssistant add-ons:

Repository Url: https://github.com/Expaso/hassos-addons

TimescaleDB (PostgreSql + Extensions)

This add-on runs a PostgreSql database server on your HomeAssistant installation.

You can use this database-server to replace the default HomeAssistant SQLLite database (the one that is used by the recorder-component and stores all entity-states).

Recorder

The recorder-component can be configured to use PostgreSql instead of it’s default SQLLite connection by using the following database-url:

postgresql://user:[email protected]/homeassistant

For more information on configuring the recorder-component, please read the Docs

TimescaleDb Extension

The addon also has TimescaleDb extensions installed. These extensions open-up the possibility of using this database-sever as a timeseries database, and thus can replace InfluxDb in some occasions.

Why should you want to do that? Well, read this excellent blog-post from TimescaleDb itself, and judge yourself.

TLDR; If you already know SQL, you’re gold. Learning new query languages like InfluxQL or Flux simply isn’t needed for querying time-series data.

To bring your precious sensor-statistics into TimescaleDB, you can use the Long Time State Storage Component. Available on HACS.

Of course Grafana has first-class support for TimescaleDb and PostgreSQL, so this makes a great combination for dashboarding, alerting and statistical analysis, based on your sensor data.

Oh, and before I forget: This addon also packs the Postgis extension. Postgis can be used to query geospacial data. With this extension you finally can query alerts when the smartphone of your kid is detected more then 5 blocks away. Can become handy :wink:

pgAdmin 4

No database server is complete without a powerful management front-end.
Therefore, I packaged pgAdmin as an add-on, ready for you to install.

pgAdmin is a web-based database management tool for PostgreSql.
You can manage your TimescaleDb instance with it.

I chose to pack it separately from the TimescaleDb add-on, so you can choose this, or your own tooling, without forcing the extra overhead upon your installation when not needed.

You can connect to your TimescaleDb instance by using it’s internal mDNS name:

77b2833f-timescaledb port 5432

You don’t need to expose any portnumbers from the TimescaleDb add-on, if your intent is to use it solely for your home-assistant installation.

Conclusion

With these add-ons, I hope to fill-in a little gap in the more-then-complete Home Assistant ecosystem, and give people a choice about what tool they want to use for a specific purpose.

If you have suggestions or encounter any problems, please create an Issue on the respective Github pages:

Add the following repository-url to obtain these add-on’s:

Kind Regards,

Hans

1 Like

Hi, I’m interested in running this add-on, but looks like it will not run on Raspberry Pi 3B. What are the hardware requirements?

Hi Mil,

I am afraid that running a full-blown Postgresql and timescaledb addon on a Raspberry Pi3 is a little bit too much.
I assume you also run Home assistant on it, and some other addons.
My advice would be to run it at least on a Raspberry Pi 4 with 4GB of memory.

1 Like

Awesome!

Has anyone had issues connecting to the DB via 77b2833f-timescaledb or timescaledb?

I’m running straight hassos on proxmox.

My mistake. I was using 77b2833f_timescaledb, not 77b2833f-timescaledb.

Great. I’m planning to upgrade the hardware. Good to know. Thanks.

Hi Expaso,

Great work on the add-on…thank you for the time and effort!

I’m running hassio on a raspberry pi 4 (armv7l architecture). I add your repo in the supervisor panel and am able to see 2 plugins.

The problem I have is timescaledb add-on shows "This add-on is not available on your system."

I see in config.json:
"arch": ["aarch64", "amd64"],

Is this a restriction due to the referenced dockerfiles? Any advice on how to get this working on an rpi4?

If you have ideas, I’d love to help work on them.

Thanks again!