The best way is to keep the database (addon) running and use pg_dump to backup the database. When you DO take filesystem snanshots (that’s what HA does), keep this in mind: PostgreSQL: Documentation: 9.1: File System Level Backup. Ergo, stop the addon, take snapshot, start the addon.
Because this addon could be your whole data storage for HA, the last option is inferior.
It’s better to use pg_dump.
1 Like
Hi @Expaso and thank you for your great work! i was getting crazy with influx!!! 
The addon you created is great, but i think i’m not using it in the right way.
I’ve installed the addon, imported the data and the db is working great, but i think i’m not using the potentiality of timescale because i’m getting my db growing over 3 gigabyte.
My question is, are these steps correct? do you have any suggestion or i’m missing something?
- i want to export all my data. Recreate the empty db, create the hypertable.
Regarding hypertables: do i have to partition over all time colums? which is the best solution?
i.e. in the states table i’ve found 2 timestamp columns, do i have to proceed in this way?
SELECT create_hypertable('states', 'last_changed', chunk_time_interval => INTERVAL '3 day');
SELECT create_hypertable('states', 'last_updated', chunk_time_interval => INTERVAL '3 day');
or do i have to use the partitioning_column parameter?
-
i will reimport my data, activate continuos aggregates and compression policy.
-
i will install LTSS
do i miss something?
Thanks for any answer to my few and very confused thoughts!!!
Haha Thank you @shakin89 for your kind words. I appreciate.
The addon provides basically 2 major usecases:
1- Replace the SQLLite backend of HA with a Postgres backend. It’s the HA’s own recorder component that is filling the states table with data. Within the recorder-component you can set how many days of historic data you want it to keep. By default this is only 24 hours, to keep the database small.
2- In combination with the LTSS integration, you can store data for much longer periods of time. The LTSS integration basically behaves the same like the recorder component, but does not remove any historical data (because you want it to store this data for long term, hence the name).
The LTSS integration create a table calles ltss, and will automatically create it as a hypertable. so you won’t have to do this manually.
So, keep the events table small (24 hours of 48 hours), and use LTSS as long-term sensor storage.
Now, the next step is to think about retenion, storage space and downsampling. So please take a look at a few posts higher where I point this in the right direction.
A combination of compression, downsampling with continues aggregates and a retention policy (i.e. drop old original data after downsampling) will give you a nice small and clean database.
How small depends on your needs and/or storage space or hardware. But a few hundred MB’s for a regular setup should be doable.
Thanks for your answer. I’ve set up a bash script to use pg_dumpall and putting the backup in the /share/ folder so that I can backup it using the normal backup process in HA. However, I’m having a hard time scheduling the script, as there doesn’t seem to be a cron service active in your container. Could you point me in the right direction here?
Sure! Indeed there is no CRON in the container, as it’s a stripped down Alpine container.
But, if you also use the pgAdmin 4 addon, you could use a pgAgent job. Both addons expose the /share folder.
I can’t get the pgAgent job to work. It works with a SQL statement, but running a bash job (tried to call my call script and also tried echo 1) is giving me a Script error: sh: /tmp/pga_11_11_someCode/11_11.scr: Permission denied. It seems that pgAgent is trying to create a temporary .scr file to execute the code, but is failing in doing so.
I can see the pgAgent Daemon running on the timescaleDB container, under the root user. The root user has access to /tmp and can create files there, so I’m a bit lost.
Just did the upgrade, as I’m using the add-on for a looong time.
Backup took almost an hour, but the upgrade went perfect.
@Expaso great job and many thanks !
Thank you so much! This type of comment is what makes open source so worthwile 
1 Like
I see the same behaviour… I’m trying to find out what it’s nagging about. I’ll keep you posted!
Tracked issue here:
It has to do with the way Home Assistant mounts the /tmp folder withinn the addon.
It will need additional fixes to make this work.
Any update on this? I’d love to automate my backup
1 Like
Hello everyone,
I have just installed TimescaleDB+PostgreSQL addon (twice) and I noticed it didn’t include the PostGIS extension. Is there any extra step I must do? Is it an extra installetion? Can anyone point me how to fix it?
I have a RPi 4 8gb Ram + SSD 240gb. I’m using the latest version of HassOS and TimescaleDB available. I haven’t installed LTSS yet, because I understood that PostGIS must be present first, right?
Thank you.
Hi @siqueira ,
You have to install the postgis extension for the database you want.
See this screenshot:
Thanks, @Expaso,
That’s what I did. I just didn’t see this step explicitly mentioned in the documentation.
Can you point some resources (docs/videos) on how to use Grafana with Timescaledb for beginners? Although SQL is easier than Influx, at least for the first steps and simple graphics, setting up Grafana and InfluxDB for the first time seemed easier than with postgresql/timescaledb.
I also have the impression that you can find much more how-tos with InfluxDb than with timescaledb. Is this correct?
Thanks,
Paulo
Hi @Expaso
could you please add information on how to access (the filesystem of) the add-ons into your initial post? I am running both addons on hassos and tried several things to access the add-ons, mainly to upload and migrate my sqlite database and have spent hours on this already.
With the terminal / SSH, all directories like /data /mnt /media are empty.
Also I cannot access the postgresql port from outside with another database editor (which usually is fine).
The database itself is running fine.
thank you!
Sure! Please check this info: Pinned: Additional Documentation for Timescaledb Integration · Issue #1 · Expaso/hassos-addons (github.com)
Hi Andre! Sure!
First, the addon mounts the SHARE folder from homeassistant itself on /share within the addon.
So anything you read or write on /share from within the addon, you can access from outside the addon and viceversa.
You can use this to transfer backup files from and to the addon, or basically any file you need.
Second, you can control the PostgreSql instance from outside of the addon (from anywhere on your network) if you just open-up it’s port to the outside world, from the configuration-page of the addon in the network section:
Hope this helps!
I saw I didn’t do a reply @siqueira , sorry…
Here is a link with a basic setup: Sure! Pinned: Additional Documentation for Timescaledb Integration · Issue #1 · Expaso/hassos-addons (github.com)
I can indeed image that setting up Influx is easier at first, and some basic knowledge of SQL is very helpful. Having said that, SQL and especially timescale gives you far more options and freedom to shape and query the data you need. Don’t be fooled! Getting dashboarding right can be quite a challenge!
Influx is fairly basic compared to timescale, so that explains the big difference in videos. You are in the advanced corner here 
Although for people with a lot of SQL knowledge, it may be easier here then in Influx-land.
Please check this link from the docs: Timescale Documentation | Create a Grafana dashboard and panel
And ofcourse all videos abount Grafana and Timescale together are a valid source of information too.
Did you find a solution for properly performing pg_dump based backups with pg_agent job using script gives permission denied error in TimescaleDb · Issue #6 · Expaso/hassos-addon-pgadmin4 · GitHub being still open?
I’m currently backing up timescaledb with GitHub - sabeechen/hassio-google-drive-backup: Automatically create and sync Home Assistant backups into Google Drive which works fine-ish, since it has to stop the timescaledb addon during every backup.
If I don’t set it up this way the filesystem-based backup occasionally does not contain all data.
This addon-stopping is not ideal, since I now have daily gaps of ~20min during the backup procedure…
Hi @tldp ,
I am in the process of creating a new update, and address this issue as well.
The only major hurdle I am having right now, is a compiler-crash when trying to compile timescaledb-toolkit for ARM.
This doesn’t seem to work properly at the moment. Looks to be a bug in QEMU that I am waiting to be fixed upstream.
It this takes too long, I could do an intermediate release, but I certainly haven’t forgotten you.
2 Likes