Home Assistant Add-on: PostgreSQL + TimescaleDB

[arendal]

Thanks @Expaso,
I had concerns mysqldump would somehow not be compatible with Postgress.

[Expaso]

There is a sort of compatibility flag: Converting MySQL to PostgreSQL - Wikibooks, open books for an open world

If this road leads to nowhere, then pgLoader will do the job just fine.

[Ecard (Eric Cardinal)]

Is it possible to reset the DB? Similar to delete the home_assistant_v2.db?

Thanks

[Expaso]

How do you mean reset? Dump all the data and start over?

[Ecard (Eric Cardinal)]

For some reason, after a restore, the database stops accepting data after some time

So, I would like to be able to reset to a fresh install

[Ecard (Eric Cardinal)]

Here what I did for x times I uninstalled the add-on, then reinstalled.

Looks like the data is being inserted… I will let you know if I get the problem again

To move my data from SQLite, this is what I did:

• Once the data is set to Posgres
• copied the home-assistant_v2.db to my laptop
• Using DBeaver
• Export the events table and states table to CSV file
• Import the CSV files in Postgres using DBeaver

Reset my password, open the port…

[Expaso]

Hmm… never had that problem… what did the logs say?
Importing data seems right.

If this should happen again indeed please mention, so we can investigate.

[Ecard (Eric Cardinal)]

I found my problem… After a restore, for some reasons it was getting duplicate key on an insert on the events table. I truncated the table to fix the problem.

[ogiot (Toni)]

I am running the extension. Really great work. Can I connect to the database from another client in the networtkusing psycopg in python ?

[Expaso]

@ogiot

ing the extension. Really great work. Can I connect to the database from another client in the networtkusing psyc

Hi @ogiot! Yes, this is certainly possible.
All you have to do is map a port to the outside world and restart the addon.

[Rob8]

My question is how can I change the standard postgres password.

ALTER USER user_name WITH PASSWORD 'strongpassword';

I can log is via ssh: ssh [email protected]

But I have no clue how to do this?

After this I would like to migrate my SQLite to postgres.

Hopefully some one can help me out.

Kind regards,

Robert

Thank you Hans for your great work!

I followed the installation:

Expaso/hassos-addons

Installing timescaledb + pgadmin4

With pgadmin 4, I made the connecting ith PostgreSQL.

Expaso/hassos-addons

My Rpi 4, 4Gb (with SSD) installation is as followed:

Home Assistant installation:

https://krdesigns.com/articles/installation-home-assistant-with-supervisor-on-debian-10

[Expaso]

Hi @Rob8,

You’re very welcome!
To access Postgres, you need a Postgres client like pgAdmin.
When you connect to your database using pgAdmin, the default credentials are:

user: postgres
password: homeassistant

After you have succesfully logged in, you can cahnge the password using the method you described.

About the SQLLite migration, try this:

How to migrate from SQLite to PostgreSQL (Rails) - Stack Overflow

It seems that sqllite3 could be used to generate a big script to dump the data into a sql-file that could be used to replay in Postgres.

[Rob8]

Hi Expaso,
Thank you for you replay. First of all thank you for your great work and making PostgreSQL, TimescalDB available!
I am able to connect pgAdmin 4 to the postgres database, but unable to change the standaard pasword. Hopefully you can alaborate on how I can change the username and password. Sorry I am still fresh with Home Assistant, postgres and pgAdmin 4.

I found the following link:

Protip: How to get shell in actual homeassistant (or addon) container when using Hassio

This took a surpising amount of digging to find… I needed to debug a command_line switch that wasn’t working as expected in hass.io so I wanted a shell on the actual environment. It’s pretty simple once you actually figure out how… Shut down your pi and put the SD Card in your computer. Create a file in the resinos-boot partition called authorized_keys and put your ssh public key in it. Put the SD Card back in your pi and start it up. Run this SSH command: ssh -t root@hassio -p 22222 “docker …

To login via ssh using ssh -t [email protected] -p “docker exec -it $(docker ps -f name=homeassistant -q) bash
I am promted with dquote>
I have now idea what to do next.

My question still is how do i make a backup/ dump from de standard used SQLite and import this in to PostgreSQL?

Looking forward hearing from you!

Kind regards,
Robert

[ogiot (Toni)]

Sorry,
Tried but don’t know how to fill the connection form in pgadmin network client
Host 77b2833f-timescaledb … Port 45678 didnt work.

Thanks

Toni

[Expaso]

Hi Rob!
The command to change the default password is to be run from within a SQLClient like pgAdmin.
It’s not a shell command, so there is no need to SSH into the container:

[Expaso]

Hi @ogiot!

I am right that you can successfully connect to the Postgres database using the pgAdmin add-on,
but you can’t connect to it from a different client in your LAN?

If so, you would use the ip-address of your homeassistant box to conenct to, and the portnumber you configured within the application.

The hostname 77b2833f-timescaledb only works between containers (add-ons) within your home-assistant box:

Add-On Communication | Home Assistant Developer Docs (home-assistant.io)

Connection from and to the outside world always use the LAN IP address of your box, that’s why you have to map it in the config.

[arunas (ak)]

Hi!
Thank you for a great add-on! I have everything running and its working well.
I have 1 database running and its also using the timescale extension since I have the default configuration running.

databases:
  - homeassistant
timescale_enabled:
  - homeassistant

But I want my db to be pure postgresql and not timescale.
When I try removing it from timescale_enable I can’t save the configuration. Any ideas?

[Expaso]

Hi Arunas!

If you want this, it’s best to remove the add-on, re-add it, and before starting, remove the

• home-assistant entry from the list, and then start the addon for the first time.
  !! BEWARE !! You will lose your existing data !!

Having said that, I’ve never tried this myself to be honest.
To be curious, why would you not want those extensions enabled? They won’t do any harm if you simply don’t use them.

[arunas (ak)]

Hi, thank you for a quick reply!

By removing home-assistant entry from the list, do you mean the “databases:” list or “timescale_enabled:” list? or both?

I am working on a project where I’m supposed to have a postgreSQL database and not anything else, and I’m fine with losing all my data in my current database if I manage to switch it to postgre.
I am aware that timescaleDB has a lot of advantages and would definitely use it in my own project!

[ogiot (Toni)]

Thank you very much - Helped a lot - Saved me precious time - Connection now without problems

ogiot