I’ve been using HA for a few years now just using the standard SQlite DB. I’m running a second instance in my RV now and decided to use Postgres instead since I’m more familiar with it. I have both HA and Postgres running as Docker containers.
I connected to the Postgres DB tonight to see if I could manually add some missing data. But what I’m seeing doesn’t make sense. When I query the states table, the entity_id field is blank for every record.
homeassistant=# select entity_id, count(*) from states group by entity_id;
entity_id | count
-----------+---------
| 1576471
(1 row)
Similarly, when I describe the table, the entity_id field is a 1 byte character type.
homeassistant=# \d states
Table "public.states"
Column | Type | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+----------------------------------
state_id | integer | | not null | generated by default as identity
entity_id | character(1) | | |
state | character varying(255) | | |
attributes | character(1) | | |
event_id | smallint | | |
last_changed | timestamp with time zone | | |
last_changed_ts | double precision | | |
last_updated | timestamp with time zone | | |
last_updated_ts | double precision | | |
old_state_id | integer | | |
attributes_id | integer | | |
context_id | character(1) | | |
context_user_id | character(1) | | |
context_parent_id | character(1) | | |
origin_idx | smallint | | |
context_id_bin | bytea | | |
context_user_id_bin | bytea | | |
context_parent_id_bin | bytea | | |
metadata_id | integer | | |
I’m not sure why the data is being displayed this way. Home Assistant seems to be acting normal and I don’t see any obvious errors in the HA or Postgres containers. I feel like I’m missing something obvious.
HA also displays all historic data normally so it doesn’t seem like the DB has become corrupted.
recorder conf:
recorder:
db_url: postgresql://homeassistant:$DB_PASS@localhost:5433/homeassistant
docker compose
version: '3.3'
services:
homeassistant:
restart: always
environment:
- TZ=America/New_York
volumes:
- ./config:/config
network_mode: host
image: homeassistant/home-assistant:2023.7
postgres:
image: postgres:15.3
restart: always
env_file:
- .env
environment:
POSTGRES_DB: homeassistant
POSTGRES_USER: homeassistant
POSTGRES_PASSWORD: ${DB_PASS}
volumes:
- pgdata:/var/lib/postgresql/data
ports:
- 5433:5432
volumes:
pgdata:
