# From SQLite to MariaDB on Proxmox — a complete migration guide for serious setups

Installation
1

From SQLite to MariaDB on Proxmox — a complete migration guide for serious setups

Category: Installation & configuration · HA 2026.4.2 · Proxmox 9.1 (Trixie)
Tags: mariadb proxmox recorder performance sqlite migration

If you run Home Assistant with a significant number of integrations and devices, SQLite will eventually let you down. This post documents a complete, production-tested migration to MariaDB — including every pitfall encountered along the way — so you can do it cleanly in one shot.

Before that I was running HA instance on a Win 11 miniPC / Virtual Box, certainly not the best setup. Proxmox is definitely the way to go.

I decided to move forward after reading this post.

My setup
Official integrations 35
HACS integrations 13
Devices 524
Entities 3,082

Infrastructure: HAOS 15.2 as a VM (UEFI, VirtIO SCSI, 4 cores, 6 GB RAM) on Proxmox 9.1 running on an Intel NUC i3 8109U. SQLite database: 1.9 GB, accumulated over several years.

Why leave SQLite?

SQLite is embedded, single-writer, and relies on WAL for concurrency. Under the continuous write pressure of a busy HA instance (states, events, statistics), it is prone to corruption — especially when the host crashes or the VM is killed without a clean shutdown.

I experienced recently several corruptions: after a SIGSEGV during a HAOS upgrade, after orphaned statistics cleanup. Recovery involved .recover, manually deleting .wal/.shm files, and hoping for the best. MariaDB’s InnoDB engine has a redo log that automatically replays incomplete transactions after a crash — this class of failure simply does not exist.

The performance improvement after migration is dramatic: UI responsiveness, history loading, and dashboard rendering are all noticeably faster.

Architecture choice: official addon vs. dedicated LXC

After evaluating both options, the official MariaDB addon is the right choice for most setups:

  • Two-click install, automatic updates
  • Included in HAOS snapshots
  • Fully configurable via mariadb_server_args
  • No extra infrastructure to maintain

A dedicated LXC only makes sense if you plan to set up MariaDB replication to a secondary node.

:warning: Pitfalls — read this before starting

:x: innodb_flush_method=O_DIRECT

Commonly recommended for dedicated servers, but incompatible with the overlay filesystem of HAOS Docker containers. Causes:

ERROR 1180 (HY000): Got error 1 "Operation not permitted" during COMMIT

Do not use it. It is intentionally absent from the configuration below.

:x: mysqldump for import

A SQL dump wraps large tables (statistics, statistics_short_term) in a single LOCK TABLES / INSERT transaction. InnoDB cannot absorb it inside the HAOS container. Same ERROR 1180. Use sqlite3mysql directly instead.

:x: Running sqlite3mysql from a remote machine

If you run the migration tool from a PC connecting over the LAN to the addon’s exposed port 3306, throughput is ~165 rows/sec. With 8M rows, that’s 13+ hours. Run it from a temporary Debian LXC on the same Proxmox host instead — throughput jumps to ~2300 rows/sec via the virtual bridge.

:warning: statistics table behavior

sqlite3mysql loads each chunk into RAM before inserting. The progress bar exists but appears frozen for the first few minutes — this is normal. The first chunks are slow while InnoDB warms up its buffer pool. Use --chunk 50000 for the statistics pass — it is the right balance between memory usage and speed on a 4 GB LXC.

What you need

  • HAOS running on Proxmox (any recent version)
  • A temporary Debian 12 LXC on the same Proxmox host — 4 GB RAM, 1 vCPU, 10 GB disk
  • MariaDB addon installed (not started yet) with the configuration below

LXC post-install

apt update && apt install -y python3-pip python3-dev \
  default-libmysqlclient-dev default-mysql-client \
  build-essential sqlite3

pip3 install sqlite3-to-mysql --break-system-packages

Addon configuration

databases:
  - homeassistant
logins:
  - username: homeassistant
    password: YOUR_PASSWORD
rights:
  - database: homeassistant
    username: homeassistant
mariadb_server_args:
  - "--innodb_buffer_pool_size=512M"
  - "--innodb_log_file_size=512M"
  - "--innodb_log_buffer_size=32M"
  - "--innodb_flush_log_at_trx_commit=2"
  - "--innodb_io_capacity=2000"
  - "--innodb_io_capacity_max=4000"
  - "--innodb_read_io_threads=4"
  - "--innodb_write_io_threads=4"
  - "--innodb_file_per_table=1"
  - "--innodb_stats_persistent=1"
  - "--innodb_adaptive_hash_index=1"
  - "--innodb_use_native_aio=0"
  - "--sort_buffer_size=2M"
  - "--read_buffer_size=1M"
  - "--join_buffer_size=1M"
  - "--tmp_table_size=64M"
  - "--max_heap_table_size=64M"
  - "--max_connections=50"
  - "--table_open_cache=1000"
  - "--skip-log-bin"
  - "--slow_query_log=1"
  - "--long_query_time=2"
  - "--skip-name-resolve"
  - "--performance_schema=ON"

