Update: Expaso added a so-called standalone mode, making my manual script handling unnecessary. Thanks a lot!
Hi! With the @expaso 's help, I managed to recover the data fine and documented it in this repository: GitHub - hnykda/backup-restore-ha-timescaledb: A repository with tools and examples to backup, validate and restore postgresql+timescaledb with LTSS for HA . Thanks @expaso !
Addon Version 4 has arrived, finally!! 
This release contains the long requested and awaited timescaledb-toolkit!
If you use the timescaledb-extras scripts, please apply them again after upgrade, because the upgrade procedure in the addon has to remove some objects that are otherwise blocking the upgrade of TimescaleDb.
If you have any questions of difficulties with the upgrade, please let me know. I will be happy to assist.
Also: I take feature-requests seriously. So make sure you create a ticket for them, on github ( Issues ¡ Expaso/hassos-addon-timescaledb (github.com)).
Enjoy!
Kind regards, Hans
Fantastic work Expaso, truly appretiated! Im giving it a swirl for the first time today.
Everything went smooth setting it up, apart from that I first attempted to create a new user (homeassistant) and provided it regular access (read/write) only in postgres. This resulted in the recorder not being able to load properly, so I reverted to the postgres admin user. All good after that, but not sure if it was a missing priviledge in postgres or some config in HA.
Anyways, proceed testing 
Few questions:
Today I run the SQLite database and only keep three days but also scrape with Prometheus due to the nature of longer term stats I want to retain and keep the native HA DB down in size.
With this I could leverage PSQL+TSDB and replace both SQLite and decouple my Prometheus from this? I wonât be getting ride of Prometheus as I use it for other monitoring efforts in the home ecosystem.
Is there any good documented procedure to âslurpâ in all the existing SQLite records into this PSQL+TSDB model?
I read about Long Time state storage from an HACS add-on and is this required anymore? Wouldnât HA take care of this natively for long term stats from the SQL stuff they do natively or is this required still?
Thanks!
Hi @pyrodex!
This addons, in combination with LTSS replaces the SQLLite database entirely.
The SQLLite database is used by the home-assistant recorder, which you can give a connection-string to the TimescaleDB addon.
The recorder records say 3 days of detailed data, that is now going into your SQLLite, but then goes into Porstgres (this addon).
Next to that, you install LTSS (which is literally a beefed-up version of the recorder component) and post it ALSO to a secondary database of this addon.
Now, LTSS does not delete data. So all your detailed data that goes into the recorder, also goes into LTSS.
But⌠Within the timescale-addon, you process that data by means of compression and/or down-sampling (continues aggregates) of this detailed data so you can do fast statistics on it.
Personally, I run this addon for almost 4 years now in my own home setup on a raspberry pi 4. the size of my database is like 53GB, and I have NEVER deleted any data, nor do I have any performance issues. My dashboards are lightning fast, all thanks to TimescaleDb and itâs compresson + aggregate features.
Edit: to answer your question: Yes, there is small overlap with HAâs own Long Term Statistics, but these statistics are accumulated in a different way and are locked into HA. With this addon, you can take data from any source, and combine it with SQL. This is needed if you want to build your own custom dashboards.
Note that since you are replacing SQLLite, ALL of HAâs data is also stored in a postgresql database within the addon. Here youâll see that HAâs Long Term statistics are just tables within my addon:
And here is the documentation of it:
Long- and short-term statistics | Home Assistant Data Science Portal (home-assistant.io)
Oh yeah, use pgLoader to migrate existing data if you like: SQLite to Postgres â pgloader 3.6.9 documentation
All in all: It all works beautifully together complementaring eachother. This is the real power of home-assistant, right here 
Just thinking: Could it be that your user did not have the rights to create or alter the schema? You could try to add those rights, and try againâŚ
@Expaso Is the normal HA data optimized with this addon or do you need to use LTSS to benefit from it?
I.e. without any extra configuration (other than installation) will this be more performant (moar speed and less size) than for example MariaDB?
Does anyone migrated from MySQL to Postgres? Iâve seen there is the pgloader app that can be installed, but maybe there is an easier solution.
Otherwise i would deploy a additional postgres server with pgloader, backup and restore to this Add-on.
Hi Expanso
I have an HA install with TimescaleDB Addon v 2.1.1 .
My system tells me I can upgrade to 4.0.1 - great. But when I tried this I got a message saying my DB was incompatible.
This could have been made trickier by lack of disk space. I eventually got it
back to the same place
Iâve how upgraded to a bigger disk, but I donât want to risk pressing the upgrade
button in HA in case it blows up again. Obviously, I could backup, try, restore -
but even that feels risky
Do you thnk it can upgrade from 2.1.1 to 4.0.1? If not, what is the procedure?
Thanks
Martin Green
A question⌠Which tables I should clear while excluding entities which do not make sense to store?
Im just in beginning of this process as noticed that my DB getting too large with all nonsense
This is my current configuration:
recorder:
db_url: !secret psql_string
db_retry_wait: 15 # Wait 15 seconds before retrying
exclude:
domains:
- automation
- updater
entity_globs:
- sensor.weather_*
- sensor.*rssi
- sensor.*_voltage
entities:
- sun.sun # Don't record sun data
- sensor.last_boot # Comes from 'systemmonitor' sensor platform
- sensor.date
event_types:
- call_service # Don't record service calls
## LTSS
ltss:
db_url: postgresql://xxxxxxxxxx:xxxxxxxxxxxxxxxxx@77b2833f-timescaledb/homeassistant
include:
domains:
- sensor
exclude:
domains:
- automation
- updater
entity_globs:
- sensor.weather_*
- sensor.*rssi
- sensor.*_voltage
While doing delete to existing entries, which tables I need to clear?
Currently deleting from this table:
DELETE FROM public.ltss
WHERE entity_id like 'sensor.%_voltage';
Then how about these as there are nice chuck of data?
I just performed a successful migration from SQLite to PostgreSQL+Timescale DB. SQLite has performance issues that make it difficult to process larger data sets. My general recommended procedure:
ha core stop.home-assistant_v2.db to a working directory.migration.sql file for PGLoader in the working directory:load database
from sqlite:///path/to/local/home-assistant_v2.db
into postgresql://USER:[email protected]/yournewhomeassistantdb
with data only, drop indexes, reset sequences, truncate, batch rows = 1000;
ha core start on the CLI.ha core stop again on the CLI. table name errors rows bytes total time
----------------------- --------- --------- --------- --------------
fetch 0 0 0.000s
fetch meta data 0 55 0.024s
Drop Foreign Keys 0 14 0.012s
Drop Indexes 0 33 0.020s
Truncate 0 13 0.020s
----------------------- --------- --------- --------- --------------
event_data 0 3316 456.0 kB 0.068s
state_attributes 0 110320 18.9 MB 0.604s
statistics_meta 0 85 5.0 kB 0.688s
schema_changes 0 9 0.3 kB 0.748s
events 0 16436 1.7 MB 0.824s
statistics_short_term 0 168080 13.0 MB 1.824s
recorder_runs 0 19 1.6 kB 0.004s
statistics_runs 0 3619 120.2 kB 0.096s
statistics 0 138896 11.8 MB 0.984s
event_types 0 37 0.9 kB 0.596s
migration_changes 0 4 0.1 kB 0.688s
states_meta 0 9063 589.7 kB 0.044s
states 0 332245 46.2 MB 1.980s
----------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 3.284s
Create Indexes 0 33 4.308s
Index Build Completion 0 33 0.652s
Reset Sequences 0 0 0.048s
Primary Keys 0 13 0.020s
Create Foreign Keys 0 7 0.276s
Install Comments 0 0 0.000s
----------------------- --------- --------- --------- --------------
Total import time â 782129 92.8 MB 8.588s
ha core start.NOTE: Based partially off of: Migrate Home Assistant from SQLite to PostgreSQL
Does anyone else have issues with HA 2024.8? Uppon load of the LTSS integration during ha startup, TimeScaleDB restarts. This makes the HA recorder losing connection. I am not sure where this issue belongs as it relates to three products. I think this happens during db schema update that comes with ha 2024.8.
I have created a bug report.
Hi @balk77 , author of Timescaledb addon here, This sounds not goodâŚ
Is this a onetimer or does TimescaleDb dont want to start at all after this error?
Btw, thanks for your detailed bug report. Really appreciate the detail of information. 
I will look into this asap and track progress through the bugreport on github.
Currently I am abroad, but will fly back tomorrow.
Perhaps a slightly different topic. But since this addon uses PostgreSQL/TimescaleDB, still relevant.
There is currently a high rated security issue for all those who backup their database via pg_dump using the root user: PostgreSQL: CVE-2024-7348: PostgreSQL relation replacement during pg_dump executes arbitrary SQL
In such a case you should update the affected Postgres version and/or not use a root user for backups.
It happened several times. Every time the db crashes, which causes the recorder to fail. At that point, tsdb starts but it is not connected to ltss or ha.
Will try to disable ltss and retry to see what happens. Not home though, will test in couple of days.
Thanks for the wonderful addon!
I will update PostgreSql and related tools thenâŚ
Looks like a bug in Timescale, currently working on a new version of the addon whereby I will update Timescale to address this issue.
Very usefull!! I will add this to the migration section!
