Migrate to PostgreSQL

Configuration
20

Posting to let people know that this technique still works, at least in 2024.5.5 (yeah already outdated somewhat, but as I’m not using cloud integrations that are likely to break, I prefer to use the stable HA package of my distro).

Saving you some reading from above, following are the big steps are. Adapt to your setup, notably the database name (hass for me).

  1. Back your data up.
  2. Set up your PostgreSQL database, and configure HA to connect to it
  3. Restart HA on this configuration, you can stop it as soon as it created the tables in the database (with psql open on your database, run \dt until it stabilizes to a dozen rows)
  4. Run pgloader myconfig.conf. Here’s what my configuration file looks like:
load database
  from sqlite:///var/lib/hass/home-assistant_v2.db
  into postgresql:///hass
with data only, drop indexes, reset sequences, truncate, batch rows = 1000, prefetch rows = 1000
SET work_mem to '32 MB', maintenance_work_mem to '64 MB';
Explaination
  • data only and truncate so we use the structure Home Assistant created (allegedly otherwise it will fail, didn’t verify)
  • drop indexes so they get recreated (not exactly sure why, but it’s harmless and might prevent a few edge cases)
  • reset sequnces see below
  • batch rows, prefetch rows, *work_mem so the operation performs well enough it doesn’t run out of heap or something.
  • No set timezone to ‘UTC’ as Imroy suggested It wasn’t necessary in my case, but maybe it would be in yours?

You will get a few warnings but those are supposedly expected.
It will also take a while, the data import goes fast but not the recreation of the indexes.

  1. Fix the sequence ids
pg_dump hass --schema-only | grep 'ADD GENERATED BY DEFAULT AS IDENTITY' | awk '{ print "SELECT setval('\''" $3 "_" $6 "_seq'\'', (SELECT MAX("$6") FROM "$3"));" }' | psql hass
Explaination

For most tables there’s a sequence counter which determines the next id a row in that table will get. We reset it with reset sequences but even without it pgloader seems to set it to a too low value:

hass=# SELECT MAX(state_id) FROM states;
    max
-----------
 132378632
hass=# SELECT last_value FROM states_state_id_seq;
 last_value
------------
       2711

This causes issues like complaining that columns do not support double precision (they do, but due to duplicate id HA will not get back the row it attempted to write to test this), but also the data written to recorder will not show up (despite being written. I have not found a way to salvage data written at the wrong place, duplicates ids and foreign keys not written as such makes this hard) for a long time (until you wrote as much data as you did before the migration).

The awk magic above resets the sequence counter to a value that makes sense.
This is an alternative to the solution proposed by tobixen, which I think is cleaner.

  1. Start Home Assistant, make sure your historical data is there, and that new data gets written as well (even after you reload the history page).

(I have not phrased those as answers but the explainations it potentially explains the issues @FlameBoy and @Nuuki have been going through)

5 Likes