:white_check_mark: innodb_use_native_aio=0 is mandatory — native AIO is incompatible with the HAOS container filesystem.

Migration procedure

Total downtime (HA core stopped): approximately 1h30.

Phase Description Duration HA state
0 Create temporary Debian 12 LXC and install dependencies 15 min Running
1 Configure MariaDB addon (do not start yet) 5 min Running
2 Take a Proxmox snapshot of the HAOS VM 2 min Running
3 Start MariaDB addon, then stop HA core 2 min Transitioning
4 Copy SQLite from HAOS to the LXC 2 min Stopped
5A sqlite3mysql — all tables except statistics ~20 min Stopped
5B sqlite3mysql — statistics only, with --chunk 50000 ~55 min Stopped
6 Verify row counts match between SQLite and MariaDB 5 min Stopped
7 Update configuration.yaml and restart HA core 5 min Restarting
8 Validate logs, enable Start on boot, delete LXC 10 min Running

Phase 3 — critical order

Start the MariaDB addon before stopping HA core. Once the core is stopped, the HAOS web UI is gone — you can only interact via the SSH addon.

Phase 4 — copy SQLite from the LXC

scp root@YOUR_HAOS_IP:/config/home-assistant_v2.db /root/

Phase 5A — all tables except statistics

sqlite3mysql \
  --sqlite-file /root/home-assistant_v2.db \
  --mysql-user homeassistant \
  --mysql-password YOUR_PASSWORD \
  --mysql-database homeassistant \
  --mysql-host YOUR_HAOS_IP \
  --sqlite-tables event_data event_types events migration_changes \
    recorder_runs schema_changes state_attributes states \
    states_meta statistics_meta statistics_runs statistics_short_term

Phase 5B — statistics table only

sqlite3mysql \
  --sqlite-file /root/home-assistant_v2.db \
  --mysql-user homeassistant \
  --mysql-password YOUR_PASSWORD \
  --mysql-database homeassistant \
  --mysql-host YOUR_HAOS_IP \
  --sqlite-tables statistics \
  --chunk 50000

:warning: The progress bar exists but appears frozen for the first few minutes — this is normal. Throughput stabilizes around 2300 rows/sec after InnoDB warms up. Monitor LXC RAM in Proxmox — usage stays well within limits on a 4 GB LXC with --chunk 50000.

Phase 6 — verify counts

mysql -h YOUR_HAOS_IP -u homeassistant -pYOUR_PASSWORD homeassistant \
  -e "SELECT COUNT(*) FROM states; SELECT COUNT(*) FROM statistics; SELECT COUNT(*) FROM events;"

sqlite3 /root/home-assistant_v2.db \
  "SELECT COUNT(*) FROM states; SELECT COUNT(*) FROM statistics; SELECT COUNT(*) FROM events;"

Production results (1.9 GB database):

Table SQLite MariaDB
events 1,325,288 1,325,288 ✓
states 98,012 98,012 ✓
statistics 6,784,081 6,784,081 ✓

Phase 7 — switch recorder to MariaDB

recorder:
  db_url: mysql://homeassistant:YOUR_PASSWORD@core-mariadb/homeassistant?charset=utf8mb4
  purge_keep_days: 10 (corrected, was 30)
  commit_interval: 30
  exclude:
    entity_globs:
      - sensor.*_rssi
      - sensor.*_lqi
      - sensor.*_signal_strength

Then from the SSH addon terminal:

ha core start

Expected log output on first start

You will see auto_repairs.schema errors about double precision and utf8mb4_bin columns — this is normal. HA detects the schema differences left by sqlite3mysql and automatically corrects them. Wait a few minutes and they will stop.

Results

The performance improvement is immediately noticeable: UI responsiveness, history queries, and dashboard rendering are all significantly faster. The fundamental reason is that InnoDB’s buffer pool keeps frequently accessed data in RAM (512 MB configured here), while SQLite was reading and writing a 1.9 GB flat file on every transaction.

More importantly: no more corruption. InnoDB’s redo log handles crashes cleanly. The days of manually running .recover on a malformed SQLite file are over.

