Updating home assistant to postgres

HomeAssistant.md

1. BACKUP YOUR HOME ASSISTANT!
2. Grab the SQLite database somewhere
3. Install pgloader ** Create postgres database:

create database ha_live;

CREATE USER ha_live WITH ENCRYPTED PASSWORD '<password>';
GRANT ALL PRIVILEGES ON DATABASE ha_live TO ha_live;
GRANT ALL  ON DATABASE ha_live TO ha_live;

4. run something like:

pgloader /mnt/c/temp/home-assistant_v2.db pgsql://ha_postgres_user:ha_postgres_password@postgres_host/postgres_db

That will convert your SQLite database to postgres, my 250Mb file took about 20s running on Ubuntu under WSL (the postgres host was remote and on a low spec machine running in docker) 5. The database will not be quite correct, some of the columns will be the wrong type and sequences will need to be reset, so run:

alter table public.event_data
    alter column data_id add generated by default as identity;
SELECT pg_catalog.setval(pg_get_serial_sequence('event_data', 'data_id'), MAX(data_id)) FROM event_data;
    
alter table public.event_types
    alter column event_type_id add generated by default as identity;
SELECT pg_catalog.setval(pg_get_serial_sequence('event_types', 'event_type_id'), MAX(event_type_id)) FROM event_types;

alter table public.events
    alter column event_id add generated by default as identity;
SELECT pg_catalog.setval(pg_get_serial_sequence('events', 'event_id'), MAX(event_id)) FROM events;

alter table public.events
    alter column time_fired type timestamptz using time_fired::timestamptz;

alter table public.recorder_runs
    alter column run_id add generated by default as identity;
SELECT pg_catalog.setval(pg_get_serial_sequence('recorder_runs', 'run_id'), MAX(run_id)) FROM recorder_runs;

alter table public.schema_changes
    alter column change_id add generated by default as identity;
SELECT pg_catalog.setval(pg_get_serial_sequence('schema_changes', 'change_id'), MAX(change_id)) FROM schema_changes;

alter table public.state_attributes
    alter column attributes_id add generated by default as identity;
SELECT pg_catalog.setval(pg_get_serial_sequence('state_attributes', 'attributes_id'), MAX(attributes_id)) FROM state_attributes;

alter table public.states
    alter column state_id add generated by default as identity;
SELECT pg_catalog.setval(pg_get_serial_sequence('states', 'state_id'), MAX(state_id)) FROM states;
alter table public.states
    alter column last_updated type timestamptz using last_updated::timestamptz,
    alter column last_changed type timestamptz using last_changed::timestamptz
;

alter table public.states_meta
    alter column metadata_id add generated by default as identity;
SELECT pg_catalog.setval(pg_get_serial_sequence('states_meta', 'metadata_id'), MAX(metadata_id)) FROM states_meta;

alter table public.statistics
    alter column id add generated by default as identity;
SELECT pg_catalog.setval(pg_get_serial_sequence('statistics', 'id'), MAX(id)) FROM statistics;
alter table public.statistics
    alter column created type timestamptz using created::timestamptz,
    alter column start type timestamptz using start::timestamptz,
    alter column last_reset type timestamptz using last_reset::timestamptz
;

alter table public.statistics_meta
    alter column id add generated by default as identity;
SELECT pg_catalog.setval(pg_get_serial_sequence('statistics_meta', 'id'), MAX(id)) FROM statistics_meta;

alter table public.statistics_runs
    alter column run_id add generated by default as identity;
SELECT pg_catalog.setval(pg_get_serial_sequence('statistics_runs', 'run_id'), MAX(run_id)) FROM statistics_runs;

alter table public.statistics_short_term
    alter column id add generated by default as identity;
SELECT pg_catalog.setval(pg_get_serial_sequence('statistics_short_term', 'id'), MAX(id)) FROM statistics_short_term;

alter table public.statistics_short_term
    alter column start type timestamptz using start::timestamptz,
    alter column last_reset type timestamptz using last_reset::timestamptz

6. Restart HomeAssistant.
7. Update the configuration.yaml file with:

recorder:
  db_url: postgresql://ha_postgres_user:ha_postgres_password@postgres_host/postgres_db

8. Restart HomeAssistant again
9. You should now be logging all states to postgres! Rejoice in the speed that your history graphs display!