I expect you already have a PostgreSQL database running somewhere (and know how it works). If you do not have this, or do not have a separate server for your Postgres, you can take a look at this github.com/expaso/hassos-addon-timescaledb addon which installs a PostgreSQL server with TimescaleDB for you.
While this worked for me, it might not work for you. If you encounter any issues check out the comments below.
Step 1: Retrieve the SQLite database
The first thing you will need to do is to retrieve the SQLite database. This is the
For consistency I wanted to make sure that I did not have Home Assistant running while I was retrieving the database. So I stopped Home Assistant and retrieved the database. However since you need to have Core running to use SMB or SSH it isn't really straightforward.
recorder.disable. This stops saving any events and states to the database. After which you can download the database without stopping HA, I would recommend you try this first since it is much easier. I will leave the options below in case that does not work for you.
Docs: Home Assistant Recorder
There are multiple ways to do this:
- Get the whole disk content of the OS. If you are using a Raspberry Pi this is probably the easiest and fastest way, just insert the SD into your computer. If you are using a VM, create a backup of the disk and then retrieve the disk content.
- Do this step after Step 2 & 3. What I mean is, you first setup the database and configure Home Assistant to use the database. This will only remove the events and history. Your users, config, etc. will not be changed since this isn't stored in the SQLite database. After that you can simply download the database file using SSH or SMB and thus ensure consistency.
- Install a tool on the OS itself, like
netcat. This is will allow you to retrieve the database from the OS itself. I decided against this because I did not want to install other software on the OS.
I went for the first option, I downloaded a backup, extracted it and then retrieved the database file. Now store the database somewhere temporary. I used
/tmp/home-assistant_v2.db (on Linux).
Step 2: Setup Postgres
This is actually pretty straight forward if you already have a PostgreSQL server running. You can find instructions on how to setup PostgreSQL on your OS here. Make sure you install a version that is supported by Home Assistant, you can find a list of supported versions here.
sudo -u postgres psql
Now create a Postgres user:
CREATE USER homeassistant WITH PASSWORD 'yourpassword';
And now create a database. Make sure the Database is in UTF-8 format!
CREATE DATABASE homeassistant_db WITH OWNER homeassistant ENCODING 'utf8' TEMPLATE template0;
Once that is done, make sure Postgres allows connections from the Home Assistant server and your PC. This is done by adding the following to the
/etc/postgresql/VERSION/main/pg_hba.conf file (obviously you will need to change the IP address):
host homeassistant_db homeassistant 192.168.0.xxx/32 md5 # Home Assistant server host homeassistant_db homeassistant 192.168.0.xxx/32 md5 # Your PC
Now you will only need to reload PostgreSQL.
sudo systemctl reload postgresql
Step 3: Let Home Assistant create the tables
Once you have created the empty database, you can let Home Assistant create the tables. It is possible to also restore this from the SQLite database but I wouldn't recommend it. My attempt resulted in schema errors. It is easier to let it create the databases tables itself and then copy over the data.
To do this you will need to start Home Assistant again and edit the
configuration.yaml file. Now you can add the custom `recorder` component like this:
# Database recorder: db_url: !secret psql_string db_retry_wait: 15 # Wait 15 seconds before retrying exclude: domains: - automation - updater entity_globs: - sensor.weather_* entities: - sun.sun # Don't record sun data - sensor.last_boot # Comes from 'systemmonitor' sensor platform - sensor.date event_types: - call_service # Don't record service calls
exclude section is used to exclude certain data from being recorded. You can find more information about this in the Home Assistant documentation.
You may have also noted that I am using a
!secret to hide the password in the configuration file. Thus you will need to add the password to the
secrets.yaml file or add it to the configuration (if you want).
# Postgres connection string psql_string: "postgresql://homeassistant:PASSWORD@192.168.0.XXX/homeassistant_db"
Now restart Home Assistant, once it is running you should see your normal Home Assistant UI but the history will be empty. You can now stop Home Assistant again.
Step 4: Migrate the database
An example is the
createdcolumn in the
eventstable. This column should be removed.
ALTER TABLE events&
DROP COLUMN created
The second to last step is to migrate the database. I used the tool pgLoader to accomplish this. (On Debian-based install via:
apt install pgloader)
Create a file
migrate.sql with the following content. Obviously you will need to change the sqlite database path and the postgres connection string. (Some may have to remove the quotes for it to work)
load database from 'sqlite:///tmp/home-assistant_v2.db' into 'postgresql://homeassistant:PASSWORD@192.168.0.XXX/homeassistant_db' with data only, drop indexes, reset sequences, truncate, batch rows = 1000 SET work_mem to '32 MB', maintenance_work_mem to '64 MB';
Once you have created the file, run the following command:
Step 5: Start Home Assistant
You should now be able to start Home Assistant again and all your data should be migrated. You will notice that loading history data is considerably faster than the SQLite database.
Hopefully this tutorial has helped you migrate your data from SQLite to PostgreSQL. Let me know in the comments if you experience any issues!