Questions welcome. If you run into the ERROR 1180 or the frozen progress bar on statistics, check the pitfalls section above — those two issues account for most failed migrations.

Edit : A more straightforward approch is proposed by @Impact [here]. No need to add LXC. (# From SQLite to MariaDB on Proxmox — a complete migration guide for serious setups - #8 by Impact - Installation - Home Assistant Community)

1 Like
2

1: If you are storing data in your database and it gets big, your fast moment to moment actions will suffer, so just don’t store gigs of live data where HA is trying to work fast on 0 horsepower machines, give it it’s own space.
2: HA is highly tweaked to run on sqlite, and does well as a result. There are many problems with Maria being non-native in HA.

For these reasons and many others, I deleted my maria data and went back to sqlite years a ago.

If you want to store lots of data, set up an external database and port your static data over there, graph it all you want. Keep as little static data in your like HA database as you can.

3

Thanks you for your very valuable input. As a matter of fact I’m running as well influxdb and grafana. I’ll see how it is going and in the meantime I’ll try to keep the DB as small as possible.

4

You know, I have wondered if it wouldn’t make sense for HA to default to not writing states (and stats) to the DB and then instead when adding a device you pick what entities to track. I’m sure a the vast majority of my DB is for entities I never will look at, and that’s a common situation. That is, better to not bloat the DB in the first place.

Anyway, I have been wanting to try a different database, just out of curiosity.

@kolia, you set up an LXC as a place to install sqlite3-to-mysql, right? And then you run the migration on the LXC but writing back to the mariadb on HAOS container, correct? Could you run the migration on a separate machine that has MariaDB and then copy the DB files back to HAOS?

1 Like
5

Yes, exactly right on both counts.

The LXC is just a lightweight execution environment for sqlite3mysql — it reads the SQLite file locally and writes directly into the MariaDB addon running inside HAOS, over the Proxmox virtual bridge (vmbr0). No physical network involved, which is why throughput is ~2300 rows/sec instead of the ~165 rows/sec you get when running the tool from a remote machine over LAN.

As for migrating on a separate machine and copying the DB files back — I actually tried a variant of this (mysqldump on a local Windows MariaDB, then import into the addon) and it fails. Two reasons:

  1. Binary files are not portable — InnoDB data files (ibdata, .ibd) are tied to the exact engine version and configuration. Copying them between instances causes corruption.
  2. mysqldump also fails — the SQL dump wraps large tables like statistics in a single transaction. InnoDB inside the HAOS container cannot absorb it and throws ERROR 1180 (HY000): Got error 1 "Operation not permitted" during COMMIT.

The direct sqlite3mysql → HAOS MariaDB path via a co-located LXC is the only approach that worked reliably.

6 
purge_keep_days: 30

This is what bloats your recorder database and I generally don’t recommend to increase the default. It’s simply not needed.
You can easily check which entities bloat your database with this.
Why use a separate LXC instead of the SSH App?

7

All fair points, thank you.

On purge_keep_days — agreed, 30 days was an arbitrary choice on my end. The default (10 days) that I have been using untill now is the right recommendation and I’ll update the post accordingly. The link to the entity bloat diagnostic is a great addition — exactly the kind of complementary housekeeping that keeps the DB lean regardless of the backend.

On the SSH addon vs. LXC — the SSH addon gives you a shell inside HAOS, but HAOS is a closed OS. You cannot install Python packages, libmysqlclient, or sqlite3-to-mysql there. The LXC is a standard Debian environment on the same Proxmox host where you can install anything freely — and critically, it sits on the same virtual bridge (vmbr0) as the HAOS VM, which is why throughput is ~2300 rows/sec instead of ~165 rows/sec over the physical LAN. It is a temporary container — deleted once the migration is done.

8

You absolute can install things and run sqlite3-to-mysql

apk add pipx
pipx run sqlite3-to-mysql ...

You can also just create a temporary debian docker container if you’re so inclined. See some tips/tricks here. I just feel like adding LXC into the mix here restricts the target audience and makes this more complicated than needed.

9

You are absolutely right, and thank you for the correction — I was not aware that apk was available in the SSH addon environment. This simplifies the procedure significantly and removes the need for a temporary LXC entirely.

For the record, the reason I ended up with a LXC was that I hit the ~165 rows/sec throughput bottleneck when running sqlite3mysql from a Windows PC over the physical LAN. The LXC on the same Proxmox host solved that by using the virtual bridge instead. But running directly from the SSH addon achieves the same result — same host, same vmbr0 bridge — so the throughput argument for the LXC disappears too.

I’ll update the post to reflect this simpler approach. Thanks for the tips/tricks link as well — very useful.