Thanks so much to everyone yesterday in the ‘Configuration’ section of the forum, it was a big help and a great solution to my problem of getting my GCE IPX controller communicating with HASS.
At the same time I also managed to get the internal Sqlite db migrated over to an external Postgresql db as the hass v2.db file grew to over 12GB and things started to become really slow.
My initial thought was to use MySQL but after lot’s of reading it turns out that HASS supports many different types of DB and among them was my favorite Postgres.
Here is the list of links in case anyone is interested:
https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL
https://docs.sqlalchemy.org/en/13/errors.html#error-gkpj
https://www.google.com/search?client=firefox-b-d&q=postgres+rename+table
So basically here is what I have done:
- The first thing was to dump the sqlite db after shutting down HASS
sqlite database .dump > /the/path/to/sqlite-dumpfile.sql
- Next is convert the dump over to pgsql using pgloader (tip: make sure it is pgloader v3)
2.1 First create the db file in Postgres and the hass user
create database <db_name>;
create user <uname> with password <pass>;
2.2 Then create a command file for pgloader
load database
from 'db.sqlite3'
into postgresql:///testdb
with include drop, create tables, create indexes, reset sequences
set work_mem to '16MB', maintenance_work_mem to '512 MB';
2.3 Run pgloader
pgloader command
2.4 Add the db path to hass configuration.yaml
recorder:
db_url: postgresql://user:pass@<pg_ip>/hass_db
2.5 Start hass and enjoy 
Note: Just a quick note on my side, I had some issues with hass complaining about NULL constraints within the table ‘recorder_runs’. I basically just backed the table up by renaming it and letting hass create an new one
alter table recorder_runs rename to recorder_runs_backup;
Here are some screenshots of what I have been able to do with hass so far including a long list of things to work on. The entrance lighting is a little redundant now as I have tied the lights into GPS using ‘device_tracker’, I have 3 different views; 1 for phones, 1 for the on/in-wall SIBO Q8919 10" tablets, 1 for desktop/laptop PC:
This last picture which shows the ‘Panel View’ is monitoring my 2 meter high ABB DIN panel containing my electric, distribution and automation systems:
, an Atom or Celeron system with around 8GB RAM is a pretty powerful beast! <- just make sure to not to run any X11 stuff…
