Recorder

This integration is by default enabled as dependency of the history integration.

Home Assistant uses SQLAlchemy, which is an Object Relational Mapper (ORM). This makes it possible to use a number of database solutions.

The supported database solutions are:

• MariaDB ≥ 10.3
• MySQL ≥ 8.0
• PostgreSQL ≥ 12
• SQLite ≥ 3.40.1

Although SQLAlchemy supports database solutions in addition to the ones supported by Home Assistant, it will behave differently on different databases, and features relied on by the recorder may work differently, or not at all, in different databases.

The default, and recommended, database engine is SQLite which does not require any configuration. The database is stored in your Home Assistant configuration directory (’/config/’) and is named home-assistant_v2.db.

To change the defaults for the recorder integration in your installation, add the following to your configuration.yamlThe configuration.yaml file is the main configuration file for Home Assistant. It lists the integrations to be loaded and their specific configurations. In some cases, the configuration needs to be edited manually directly in the configuration.yaml file. Most integrations can be configured in the UI. [Learn more] file:

Disk space requirements

A bare minimum requirement is to have at least as much free temporary space available as the size of your database at all times. A table rebuild, repair, or repack may happen at any time, which can result in a copy of the data on disk during the operation. Meeting the bare minimum requirement is essential during a version upgrade, where the schema may change, as this operation almost always requires making a temporary copy of part of the database.

For example, if your database is 1.5 GiB on disk, you must always have at least 1.5 GiB free.

Advanced configuration

# Example configuration.yaml entry
recorder:

Configure filter

By default, no entity will be excluded. To limit which entities are being exposed to recorder, you can use the include and exclude parameters.

# Example filter to include specified domains and exclude specified entities
recorder:
  include:
    domains:
      - alarm_control_panel
      - light
    entity_globs:
      - binary_sensor.*_occupancy
  exclude:
    entities:
      - light.kitchen_light

Filters are applied as follows:

1. No filter
   • All entities included
2. Only includes
   • Entity listed in entities include: include
   • Otherwise, entity matches domain include: include
   • Otherwise, entity matches glob include: include
   • Otherwise: exclude
3. Only excludes
   • Entity listed in exclude: exclude
   • Otherwise, entity matches domain exclude: exclude
   • Otherwise, entity matches glob exclude: exclude
   • Otherwise: include
4. Domain and/or glob includes (may also have excludes)
   • Entity listed in entities include: include
   • Otherwise, entity listed in entities exclude: exclude
   • Otherwise, entity matches glob include: include
   • Otherwise, entity matches glob exclude: exclude
   • Otherwise, entity matches domain include: include
   • Otherwise: exclude
5. Domain and/or glob excludes (no domain and/or glob includes)
   • Entity listed in entities include: include
   • Otherwise, entity listed in exclude: exclude
   • Otherwise, entity matches glob exclude: exclude
   • Otherwise, entity matches domain exclude: exclude
   • Otherwise: include
6. No Domain and/or glob includes or excludes
   • Entity listed in entities include: include
   • Otherwise: exclude

The following characters can be used in entity globs:

* - The asterisk represents zero, one, or multiple characters ? - The question mark represents zero or one character

If you only want to hide events from your logbook, take a look at the logbook integration. But if you have privacy concerns about certain events or want them in neither the history or logbook, you should use the exclude/include options of the recorder integration. That way they aren’t even in your database, you can reduce storage and keep the database small by excluding certain often-logged events (like sensor.last_boot).

Common filtering examples

Defining domains and entities to exclude (i.e. blocklist) is convenient when you are basically happy with the information recorded, but just want to remove some entities or domains.

# Example configuration.yaml entry with exclude
recorder:
  purge_keep_days: 5
  db_url: sqlite:////home/user/.homeassistant/test
  exclude:
    domains:
      - automation
      - update
    entity_globs:
      - sensor.sun*
      - weather.*
    entities:
      - sensor.date
      - sensor.last_boot # Comes from 'systemmonitor' sensor platform
      - sun.sun # Don't record sun data
    event_types:
      - call_service # Don't record actions

Defining domains and entities to record by using the include configuration (i.e. allowlist) is convenient if you have a lot of entities in your system and your exclude lists possibly get very large, so it might be better just to define the entities or domains to record.

# Example configuration.yaml entry with include
recorder:
  include:
    domains:
      - sensor
      - switch
      - media_player

You can also use the include list to define the domains/entities to record, and exclude some of those within the exclude list. This makes sense if you, for instance, include the sensor domain, but want to exclude some specific sensors. Instead of adding every sensor entity to the include entities list just include the sensor domain and exclude the sensor entities you are not interested in.

