Home Assistant SQLite to TimescaleDB

timescaledb.md

Migration from SQLite to Timescale DB

1. Prepare local environment
   1. Install pgcli and pgloader
2. Prepare homeassistant environment
   1. Install timescaledb addon (supervisor required)
   2. Configure addon (you have to manually add the listening port in the addons configuration page)
3. Stop homeassistant core with ha core stop (when connected via SSH to your homeassistant instance)
4. Fetch home-assistant_v2.db via SCP to your local machine (which you've prepared in step #1)
5. Execute pgloader to migrate SQLite DB to PostgresSQL
   1. pgloader home-assistant_v2.db postgresql://<user>:<password>@<hostname>/<dbname>
   2. Wait (took 55 Minutes for me on a 1.8 GB sqlite)
6. Apply schema changes as shown below
7. Modify configuration.yaml recorder configuration to point to the postgresql server
8. Re-start homeassistant core ha core start

Postgre SQL schema changes

Postgre SQL needs sequences to generate primary keys for every table. So here are the changes I've applied to make it work.

Disclaimer: I've just copy & pasted it from this guy as I'm not yet familiar with Postgre SQL. If there are better ways, feel free to comment below. This works for me.

--
-- Name: events_event_id_seq; Type: SEQUENCE; Schema: public; Owner: homeassistant
--

CREATE SEQUENCE public.events_event_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

--
-- Name: events_event_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: homeassistant
--

ALTER SEQUENCE public.events_event_id_seq OWNED BY public.events.event_id;

--
-- Name: recorder_runs_run_id_seq; Type: SEQUENCE; Schema: public; Owner: homeassistant
--

CREATE SEQUENCE public.recorder_runs_run_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

--
-- Name: recorder_runs_run_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: homeassistant
--

ALTER SEQUENCE public.recorder_runs_run_id_seq OWNED BY public.recorder_runs.run_id;

--
-- Name: schema_changes_change_id_seq; Type: SEQUENCE; Schema: public; Owner: homeassistant
--

CREATE SEQUENCE public.schema_changes_change_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

--
-- Name: schema_changes_change_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: homeassistant
--

ALTER SEQUENCE public.schema_changes_change_id_seq OWNED BY public.schema_changes.change_id;

--
-- Name: states_state_id_seq; Type: SEQUENCE; Schema: public; Owner: homeassistant
--

CREATE SEQUENCE public.states_state_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

--
-- Name: states_state_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: homeassistant
--

ALTER SEQUENCE public.states_state_id_seq OWNED BY public.states.state_id;


--
-- Name: events event_id; Type: DEFAULT; Schema: public; Owner: homeassistant
--

ALTER TABLE ONLY public.events ALTER COLUMN event_id SET DEFAULT nextval('public.events_event_id_seq'::regclass);


--
-- Name: recorder_runs run_id; Type: DEFAULT; Schema: public; Owner: homeassistant
--

ALTER TABLE ONLY public.recorder_runs ALTER COLUMN run_id SET DEFAULT nextval('public.recorder_runs_run_id_seq'::regclass);


--
-- Name: schema_changes change_id; Type: DEFAULT; Schema: public; Owner: homeassistant
--

ALTER TABLE ONLY public.schema_changes ALTER COLUMN change_id SET DEFAULT nextval('public.schema_changes_change_id_seq'::regclass);


--
-- Name: states state_id; Type: DEFAULT; Schema: public; Owner: homeassistant
--

ALTER TABLE ONLY public.states ALTER COLUMN state_id SET DEFAULT nextval('public.states_state_id_seq'::regclass);

--
-- Name: ix_events_context_id; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_events_context_id ON public.events USING btree (context_id);


--
-- Name: ix_events_context_user_id; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_events_context_user_id ON public.events USING btree (context_user_id);


--
-- Name: ix_events_event_type; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_events_event_type ON public.events USING btree (event_type);


--
-- Name: ix_events_time_fired; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_events_time_fired ON public.events USING btree (time_fired);


--
-- Name: ix_recorder_runs_start_end; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_recorder_runs_start_end ON public.recorder_runs USING btree (start, "end");


--
-- Name: ix_states_context_id; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_states_context_id ON public.states USING btree (context_id);


--
-- Name: ix_states_context_user_id; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_states_context_user_id ON public.states USING btree (context_user_id);


--
-- Name: ix_states_entity_id; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_states_entity_id ON public.states USING btree (entity_id);


--
-- Name: ix_states_entity_id_last_updated; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_states_entity_id_last_updated ON public.states USING btree (entity_id, last_updated);


--
-- Name: ix_states_event_id; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_states_event_id ON public.states USING btree (event_id);


--
-- Name: ix_states_last_updated; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_states_last_updated ON public.states USING btree (last_updated);