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 home-assistant_v2.db file.

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.

As someone said in a comment, you can actually disable the recorder using a service call 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 rsync or 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
  db_url: !secret psql_string
  db_retry_wait: 15 # Wait 15 seconds before retrying
      - automation
      - updater
      - sensor.weather_*
      - sun.sun # Don't record sun data
      - sensor.last_boot # Comes from 'systemmonitor' sensor platform
      - sensor.date
      - call_service # Don't record service calls

The 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

There are quite a few comments about columns that cannot be found. If you have a database that was updated to a newer versions (of HA), the old columns are still present but unused. However, in a new install they might not be created anymore and thus we get errors about them not being found. Take a look at the data docs of Home Assistant to see which are currently in use. If it isn't shown there, you will probably need to remove it from your SQLite DB as well.

An example is the created column in the events table. 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:

pgloader migrate.sql

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!

Migrate Home Assistant from SQLite to PostgreSQL

For a while I had been using the standard SQLite database for Home Assistant. However after adding more integrations it became very slow. This guide will show you how to migrate from SQLite to PostgreSQL