I wanted to switch my new Home Assistant (HA) installation to write data to PostgreSQL instead of SQLite for a variety of reasons. Here’s how I did it.

Here’s why I decided to switch:

  • Resilience. If you’re running Home Assistant on a Raspberry Pi’s SD card, the constant churn of history updates will eventually destroy the card. The more entities you have, the faster HA will grind your SD card to failure. Writing all that to another server that is writing to a real SSD or spinning disk eliminates that problem.
  • Convenience. I can back up the postgres database without having to stop HA. I don’t even have to run the backup on the HA server.
  • Speed. Using a real database will speed up history display, especially once you have a large number of entities.

Pre-requisites

Note - I did this on a fresh server with no history I wanted to preserve. What I’m describing here will discard all your old history data. If you do have history you want to preserve, there’s a forum post here that explains how to load your history from SQLite and into postgres with pgloader.

A PostgreSQL server. I recommend that you configure your router to assign your server a static IP address so when you reboot it it doesn’t get a different IP from the DHCP pool and force you to update your HA server’s configuration.yml.

Setting up postgres

Here’s an example docker-compose.yaml file that will start postgres for you. Rather than use a docker volume and then have to save and restore that, this configuration will mount /path/to/postgres/data into the container so you can safely destroy and recreate your docker environment without having to restore your database from a backup.

version: '3'
services:
  postgres:
    container_name: postgres
    image: postgres:14.5
    restart: always
    network_mode: host
    ports:
      - "5234:5234"
    environment:
      POSTGRES_USER: postgresadmin
      POSTGRES_PASSWORD: <redacted>
    volumes:
      - /path/to/postgres/data:/var/lib/postgresql/data
      - /etc/localtime:/etc/localtime:ro

The first time you run this image, it’ll generate the database files, create a user with admin privileges named POSTGRES_USER with password POSTGRES_PASSWORD. Now that it’s running, we should create a user just for Home Assistant.

I set this up on one of my Odroid HC2s so I could keep the data directory on the hard drive there for ease of backup.

Updating Home Assistant to use PostgreSQL

Now that the postgres server is running, we’re going to use psql (the postgres command line client) to set up a Home Assistant user and database. You can install psql on your machine, but I prefer to use a container.

Run docker run -it --rm postgres:14.5 bash to get a shell running inside a postgres container. It’ll have the tools you need to create an account for your Home Assistant instance.

Here’s the commands you’re going to need:

  1. psql
  2. CREATE USER homeassistant WITH PASSWORD 'yourHomeAsssistantPassword';
  3. CREATE DATABASE homeassistant_db WITH OWNER homeassistant ENCODING 'utf8' TEMPLATE template0;

Now that the postgres server is set up, you can configure your HA server to use it instead of SQLite.

Here’s a snippet from my configuration.yaml

# Database
recorder:
  db_url: !secret psql_connector_string
  db_retry_wait: 10 # Wait 10 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

To save space, I’ve disabled storing changes to sun.sun, sensor.date and sensor.last_boot, along with weather information and calls to services. Tune yours as you see fit.

I’m using a secret for the db_url, and here’s a redacted example from my secrets.yaml file showing the proper format:

psql_connector_string: "postgresql://DATABSE_USERNAME:DATABASE_PASSWORD@DNSNAME_OR_IP_OF_POSTGRES_SERVER/DATABASE_NAME"

So in my case, the redacted string is postgresql://hassuser:hasspassword@postgres.example.com/homeassistant_db

Now that you’ve updated the server configuration, confirm that you don’t have any typos and your configuration is valid by going to http://yourHA:8123/developer-tools/yaml and clicking CHECK CONFIGURATION. If it reports it valid, you can safely restart HA and you’ll be storing all your history data in Postgres.

Backing Up Your Database

Now that we’re writing data to postgres, time to take advantage of not having to shut it down for backups and start backing things up.

I wrote a simple shell script for backing up your postgres database, ha-postgresql-backup. It has reasonable default settings, which you can override for your environment by setting environment variables.

  • COMPRESSOR - If you set this, also set EXTENSION. Defaults looking for bzip2 and gzip, and will use bzip2 if both are found.
  • DUMP_D - What directory to write the dump file to. If you don’t set this, it will use the current directory
  • HASS_D - What postgres database to back up. Defaults to homeassistant_db
  • PG_PASSWORD - The ha user’s password
  • PG_SERVER - What server to connect to
  • PG_USERNAME - Used to log into your postgres server. Defaults to homeassistant. You should use the same username & password your Home Assistant is using.
  • POSTGRESQL_IMAGE - What docker image to use. Defaults to postgres:14.5

Example usage: PG_PASSWORD=your_ha_pg_password PG_USER=your_ha_pg_user PG_SERVER=pg.example.com HASS_DB=homeassistant_db DUMP_D=/path/to/directory hass-postgresql-backup

You don’t need to stop Home Assistant to pull a backup, and you also don’t need to run it on the same server you’re running the postgres server on.

Update

@myoung34 has shared their Argo Workflow for backing up Postgres