Migrate home-assistant from SQLite to PostgreSQL

Migrate home-assistant from SQLite to PostgreSQL#

Problem#

I want to migrate home-assistant from SQLite to PostgreSQL.

Solution#

The migration involves the following steps:

Create a PostgreSQL database following PostgreSQL - Create UTF-8 Database and PostgreSQL - Create Role for a Database.
Configure home-assistant to use PostgreSQL.
Boot home-assistant to allow it to create the tables on PostgreSQL.
Dump the database (schema-only) and re-create the database and load the schema into it.
Workaround issue where sqlite store boolean as integer and postgres stores it as boolean.
Have pgloader migrate the database.
Undo the workaround.
Reset sequences so new records is correct.

Configure home-assistant to connect to PostgreSQL#

Follow this document to configure the recorder to connect to PostgreSQL.

configuration.yaml

recorder:
  db_url: !secret recorder_db_url

secrets.yaml

recorder_db_url: "postgresql://home-assistant:<omitted>@pg-cluster.domain.tld/home-assistant"

Dump the database to re-create it#

Dump it with this command while logged in to the Postgres host as the postgres user.

pg_dump -U postgres -s "home-assistant" > /tmp/ha_schema.sql

And then load it with the following command (after having dropping and creating the database, see PostgreSQL - Create UTF-8 Database)

psql -U postgres -d "home-assistant" -f /tmp/ha_schema.sql

Workaround the Integer vs Boolean issue.#

The following columns are boolean but should be integers:

recorder_runs.closed_incorrect
statistics_meta.has_mean
statistics_meta.has_sum

To workaround it, first apply this change:

psql -U postgres -d "home-assistant" -c "ALTER TABLE recorder_runs ALTER COLUMN closed_incorrect TYPE smallint USING closed_incorrect::integer;"
psql -U postgres -d "home-assistant" -c "ALTER TABLE statistics_meta ALTER COLUMN has_mean TYPE smallint USING has_mean::integer;"
psql -U postgres -d "home-assistant" -c "ALTER TABLE statistics_meta ALTER COLUMN has_sum TYPE smallint USING has_sum::integer;"

After running pgloader, now restore the columns back to being booleans:

psql -U postgres -d "home-assistant" <<EOF
ALTER TABLE recorder_runs ALTER COLUMN closed_incorrect TYPE boolean USING (closed_incorrect <> 0);
ALTER TABLE statistics_meta ALTER COLUMN has_mean TYPE boolean USING (has_mean <> 0);
ALTER TABLE statistics_meta ALTER COLUMN has_sum TYPE boolean USING (has_sum <> 0);
EOF

Reset the sequences#

psql -U postgres -d "home-assistant" <<EOF
-- Reset Sequences (Crucial for HA Startup)
SELECT setval(pg_get_serial_sequence('events', 'event_id'), COALESCE(MAX(event_id), 1)) FROM events;
SELECT setval(pg_get_serial_sequence('states', 'state_id'), COALESCE(MAX(state_id), 1)) FROM states;
SELECT setval(pg_get_serial_sequence('recorder_runs', 'run_id'), COALESCE(MAX(run_id), 1)) FROM recorder_runs;
SELECT setval(pg_get_serial_sequence('statistics', 'id'), COALESCE(MAX(id), 1)) FROM statistics;
SELECT setval(pg_get_serial_sequence('statistics_meta', 'id'), COALESCE(MAX(id), 1)) FROM statistics_meta;
SELECT setval(pg_get_serial_sequence('statistics_runs', 'run_id'), COALESCE(MAX(run_id), 1)) FROM statistics_runs;
SELECT setval(pg_get_serial_sequence('statistics_short_term', 'id'), COALESCE(MAX(id), 1)) FROM statistics_short_term;
SELECT setval(pg_get_serial_sequence('states_meta', 'metadata_id'), COALESCE(MAX(metadata_id), 1)) FROM states_meta;
SELECT setval(pg_get_serial_sequence('event_data', 'data_id'), COALESCE(MAX(data_id), 1)) FROM event_data;
SELECT setval(pg_get_serial_sequence('event_types', 'event_type_id'), COALESCE(MAX(event_type_id), 1)) FROM event_types;
SELECT setval(pg_get_serial_sequence('state_attributes', 'attributes_id'), COALESCE(MAX(attributes_id), 1)) FROM state_attributes;
SELECT setval(pg_get_serial_sequence('schema_changes', 'change_id'), COALESCE(MAX(change_id), 1)) FROM schema_changes;
EOF