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
You have to remove the homeassistant entry from the ‘timescale-enabled’ list in the config. but keep it in the ‘databases’ list.
If the ‘timescale-enabled’ list becomes empty, just use ‘[]’ to denote an empty list.
Just like this:
If you do this on a fresh installed add-on, which hasn’t been started yet, you’re done.
Timescale won’t be installed for databases not in that list.
If you have an already running addon, you can simply remove the Timescaledb extension by hand afterwards:
!! Please be aware that any timescale-ish stuff you’ve created earlier will be removed on that specific database (but that’s your intention in this case I presume).
Hi Expaso,
Thank you for your input! I got it up and running.
I created an user: view_user with a password, with can login, inherit rights from the parent roles, Can initiate streaming replication and backups.
I would like to use this user as a viewer connecting Postgres with Grafana, but getting a warning “pq: password authentication failed for user ‘view_user’”
Do you have any idea what I mist?
Kind regards,
Robert
Hi Rob8!
Here you see how I use a special read-only user ‘grafana’ to access the database (both grafana and timescale running on the same raspberry pi).
Your settigns should exacly match this screenshot, except for the username ofcourse.
Thank you very much for this addon, I really love it!
I have a graph in grafana that displays my fitbit steps:
SELECT
"time" AS "time",
state as metric
FROM ltss
WHERE
$__timeFilter("time") AND entity_id = 'variable.fb_steps'
GROUP BY 1,2
ORDER BY 1,2
But I don’t know how to SUM them, so that all steps are calculated together. I tried SUM(state) as metric, but I get only this error:
pq: function sum(character varying) does not exist
Can somebody help?
Hi @drimpart!
Thank you very much! Nice to hear!
I think I know what the problem is:
The ‘state’ field of ltss is a text-field. This is because a state in HA can be anything. The name of your cellphone, the position of the sun, or in your case: the amount of steps.
The error indicates that SQL does not know how to sum text…(varying characters).
To overcome this, please take a look at: Additional Documentation for Timescaledb Integration · Issue #1 · Expaso/hassos-addons (github.com)
1 Like
fantastic! Thank you @Expaso that was the trick, I did run both scripts (the second one took me about 7 minutes) and now I can calculate with the values!
One last question: I have some sensor that update more than once a day, and now I want to sum all maximum values (so the last ones before they reset at 0 o’clock) - is this possible?
Sure it is!
It looks like your step counter increments during the day, looking at your data, and I presume you want to show a graph where you can see the total steps per day, am I right?
If so, try something like this:
SELECT
$__timeGroup(time,1d,0) as "time",
max(state_numeric) AS "state_numeric"
FROM ltss
WHERE
$__timeFilter(time) AND
entity_id = 'sensor.steps'
GROUP BY 1
ORDER BY 1
The magic is in the $__timeGroup(time, 1d) which groups your measurements per (1) day, and then take the max(state_numeric) to get the max per day.
The $__ functions are Grafana macro’s. See also: https://grafana.com/docs/grafana/latest/datasources/postgres/#time-series-queries
2 Likes