Home Assistant Add-on: PostgreSQL + TimescaleDB

Share your Projects!
,
1

Hi fellow Home Assistant Enthusiasts!

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

Repository Url: GitHub - Expaso/hassos-addons: Home Assistant Add-ons

TimescaleDB (PostgreSql + Extensions)

image
image1070×776 57.8 KB

This add-on runs a PostgreSql database server on your HomeAssistant installation, or can be run standalone from Docker/Kubernetes if you like!

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:password@77b2833f-timescaledb/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

image
image1639×919 127 KB

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:

Getting Started

For more information how to start using these plugins in combination with Grafana:
Getting started with Timescale & Grafana in HA

For more information and help about how to harness this power in real-life examples, please check out: Making Graphs in Grafana from Postgresql TimescaleDb Databases

Kind Regards,

Hans

16 Likes
2

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?

3

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
4

Awesome!

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

I’m running straight hassos on proxmox.

5

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

6

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

7

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!

8

Hi @noisedestroyers,

Thank you!

You need the 64-bit version of hassio: https://www.home-assistant.io/hassio/installation/

I know the 32-bit version is recommended, but in practice I haven’t noticed any problems with it at all.
From my memory, the 32-bit recommendation was due to certain add-ons like ESPHome not being compatible, but those days are long gone.

1 Like
9

I’ve been using this for about a week now. No issues other than my DB size has almost tripled from what it was with MariaDB.

10

Running this addon on Homeassistant Supervised, I recently tried to restore from a snapshot. The result was that this addon was not able to start due to permission denied error on access to the server configuration file postgresql.conf.

Any thought on what could cause this error? Thanks.

11

There are various ways to compress your data and keep the size in check.
It’s mostly a tradeoff between speed vs size.
Perhaps if you share your statistics I can point you in the right direction.

12

Hi Mil,

Can you post the log where you get the permission denied error?

Restoring from a snapshot sounds tricky because you basically go back in time with your database, thus loosing data after the point of the snapshot. Is that what you intended?

13

I didn’t copy the log, not sure if I still have it, but what it said was something very close to:

postgres: could not access the server configuration file "/data/postgres/postgresql.conf": Permission denied

Yes there would be a gap of missing data, but ideally a short one.

The only way I could get the database up and running again was start entirely fresh. Right now I’m not particularly concerned about the historic data, but in the future would like to be able to use the snapshot/restore process to migrate to new hardware, for example.

14

Hi Mil,

I created an issue in Github for it to research:

You can track the progress there, or add any more details if you have them.
Edit: Fixed

15

Hi @Expaso,
This is a really interesting addon I would like to play around with.
Do you have any suggestions as to how I could migrate my data from mariadb to your addon?

Regards Arendal

16

Yes, you could just dump the data with MySqlDump into INSERT INTO statements.
This script could then be replayed in Postgress.

If it’s really complex and a lot of data, you could use pgLoader: https://www.digitalocean.com/community/tutorials/how-to-migrate-mysql-database-to-postgres-using-pgloader

1 Like
17

Thanks @Expaso,
I had concerns mysqldump would somehow not be compatible with Postgress.

18

There is a sort of compatibility flag: Converting MySQL to PostgreSQL - Wikibooks, open books for an open world

If this road leads to nowhere, then pgLoader will do the job just fine.

19

Is it possible to reset the DB? Similar to delete the home_assistant_v2.db?

Thanks

20

How do you mean reset? Dump all the data and start over?