Thanks @Expaso,
I had concerns mysqldump would somehow not be compatible with Postgress.
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.
Is it possible to reset the DB? Similar to delete the home_assistant_v2.db?
Thanks
How do you mean reset? Dump all the data and start over?
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 
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ā¦
1 Like
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.
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.
I am running the extension. Really great work. Can I connect to the database from another client in the networtkusing psycopg in python ?
Hi @ogiot! Yes, this is certainly possible.
All you have to do is map a port to the outside world and restart the addon.
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:
Installing timescaledb + pgadmin4
With pgadmin 4, I made the connecting ith PostgreSQL.
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
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.
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:
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
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
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:
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.
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?
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.
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!
Thank you very much - Helped a lot - Saved me precious time - Connection now without problems
ogiot
1 Like