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.
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:
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.
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.
I did follow this guide to migrate sqlite to postgres
This is the file for pgloader from another linux
load database
from sqlite:///tmp/home-assistant_v2.db
into postgresql://postgres:[email protected]/homeassistant
with data only, drop indexes, reset sequences, truncate, batch rows = 1000
SET work_mem to '32 MB', maintenance_work_mem to '64 MB';
Password is correct for sure as I can use pgadmin addon with same credentials.
This is what I get while using pgloader
sudo pgloader migrate.sql
KABOOM!
FATAL error: Failed to connect to pgsql at "192.168.1.11" (port 5432) as user "postgres": Database error: Socket error in "connect": ECONNREFUSED (Connection refused)
An unhandled error condition has been signalled:
Failed to connect to pgsql at "192.168.1.11" (port 5432) as user "postgres": Database error: Socket error in "connect": ECONNREFUSED (Connection refused)
What I am doing here?
Failed to connect to pgsql at "192.168.1.11" (port 5432) as user "postgres": Database error: Socket error in "connect": ECONNREFUSED (Connection refused)
I’m using default settings of this addon as I’ve no clue from where I could find pg_hba.conf from Home Assistant OS?
The Postgresql instance is by default not exposed to the outside world (because that is more secure), but you can enable this easily in the settings pane of the addon, and restart the addon:
/tmp$ sudo pgloader migrate.sql
KABOOM!
FATAL error: Failed to connect to pgsql at "192.168.1.11" (port 45678) as user "postgres": 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
An unhandled error condition has been signalled:
Failed to connect to pgsql at "192.168.1.11" (port 45678) as user "postgres": 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
What I am doing here?
Failed to connect to pgsql at "192.168.1.11" (port 45678) as user "postgres": 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
Waiting for the monitor thread to complete.
edit: Really not sure what im doing
Anyway. I took backup of this addon to reach out the files and looks like it’s using Default scram-sha-256 as this is commented out in postgres.conf file?
#password_encryption = scram-sha-256 # scram-sha-256 or md5
Not sure is there anyway to edit that file directly in HA, but could this workaround work. I’ll take backup and edit postgres.conf from it and then throw that backup back to HA?
This just in case there are no easier option to get it to work.
Ok. I think I made it… I used Dbeaver to import sqlite data and then adjusted Postgres seq’s with below statements.
Only lost 24h data from it as didn’t do it yesterday and I did not want to wonder how I can get all ID values set to not get all constrait violations while keeping data which were on postgres after the change.
SELECT SETVAL('public.event_data_data_id_seq', COALESCE(MAX(data_id), 1)) FROM public."event_data";
SELECT SETVAL('public.event_types_event_type_id_seq', COALESCE(MAX(event_type_id), 1)) FROM public."event_types";
SELECT SETVAL('public.events_event_id_seq', COALESCE(MAX(event_id), 1)) FROM public."events";
SELECT SETVAL('public.recorder_runs_run_id_seq', COALESCE(MAX(run_id), 1)) FROM public."recorder_runs";
SELECT SETVAL('public.schema_changes_change_id_seq', COALESCE(MAX(change_id), 1)) FROM public."schema_changes";
SELECT SETVAL('public.state_attributes_attributes_id_seq', COALESCE(MAX(attributes_id), 1)) FROM public."state_attributes";
SELECT SETVAL('public.states_state_id_seq', COALESCE(MAX(state_id), 1)) FROM public."states";
SELECT SETVAL('public.states_meta_metadata_id_seq', COALESCE(MAX(metadata_id), 1)) FROM public."states_meta";
SELECT SETVAL('public.statistics_id_seq', COALESCE(MAX(id), 1)) FROM public."statistics";
SELECT SETVAL('public.statistics_meta_id_seq', COALESCE(MAX(id), 1)) FROM public."statistics_meta";
SELECT SETVAL('public.statistics_runs_run_id_seq', COALESCE(MAX(run_id), 1)) FROM public."statistics_runs";
SELECT SETVAL('public.statistics_short_term_id_seq', COALESCE(MAX(id), 1)) FROM public."statistics_short_term";
Right now, I have another solution to backup my database: Im running another Postgres container on Synology NAS, and using this command on the task scheduler:
It creates a backup every week. Gladly I was never in the situation to backup the database, so would this command make a complete backup that I could restore without a problem in case I have to backup? I get only this warning: pg_dump: warning: there are circular foreign-key constraints on this table: without more information
No, I followed the pgAgent instructions @Expaso posted and used the command /usr/libexec/postgresql15/pg_dump -Fc --username=postgres --dbname=homeassistant --clean --file=/share/db_backup.sql as a step in my pgAgent job, i.e. I’m performing the backup within the container, not from the outside.
Hmm, pgAgent seems to be quite unreliable in terms of marking jobs as done.
Most of the scheduled backups so far were stuck in state ‘r’ (running) and never set to ‘s’ (success), even though the pg_dump process itself worked just fine.
This means that the backup is run exactly once and then never again (since it is still “running”). Have to reset the jobs manually.
Anyone else experiencing this?
