Hi all,
Wall of crit text unfortunately. I try to be as complete as possible, but it’s a lengthy piece…
I’ve been using Home Assistant for some 4 years now, running in docker since my inception. Started out on Pi, now on a miniPC with i3 CPU, with a dozen other containers.
Last January I decided to move the data from sqlite into an timescaledb database to increase some of the responsivenesses (in especially the energy board, switching month-to-month or year-to-year became sluggish, so much better now) and see if I could aggregate some other stuff I’m running on the same machine.
As per my “best practices” I remove containers when I stop them, and build new ones when I “start” them. All containers have volume mounts, all important data is persisted (except .psql_history until a few minutes ago…)
I start my container as follows:
#!/usr/bin/env bash
IP=$(ip -4 addr show enp0s31f6 | grep -oP '(?<=inet\s)\d+(\.\d+){3}')
PIHOLE_BASE="${PIHOLE_BASE:-$(pwd)}"
DOCKERTAG='2025.7'
docker run -d \
--name homeassistant \
--privileged \
--restart=unless-stopped \
--device=/dev/ttyACM0:/dev/ttySonoffZigbeeEmber \
-e TZ='Europe/Amsterdam' \
-p ${IP}:8123:8123 \
-v /path/to/homeassistant/config:/config \
--net=host \
homeassistant/home-assistant:${DOCKERTAG}
with the following config for the recorder:
recorder:
auto_purge: false
purge_keep_days: 4000
db_url: !secret timescaledb_psql_string
db_retry_wait: 15
and secret
timescaledb_psql_string: "postgresql://homeassistant:[email protected]:15432/homeassistant_db"
I start the timescaledb containter as follows:
#!/usr/bin/env bash
IP=$(ip -4 addr show enp0s31f6 | grep -oP '(?<=inet\s)\d+(\.\d+){3}')
PIHOLE_BASE="${PIHOLE_BASE:-$(pwd)}"
DOCKERTAG='pg17'
docker run -d \
--name timescaledb \
--restart=unless-stopped \
-e TZ='Europe/Amsterdam' \
-e PSQL_HISTORY='/home/postgres/pgdata/data/.psql_history' \
-p ${IP}:15432:5432 \
-e POSTGRES_PASSWORD=yadayada \
-v /path/to/timescaledb/data:/home/postgres/pgdata/data \
-v /path/to/timescaledb/import:/import \
timescale/timescaledb-ha:${DOCKERTAG}
I like to believe that I did the migration okay-ish, I made notes of all steps which I can provide if need be, but I’m running into the following issue(s):
- Every start of the container I see logging that schemas have been updated:
2025-09-06 17:35:33.099 WARNING (Recorder) [homeassistant.components.recorder.migration] The database is about to upgrade from schema version 47 to 50
2025-09-06 17:35:33.099 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrading recorder db schema to version 48
2025-09-06 17:35:33.132 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 48 done
2025-09-06 17:35:33.132 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrading recorder db schema to version 49
2025-09-06 17:35:33.132 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns mean_type to table statistics_meta. Note: this may take several minutes on large databases and slow machines. Please be patient!
2025-09-06 17:35:33.142 WARNING (Recorder) [homeassistant.components.recorder.migration] Column mean_type already exists on statistics_meta, continuing
2025-09-06 17:35:33.143 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns mean_weight to table statistics. Note: this may take several minutes on large databases and slow machines. Please be patient!
2025-09-06 17:35:33.146 WARNING (Recorder) [homeassistant.components.recorder.migration] Column mean_weight already exists on statistics, continuing
2025-09-06 17:35:33.147 WARNING (Recorder) [homeassistant.components.recorder.migration] Adding columns mean_weight to table statistics_short_term. Note: this may take several minutes on large databases and slow machines. Please be patient!
2025-09-06 17:35:33.149 WARNING (Recorder) [homeassistant.components.recorder.migration] Column mean_weight already exists on statistics_short_term, continuing
2025-09-06 17:35:33.212 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 49 done
2025-09-06 17:35:33.212 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrading recorder db schema to version 50
2025-09-06 17:35:33.261 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 50 done
but timescaledb says (rightfully so I suppose)
2025-09-06 17:25:53.676 CEST [1] LOG: starting PostgreSQL 17.6 (Ubuntu 17.6-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
025-09-06 17:35:33.133 CEST [148] ERROR: column "mean_type" of relation "statistics_meta" already exists
2025-09-06 17:35:33.133 CEST [148] STATEMENT: ALTER TABLE statistics_meta ADD mean_type SMALLINT NOT NULL DEFAULT 0
2025-09-06 17:35:33.141 CEST [148] ERROR: column "mean_type" of relation "statistics_meta" already exists
2025-09-06 17:35:33.141 CEST [148] STATEMENT: ALTER TABLE statistics_meta ADD mean_type SMALLINT NOT NULL DEFAULT 0
2025-09-06 17:35:33.144 CEST [148] ERROR: column "mean_weight" of relation "statistics" already exists
2025-09-06 17:35:33.144 CEST [148] STATEMENT: ALTER TABLE statistics ADD mean_weight DOUBLE PRECISION
2025-09-06 17:35:33.146 CEST [148] ERROR: column "mean_weight" of relation "statistics" already exists
2025-09-06 17:35:33.146 CEST [148] STATEMENT: ALTER TABLE statistics ADD mean_weight DOUBLE PRECISION
2025-09-06 17:35:33.147 CEST [148] ERROR: column "mean_weight" of relation "statistics_short_term" already exists
2025-09-06 17:35:33.147 CEST [148] STATEMENT: ALTER TABLE statistics_short_term ADD mean_weight DOUBLE PRECISION
2025-09-06 17:35:33.149 CEST [148] ERROR: column "mean_weight" of relation "statistics_short_term" already exists
2025-09-06 17:35:33.149 CEST [148] STATEMENT: ALTER TABLE statistics_short_term ADD mean_weight DOUBLE PRECISION
so it’s giving HA the run-around.
- After x hours/days my energy dash stops working, everything is empty including historical data. I start seeing
Unique constrainterrors on mostlystatistics_short_termtable along the lines of
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "statistics_short_term_pkey"
DETAIL: Key (id)=(11431561) already exists.
If I bring down the home assistant container and follow either of these steps we’re okay again.
SELECT CURRVAL(PG_GET_SERIAL_SEQUENCE('"statistics_short_term"', 'id')) AS "Current Value", MAX("id") AS "Max Value" FROM "statistics_short_term";
SELECT CURRVAL(PG_GET_SERIAL_SEQUENCE('"statistics"', 'id')) AS "Current Value", MAX("id") AS "Max Value" FROM "statistics";
SELECT setval(pg_get_serial_sequence('statistics_short_term', 'id'), (SELECT MAX(id) FROM statistics_short_term) + 1);
Until the next restart, which may or may not be a version bump of the HA image.
Any insights would be very much appreciated!!
For completeness' sake, I executed the migration based on these docs:
https://www.redpill-linpro.com/techblog/2023/03/21/migrating-home-assistnt-to-postgresql.html
https://community.home-assistant.io/t/home-assistant-add-on-postgresql-timescaledb/198176/215?page=9
https://docs.timescale.com/self-hosted/latest/install/installation-docker/
https://stackoverflow.com/questions/8442147/how-to-delete-or-add-column-in-sqlite