From postgressdb to SQL Lite

Installation
1

I have a postgressdb that I can access with pgadmin.
But I want to start using the default sqllite db whitout losing my energie history :frowning:

What are the steps?

I already made .sql csv’s for
statistics
statistics_meta
statistics_run
statistics_short_term

but when importing on DB Broser for SQLLite:
I got several warnings: columns are not matching, constraint is missing or prevent to import.

My question is who has done this before are have the export scripts / tips?
(maybe converts, or include needed things).

2
  1. Make sure to use “full” inserts in your SQL, e.g.
INSERT INTO `statistics_short_term` (`id`, `created`, `start`, `mean`, `min`, `max`, `last_reset`, `state`, `sum`, `metadata_id`, `created_ts`, `start_ts`, `last_reset_ts`) VALUES (21478267,NULL,NULL,68.93,68.93,68.93,NULL,NULL,NULL,27,1681305610.5847085,1681305300,NULL);
  1. Insert in the proper order
  • statistics_meta
  • statistics
  • statistics_runs
  • statistics_short_term
3

Please excuse my curiosity, but what are the reasons why you want to switch from a PostgresDB to SQLite?
Postgres is much more flexible and powerful. Or are you concerned with resource consumption?

4

There is a similar thread for MySQL

5

thanks you very much I will try that this weekend and hope to minimine downtime / lost data :slight_smile:

the reason is that I followed a tutorial to use postgress but i’m no expert. so It would be better for me to have the default. Other thing is that opening developertools > states is maybe a bit slow. I want to test how performance are with sqllite.

6

I tried it but no succes yet.
Error executing query: (sqlite3.DatabaseError) database disk image is malformed [SQL: SELECT statistics_meta.id, statistics_meta.statistic_id, statistics_meta.source, statistics_meta.unit_of_measurement, statistics_meta.has_mean, statistics_meta.has_sum, statistics_meta.name FROM statistics_meta] (Background on this error at: Error Messages — SQLAlchemy 2.0 Documentation)

7

I have found a simple way! :slight_smile:
Steps I have done.

  • comment out recorder url to postgress and restart
  • sqllite file is created by Home Assistant. this file I copied to my local system to edit.
  • dbeaver https://dbeaver.io/
    opened the postgress and SQLLite
  • cliked on Import data on the followind tables (in that exact order):
  • statistics_meta
  • statistics
  • statistics_runs
  • statistics_short_term

Select the already opened tables in postgress as sources tables for the import.
alle data is copied from postgress to the local sqllite.db
I moved the db to the config folder op Home Assistant and restarted.
All seems to be succesfully.