Home Assistant Add-on: PostgreSQL TimescaleDB
From: https://www.postgresql.org/about/
PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. The origins of PostgreSQL date back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform.
PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions.
From: https://docs.timescale.com/latest/introduction
TimescaleDB is an open-source time-series database optimized for fast ingest and complex queries. It speaks "full SQL" and is correspondingly easy to use like a traditional relational database, yet scales in ways previously reserved for NoSQL databases.
Compared to the trade-offs demanded by these two alternatives (relational vs. NoSQL), TimescaleDB offers the best of both worlds for time-series data:
Full SQL interface for all SQL natively supported by PostgreSQL (including secondary indexes, non-time based aggregates, sub-queries, JOINs, window functions).
- Connects to any client or tool that speaks PostgreSQL, no changes needed.
- Time-oriented features, API functions, and optimizations.
- Robust support for Data retention policies.
Say, you want put all those nice Home Assistant measurements from your smarthome to good use, and for example, use something like Grafana for your dashboards, and maybe Prometheus for monitoring..
That means you need a decent time-series database.
You could use InfluxDB for this. This works pretty good.. but.. being a NoSQL database, this means you have to learn Flux (it's query language). Once you get there, you will quickly discover that updating existing data in Influx is near impossible (without overwriting it). That's a bummer, since my data needed some 'tweaking'.
For the Home Assistant recorder, you probaly need some SQL storage too. That means you also need to bring stuff like MariaDb or Postgres to the table (unless you keep using the SqlLite database).
So.. why not combine these? Seriously?! You ask...
Yeah! Pleae read this blogpost to get a sense of why:
And so.. Use the power of your already existing SQL skills for PostgreSQL, combined with powerfull time-series functionality of TimeScaleDb and be done with it!
As a bonus, I also added a Geospatial extention: Postgis. You can now happily query around your data like a PRO 😎.
There are two ways to install this add-on: via the Home Assistant add-on store or, by running the container manually on a separate (more powerfull?) machine. This could come in handy if you want to use a more powerfull machine for your database, or if you want to use a different OS than Home Assistant OS.
To install this Hass.io add-on you need to add the Expase add-on repository first:
You can do this by navigating to the "Add-on Store" tab in the Supervisor panel and then entering https://github.com/Expaso/hassos-addons in the "Add new repository by URL" field.
Now scroll down and select the "TimeScaleDb" add-on. Press install to download the add-on and unpack it on your machine. This can take some time.
Start the add-on, check the logs of the add-on to see if everything went well.
Example add-on configuration:
{
"databases": ["homeassistant"],
"timescale_enabled": ["homeassistant"],
"timescaledb": {
"telemetry": "basic",
"maxmemory": "512MB",
"maxcpus": "4"
},
"max_connections": 20,
"system_packages": [],
"init_commands": []
}
Sets a list of database-names that will be created for you, once you start the add-on. You can also create databases on your own ofcourse, using a psql client of your choice.
Sets a list of database-names where the timescale-extentions will be enabled for. Databases not in this list will act like normal Postgre databases.
Switches the telemetry of TimescaleDb on or off. Valid options are: 'basic' or 'off'. See: https://docs.timescale.com/latest/using-timescaledb/telemetry
Sets the maximum amount of memory that PostgreSQL will claim. It's important to leave breathing room for other processes on your machine (or raspberry pi), so set these level not too high (say max 50% of your total ram).
Example: maxmemory="1024MB"
Or leave empty for accepting auto-tune.
Sets the maximum number of cores that PostgreSQL will use. It's important to leave breathing room for other processes on your machine (or raspberry pi), so set these level not too high (say max 75% of your total number of cores).
Example: maxcpu="2"
Or leave empty for accepting auto-tune.
See also: https://docs.timescale.com/latest/getting-started/configuring for further tuning. Your Postgres.config file is located in the addon's data directory.
Sets the maximum number of connections that PostgreSql will accept. Setting this higher could lead to more memory usage.
Example: max_connections=30
Advanced users only! A list of extra alpine packages to iunstall during addon-startup.
Example: ['nano']
Advanced users only! A list of extra commands to run during startup.
To alter something in the postgresql.conf file for example:
Example: ['sed -i -e "/max_connections =/ s/= .*/= 50/" /data/postgres/postgresql.conf']
Advanced users only! When set, the upgrade from Postgres 14 to 15 could be retryed if it failed mid-flight. Basically this will try to find the old database-files from Postgres 12, and restore them before trying to upgrade to Postgres 14 again.
!! Please don't set this if you don't know what you are doing or before taking a backup. !!
In this case, you need to have a working Docker installation on your machine. pull one of the images for the desired architecture from docker hub:
docker pull husselhans/hassos-addon-timescaledb-amd64:latest
docker pull husselhans/hassos-addon-timescaledb-aarch64:latest
docker pull husselhans/hassos-addon-timescaledb-armv7:latest
docker pull husselhans/hassos-addon-timescaledb-armhf:latest
docker pull husselhans/hassos-addon-timescaledb-i386:latest
You can replace latest with the version number you want to use.
Simply start it like this:
docker run \
--rm \
--name timescaledb \
--v ${PWD}/timescaledb_addon_data:/data \
-p 5432:5432 \
husselhans/hassos-addon-timescaledb-amd64:dev
This will use ~/timescaledb_addon_data as the data directory for the container, and map the port 5432 to the host.
If you want to start the container as a daemon, simply remove the --rm option and add the -d option like so:
docker run \
-d \
--name timescaledb \
--v ${PWD}/timescaledb_addon_data:/data \
-p 5432:5432 \
husselhans/hassos-addon-timescaledb-amd64:dev
You are now ready to start using Postgres with TimescaleDb extenstions enabled!
Seeking a nice web-based client? Try the pgAdmin4 addon.
Please do not forget to also map the TCP/IP port in the network-section of the addon to the desired port number.
The default is port 5432
Securiy Notice!
The default username is postgres with password homeassistant.
Make sure you change this immediately after activating the add-on:
ALTER USER user_name WITH PASSWORD 'strongpassword';
A default pg_hba.conf is created in the data directory with the following content, which allows local peer users and network users with passwords.:
# TYPE DATABASE USER ADDRESS METHOD
host all all 0.0.0.0/0 md5"
local all all 0.0.0.0/0 md5"
local all all 0.0.0.0/0 peer"
Please review this configuration carefully by examine the docs: https://www.postgresql.org/docs/devel/auth-pg-hba-conf.html
Well.. Dive in!
You can read additional documentation on how you van work with your data and Grafana here: