(help) How do I migrate database from matt-FFFFFF's PostgreSQL to alexbelgium's Postgres, both in HA addons.

[wyx087]

Hi lildude,

Thanks for taking up the mantle with this Teslamate addon.

Please can you help me migrate from matt-FFFFFF's PostgreSQL 12.5 addon to alexbelgium's Postgres 15 addon?

The docker compose command in Teslamate documentation doesn't map into Home Assistant addon. What other way is there to do this database migration? Could alex's Portainer addon help here?

Sorry, I've only used HA addons. Docker commands is completely out of my depth.

Thanks very much

[lildude]

The process is pretty much what is documented in the TeslaMate docs with a few tweaks to use docker exec [POSTGRES CONTAINER ID] instead of docker compose.

Your question has motivated me to finally make the switch. I’ll try find some time this weekend and I’ll document my steps.

[wyx087]

Thanks for reply. But how do I run docker exec commands in Home Assistant OS?

Use an SSH add on? But they are sandboxed from each other isn’t it? Need host level access, somehow?

[lhomann]

Hi lildude,

Thanks for taking up the mantle with this Teslamate addon.

Please can you help me migrate from matt-FFFFFF's PostgreSQL 12.5 addon to alexbelgium's Postgres 15 addon?

The docker compose command in Teslamate documentation doesn't map into Home Assistant addon. What other way is there to do this database migration? Could alex's Portainer addon help here?

Sorry, I've only used HA addons. Docker commands is completely out of my depth.

Thanks very much

I did a migration a few weeks back. Out of memory did I do those steps: 1 Install Add-on "pgAdmin4" from https://github.com/expaso/hassos-addons 2 Start the add on and open the web UI 3 Make two connections, 1 to your old postgres and the other to the new postgres 4 create new database with same name as the old database on the new postgres instanc 5 create a new postgres user with same username and password on the new postgres DB. 6 now make a backup of the old DB 7 now restore. select the new database and right click and select "restore" 8 stop old postgres database 9 reconfig teslamate with paramaters to use the new postgress hostname (username and pwd should be the same, because you have created it in step 5) 10 start teslamate

good luck

[wyx087]

Awesome, that worked. Thank you very much for your help.

Needed some juggling because both PostgreSQL addons used the same port and pgAdmin wouldn't connect to a different port for me.

0. Should have old PostgreSQL 12.5 and Teslamate addon. Also installed new Postgres 15 as per ReadMe requirements and this TeslaMate addon
1. Install Add-on "pgAdmin4" from https://github.com/expaso/hassos-addons
2. Start the pgAdmin4 add on and open the UI
3. Make a connection to old (host name, user and password copy from old TeslaMate config page)
4. Back up the database specified in old TeslaMate config page.
5. Stop old TeslaMate addon and PostgreSQL 12.5 addon.
6. In new Postgres 15 addon config, input database name that was just backed up. User and password is same as step 3.
7. Start new Postgres 15 addon.
8. Restart pgAdmin4 addon
9. Restore database from earlier backup that was done in Step 4.
10. Config new TeslaMate as per migration notes of this addon, take care to change to new database_host "db21ed7f-postgres".
11. Also need to go into Grafana -> Top left menu -> Connections -> Data sources, Select TeslaMate data source and change Host URL to new Postgres 15 address: "db21ed7f-postgres"

Everything seems to work so far. Yet to take a drive but it's fetching data as expected, when car is woken up.

Thanks very much for keeping this great addon going.

---- Edit: Added step 11, needed to change data source in Grafana to new Postgres addon.

Now verified all previous data and new driving are recorded and visible in Grafana.

---- Edit 16th September:

I noticed Postgres was restarting every 2-3 minutes and I was getting miles in "Data lost (not logged)" under battery health view. Trips has lots of short interval drives without start or stop location.

Fix(es!):

• In Postgres addon, remove database name and username. Must use default postgres database and username. This stops Postgres restarting.
• In PgAdmin, backup old database and restore into postgres database. Backup may fail due to pg_dump version mismatch, fix by going into File - Preferences, Paths - Binary paths. Add /usr/local/pgsql-15 to PostgreSQL 15)
• Edit Grafana and TeslaMate to use new database and username.
• Reading TeslaMate log, you may also run into this problem, I did: https://github.com/diogob/activerecord-postgres-earthdistance/issues/30#issuecomment-2122829036
• Fix by going back into PgAdmin and right click on postgres database, click on CREATE script. Delete everything auto generated and paste in linked code. Click Execute script.

So far Postgres is stable have not auto restarted for a while. TeslaMate isn't generating errors in its logs and Grafana can still read all the data from the start.

---- Edit later on same day, 16th

The problem restarting every 2-4 minutes came back, it has nothing to do with database names or username. It is due to my Proxmox ZFS storage replication. Removing this replication task that was set to every 2 minutes (it was in error state when I was fixing Postgres earlier) seems to make Postgres stable.

But above info for Postgres from 12 to 15 may still be useful to people.