Home Assistant Add-on: PostgreSQL + TimescaleDB

Tags: #<Tag:0x00007f780d511e40> #<Tag:0x00007f780d511d28>

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