# Example configuration.yaml entry with include and exclude
recorder:
  include:
    domains:
      - sensor
      - switch
      - media_player
  exclude:
    entities:
      - sensor.last_boot
      - sensor.date
    entity_globs:
      - sensor.weather_*

Actions

Action purge

Perform the action recorder.purge to start a purge task which deletes events and states older than x days, according to keep_days action data. Note that purging will not immediately decrease disk space usage but it will significantly slow down further growth.

Action purge_entities

Perform the action recorder.purge_entities to start a task that purges events and states from the recorder database that match any of the specified entity_id, domains, and entity_globs fields. At least one of the three selection criteria fields must be provided.

Example automation to remove data rows for specific entities

The below automation will remove history for sensor.power_sensor_0 older than 5 days at 04:15:00 every day.

alias: "Purge noisy power sensors"
triggers:
  - trigger: time
    at: "04:15:00"
actions:
  - action: recorder.purge_entities
    data:
      keep_days: 5
      entity_id: sensor.power_sensor_0

Action disable

Perform the action recorder.disable to stop saving events and states to the database.

Action enable

Perform the action recorder.enable to start again saving events and states to the database. This is the opposite of recorder.disable.

Handling disk corruption and hardware failures

When using SQLite, if the system encounters unrecoverable disk corruption, it will move the database aside and create a new database to keep the system online. In this case, having at least 2.5x the database size available in free disk space is essential. Starting a new database is the system’s last resort recovery option and is usually caused by failing flash storage, an inadequate power supply, an unclean shutdown, or another hardware failure.

In this event, it may be possible to recover the old database by following the SQLite recovery guide.

Custom database engines

Here are examples to use with the db_url configuration option.

Database startup

If you are running a database server instance on the same server as Home Assistant then you must ensure that this action starts before Home Assistant. For a Linux instance running Systemd (Raspberry Pi, Debian, Ubuntu and others) you should edit the service file. To help facilitate this, db_max_retry and db_retry_wait variables have been added to ensure the recorder retries the connection to your database enough times, for your database to start up.

sudo nano /etc/systemd/system/[email protected]

and add the action for the database, for example, PostgreSQL:

[Unit]
Description=Home Assistant
After=network.target postgresql.service

Save the file then reload systemctl:

sudo systemctl daemon-reload

Installation notes

Not all Python bindings for the chosen database engine can be installed directly. This section contains additional details that should help you to get it working.

MariaDB and MySQL

Make sure the default character set of your database server is set to utf8mb4 (see MariaDB documentation). If you are in a virtual environment, don’t forget to activate it before installing the mysqlclient Python package described below.

pi@homeassistant:~ $ sudo -u homeassistant -H -s
homeassistant@homeassistant:~$ source /srv/homeassistant/bin/activate
(homeassistant) homeassistant@homeassistant:~$ pip3 install mysqlclient

For MariaDB you may have to install a few dependencies. If you’re using MariaDB 10.3, the package libmariadb-dev-compat must also be installed. Please install the correct packages based on your MariaDB version.

On the Python side we use the mysqlclient:

sudo apt-get install libmariadbclient-dev libssl-dev
pip3 install mysqlclient

For MySQL you may have to install a few dependencies. You can choose between pymysql and mysqlclient:

sudo apt-get install default-libmysqlclient-dev libssl-dev
pip3 install mysqlclient

After installing the dependencies, it is required to create the database manually. During the startup, Home Assistant will look for the database specified in the db_url. If the database doesn’t exist, it will not automatically create it for you.

The database engine must be InnoDB as MyIASM is not supported.

SET GLOBAL default_storage_engine = 'InnoDB';
CREATE DATABASE DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Where DB_NAME is the name of your database

Once Home Assistant finds the database, with the right level of permissions, all the required tables will then be automatically created and the data will be populated accordingly.

PostgreSQL

Create the PostgreSQL database with utf8 encoding. The PostgreSQL default encoding is SQL_ASCII. From the postgres user account;

createdb -E utf8 DB_NAME

Where DB_NAME is the name of your database

If the Database in use is not utf8, adding ?client_encoding=utf8 to the db_url may solve any issue.

For PostgreSQL you may have to install a few dependencies:

sudo apt-get install postgresql-server-dev-X.Y
pip3 install psycopg2

For using Unix Sockets, first create your user from the postgres user account;

createuser USER_NAME

Where USER_NAME is the name of the user running the Home Assistant instance (see securing your installation).

Then add the following line to your pg_hba.conf:

local DB_NAME USER_NAME peer

Where DB_NAME is the name of your database and USER_NAME is the name of the user running the Home Assistant instance (see securing your installation).

Reload the PostgreSQL configuration after that:

$ sudo -i -u postgres psql -c "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

A service restart will work as well.