Teslamate recently made a breaking change requiring postgresql 16+ and when I tried upgrading from v15 to v17, i ran into some issues,

 

i installed postgresql 17 container via CA store, and "upgraded" from 15 to 17 using the commands below, referencing this process for immich and this video by the same author. both specific to unraid (but for earlier postgres versions)

 

(on postgresql 15 container, click console)
 

pg_dump -U DBUSERNAME -h IPADDRESS -p PORT -fc -f /var/lib/postgresql/data/teslamate.dump DBNAME

 

(on postgresql 17 container, click console)
 

pg_restore -U DBUSERNAME -h IPADDRESS -p PORT -d DBNAME /var/lib/postgresql/data/teslamate.dump

 

I also have integration via MQTT on Home Assistant, and can see certain data that way, and teslamate webgui also seems to be loading fine. However, when trying to access any dashboards via grafana, several errors show up in the postgres logs around "invalid input syntax for type json" and also "syntax error at or near "AND"" errors"

 

I've attached what the log of the postgresql 17 container looks like when accessing grafana dashboards (uploading as text to avoid posting extremely long comment)

 

For reference, I tried asking on the Teslamate github, but they closed my post since they don't support this way of upgrading postgresql (and install via unraid i guess?)

postgres17_log.txt

Edited May 17, 20251 yr by john_smith
updated for clarity

 

Postgress docker doesn't support in tag updates.

You need to use postgress commands and make a backup of your old Postgres sql

 

then deleted the image, delete the app data database folder and install the new tag (usually edit the Postgres docker template updating the repository tag. 

 

once the new version is install you restore the database from the old database.

 

see:

postgress restore sql options:

https://davejansen.com/how-to-dump-and-restore-a-postgresql-database-from-a-docker-container/

 

 

@bmartino1 thanks for your reply. i'm aware postgresql doesn't support tag updates, so perhaps my wording wasn't clear. I've updated my post to reflect this.

 

I "upgraded" by installing the new postgresql 17 container, and using the commands i posted between the two different containers, so i believe i did this correctly, but i'm having issues after the fact.

do you have any idea what might be causing these, or perhaps a way to check if the migrated db was correctly migrated?

Edited May 17, 20251 yr by john_smith

Thank you for updating the initial post. 

this appears to be a common issue going around. when updating most are starting fresh and new due to issues restoring the previous data.

 

Github issues:

https://github.com/teslamate-org/teslamate/discussions/4328

 

General Docker copose upde forum notes

https://voiceofthedba.com/2024/01/08/backing-up-the-teslamate-database/

https://voiceofthedba.com/2024/01/15/updating-teslamate-to-the-latest-version/

 

This appears to be more a teslamate issue and changes to the structure of the new database data.

 

@bmartino1 I actually tried posting on the teslamate github first since I thought they could help having found some of those links you posted myself, but my issue was closed almost immediately before anyone else could answer: https://github.com/teslamate-org/teslamate/discussions/4743 . that's why i came here to ask instead.

 

Reading a reddit comment from the thread you just linked for example:

Thanks for that, i did it!

A little more information, login with ssh to your Synology and check your teslamate database docker name with docker ps.

The name was TeslaMate-DB.

Make a sql backup:
docker exec -t TeslaMate-DB pg_dump -U teslamate teslamate > teslamate_backup_$(date +%F_%H-%M-%S).sql

Remove docker container en manual delete all the files on the drive:
docker rm TeslaMate-DB

Update the stack with postgress:17

Copy the database:
docker cp teslamate_backup_2025-04-29_09-13-32.sql TeslaMate-DB:/tmp/teslamate_backup.sql

Login to postgres:
docker exec -it TeslaMate-DB psql -U teslamate postgres

Drop db:
DROP DATABASE teslamate;

Create db:
CREATE DATABASE teslamate;

Restore backup:
docker exec -i TeslaMate-DB psql -U teslamate -d teslamate -f /tmp/teslamate_backup.sql

Restart, everything works.

Thanks for all the tips here :)

 

this seems pretty similar to what i did, except the database creation i did via adminer, and the new database creation i did via installing a new docker for postgresql 17

4 minutes ago, john_smith said:

@bmartino1 I actually tried posting on the teslamate github first since I thought they could help having found some of those links you posted myself, but my issue was closed almost immediately before anyone else could answer: https://github.com/teslamate-org/teslamate/discussions/4743 . that's why i came here to ask instead.

 

Reading a reddit comment from the thread you just linked for example:

Thanks for that, i did it!

A little more information, login with ssh to your Synology and check your teslamate database docker name with docker ps.

The name was TeslaMate-DB.

Make a sql backup:
docker exec -t TeslaMate-DB pg_dump -U teslamate teslamate > teslamate_backup_$(date +%F_%H-%M-%S).sql

Remove docker container en manual delete all the files on the drive:
docker rm TeslaMate-DB

Update the stack with postgress:17

Copy the database:
docker cp teslamate_backup_2025-04-29_09-13-32.sql TeslaMate-DB:/tmp/teslamate_backup.sql

Login to postgres:
docker exec -it TeslaMate-DB psql -U teslamate postgres

Drop db:
DROP DATABASE teslamate;

Create db:
CREATE DATABASE teslamate;

Restore backup:
docker exec -i TeslaMate-DB psql -U teslamate -d teslamate -f /tmp/teslamate_backup.sql

Restart, everything works.

Thanks for all the tips here :)

 

this seems pretty similar to what i did, except the database creation i did via adminer, and the new database creation i did via installing a new docker for postgresql 17

That looks good for a following of a postgress backup and restore... Sorry at this point, its more trial and error.
 

Sequel can be a bit weird I usual handle mysql . mariadb instance If I recall correctly, i believe there is a Postgres command to commit / write to the database as you could have told it to make but not save changes.

5 hours ago, bmartino1 said:

If I recall correctly, i believe there is a Postgres command to commit / write to the database as you could have told it to make but not save changes.

can you tell me a bit more about make but not save changes? The specific command i ran to restore my database was the following (and nothing else), so let me know if im missing something:

pg_restore -U DBUSERNAME -h IPADDRESS -p PORT -d DBNAME /var/lib/postgresql/data/teslamate.dump

 

 

Postgress commit i'm referring too

https://www.geeksforgeeks.org/postgresql-commit/

https://www.postgresql.org/docs/current/sql-commit.html

 

pg restore doc:

https://www.postgresql.org/docs/current/app-pgrestore.html

 

I think telsamate F up and changed key and ables in the sql file. and instead want users to stat fresh with there sql databases due to the changes they made to the sql tables and structures... as teslmate would need to explain what they did to give advice.

@bmartino1 I see what  you mean now, it looks like they changed the database schema and extensions and wrote a script for for people to use during the upgrade, so i assume everyone who upgraded the normal way also broke their database. how fun

These changes below are what I didn't do when i simply restored the database directly:

# Stop the teslamate container to avoid write conflicts
docker compose stop teslamate

# Drop existing data and reinitialize (Don't forget to replace first teslamate if using different TM_DB_USER)
docker compose exec -T database psql -U teslamate teslamate << .
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
CREATE EXTENSION cube WITH SCHEMA public;
CREATE EXTENSION earthdistance WITH SCHEMA public;
.

# Restore
docker compose exec -T database psql -U teslamate -d teslamate < teslamate.bck

# Restart the teslamate container
docker compose start teslamate

 

So now I know what the issue is, thanks for your help with this!

 

Next I just need to see if it's possible to fix this after the fact or if i need to drop the database and make these changes before restoring it again. I may ask them again on github.

 

Quick question for you about unraid <> docker interaction, if i wanted to enter commands like the ones above (such as "DROP SCHEMA public CASCADE;"), would i do that directly into postgres 17 container, or with docker command ahead of it, or is it the same thing?

correct you can use untids docker console or use docker execu to enter and run comands in the docker.

example:

 

then once in the docker terminal run

psql

-Would need ,ore info at docker creation to construct comand

to connect to the postgress database...

 

then run your sql commands

 

https://www.docker.com/blog/how-to-use-the-postgres-docker-official-image/

I just went ahead to further eliminate variables, and restored my database again from scratch from v15 to v17, this time making sure to even use a .bck file and avoid the -fc argument on backup, yielding a much larger backup file of 657MB, and using all the commands from the instructions, and restoring using pgsql command instead of pg_restore

 

i ended up using the following commands this time instead:

 

# postgres v15 container
pg_dump -U DBUSERNAME -h IPADDRESS -p 5432 -f /var/lib/postgresql/data/teslamate.bck postgres

# start teslamate container to initialize database, then stop container

# postgres v17 container
# Drop existing data and reinitialize 
psql -U DBUSERNAME -d teslamate
DROP SCHEMA public CASCADE
CREATE SCHEMA public
CREATE EXTENSION cube WITH SCHEMA public
CREATE EXTENSION earthdistance WITH SCHEMA public

psql -U DBUSERNAME -h IPADDRESS -p 5432 -d teslamate < /var/lib/postgresql/data/teslamate.bck

 

 

however, i am still getting no data showing in grafana, so i'm still where i started unfortunately

Edited May 19, 20251 yr by john_smith

OK. As I'm not 100% proficeint with postgress. I don't run teslamate myself...

Here's are some double check things as it may be grafana at this point...

so lets console into the teslmate postgress via console and psql...

After the restore is applied to verfy the data...

 

1. Verify Data Exists in PostgreSQL (psql list commands)

Connect to your PostgreSQL v17 container and run the following commands to confirm that data was restored successfully:

# List databases
\l

# Connect to teslamate
\c teslamate

# List tables to see if they exist
\dt

# Check record counts for some important tables
SELECT COUNT(*) FROM drives;
SELECT COUNT(*) FROM positions;
SELECT COUNT(*) FROM charges;

 

If these return non-zero counts, then the data is in the database — so the issue is likely on the Grafana/connection/query layer. otherwsie your restore didn't work...

 

2. Verify Grafana is Pointing to the Right Database

Make sure the PostgreSQL datasource in Grafana is configured to connect to the new PostgreSQL v17 instance.

Double-check:

Host/IP and port

Database name: teslamate

User credentials

SSL settings (if used)

 

3. Check TeslaMate Metrics Backend

TeslaMate stores metrics in PostgreSQL, which Grafana visualizes using prebuilt queries.

You can also check whether the TeslaMate backend is working by visiting the /health endpoint in your browser:

 

http://<teslamate_ip>:4000/health

 

4. Check Logs for Errors

?Any error in log ? teslamate or postgress?
after restore did you stop start postgresss?

 

Check logs from the relevant Docker containers:

# Check teslamate logs
docker logs <teslamate_container_name>

# Check PostgreSQL logs
docker logs <postgres_container_name>

# Optional: check Grafana logs
docker logs <grafana_container_name>

 

Look for:

Connection errors

Schema or permission issues

Extension-related errors (cube, earthdistance)

 

5. Sanity Check Your Restore Command

Your psql restore command is basically correct, but here’s a slightly adjusted version with environment-based authentication for best practice:

PGPASSWORD=yourpassword psql -U DBUSERNAME -h IPADDRESS -p 5432 -d teslamate < /path/to/teslamate.bck

per ai to help construct the command...

 

As i Still think this is something teslmate did and changed the datbase structure thus grafana is not readin the new structure as wel...

 

If everything checks out and you still see no data in Grafana:

TeslaMate might not be fully compatible with the way the backup was restored.

Check if TeslaMate needs to be restarted after the data is restored.

Try pointing Grafana directly at PostgreSQL and manually querying a table to validate connection and data.

@bmartino1 once again thank you, I can't say this enough. You've helped me think through how this works and examine what may have been missing, and based on what you said I found the issue was twofold. First off, for context, the teslamate install on unraid is made of of 4 containers:

• MQTT
• TeslaMate
• TeslaMate-Grafana
• PostgreSQL

What you said helped me re-asses that I should check configuration for each, not just postgresql 17/15 container. So my issues were:

1. TeslaMate-Grafana container was not pointing to right database name
   • I had renamed database from default name "postgres" to new name "teslamate" to be more specific
   • this is changed in the container config, click container then edit
   • this is purely user error and likely wouldn't happen to others
2. TeslaMate-Grafana appdata, which is points to appdata/TeslaMate/grafana had issues
   • I'm not sure exactly what, but assuming that my data was safe in the database, I deleted this appdata folder and reconfigured grafana
   • this fixed my issue and may also happen to others

@john_smith would it be possible to ask for a step by step of what you did to possibly avoid the issues you ran into? I'm looking to make the changes on my side and hoping to avoid issues/data-loss so it would be great if you happen to remember or have a short guide you followed if its now in a working state :)

Hey @brent3000 as i understand it, this is the process essentially from start to finish on unraid:

# stop teslamate + teslamate-grafana + teslamate-abrp (if you use it)

# on postgres v15 container -> console
pg_dump -U DBUSERNAME -h IPADDRESS -p 5432 -f /var/lib/postgresql/data/teslamate.bck postgres
# this creates the file /mnt/user/appdata/postgres15

# copy the backup file to /mnt/user/appdata/postgres17

# stop postgres v15 container

# start postgres v17 container

# start adminer -> via webgui create database there named "teslamate" 

# start teslamate container to initialize database
# stop teslamate container

# on postgres v17 container -> console:
# Drop existing data and reinitialize, to do this:
psql -U DBUSERNAME -d teslamate
DROP SCHEMA public CASCADE
CREATE SCHEMA public
CREATE EXTENSION cube WITH SCHEMA public
CREATE EXTENSION earthdistance WITH SCHEMA public

# then restore your previously backed up data:
psql -U DBUSERNAME -h IPADDRESS -p 5432 -d teslamate < /var/lib/postgresql/data/teslamate.bck

# start teslamate, verify it works as expected

# finally, go to /mnt/user/appdata/teslamate/grafana and delete all folders/contents
# since deleting appdata/grafana contents will delete previously configured password,
# reconfigure teslamate-grafana user/pass (Default user/pass is admin/admin) 

# start teslamate-grafana, verify it works as expected
# start teslamate-abrp (if you use it)

 

FYI i'm still not sure why I had to delete appdata/grafana to get it to work correctly, but it worked for me.

Here's an additional note from the CA store on permissions for teslamate-grafana in case this happens for you, I ran this preemptively just in case but i don't think it should apply:

If you run the container w/out this command and it fails to allow creating the SQLite DB, then an easy way to allow the proper permissions after would be just to do a
chmod 0777 /mnt/user/appdata/teslamate/grafana

even if you delete the docker image the appdata had old configuration for both grafana and postgress have configs in the appdata folder that needed reset for inital reconfiguration as a fresh docker install.

1 hour ago, bmartino1 said:

even if you delete the docker image the appdata had old configuration for both grafana and postgress have configs in the appdata folder that needed reset for inital reconfiguration as a fresh docker install.

exactly, i didn't delete any docker containers in my whole process because of this, configuration/databases lives in appdata folders for grafana, teslamate, and postgres

How big was your backup @john_smith My backup is only a 430bytes and Ive been running it for a long time :/ THought it would be alot bigger

Ok so I did abit of a cheat, as I have been using PGAdmin4 for another project and to keep backups I thought to just go and use that and ended up being alot simpler to connect, backup and do a clean restore into the new system,

As it stands seems like everything is as normal, going to try the actual docker update now for TM and hope nothing else breaks :)

I ended up being on Postgres 12.5 so I was well out of date as its now on 17 :)

Thanks for the above guide helped with the process to make it all work and now I have a separate data backup process at least :)

Edited May 24, 20251 yr by brent3000
Updated with changes

glad to hear it worked for you. My .bck backup file was over 500MB, but if you got it figured out via PGAdmin4 that's great, sounds easier. I had tried using PGAdmin a while ago but couldn't get it to work, though that was when i first set up teslamate, maybe i should give it another shot.

I would recommend it..
It is a nicer interface to interface with Postgres. Unless your comfortable setting docker variable paths and running postgress commands to touch and edit the database.

just installed it again and was able to configure it correctly this time. i'm already done with this migration so i don't have much use for it right now, but it will be useful in the future i'm sure

On 5/18/2025 at 11:05 PM, john_smith said:

I just went ahead to further eliminate variables, and restored my database again from scratch from v15 to v17, this time making sure to even use a .bck file and avoid the -fc argument on backup, yielding a much larger backup file of 657MB, and using all the commands from the instructions, and restoring using pgsql command instead of pg_restore

 

i ended up using the following commands this time instead:

 

# postgres v15 container
pg_dump -U DBUSERNAME -h IPADDRESS -p 5432 -f /var/lib/postgresql/data/teslamate.bck postgres

# start teslamate container to initialize database, then stop container

# postgres v17 container
# Drop existing data and reinitialize 
psql -U DBUSERNAME -d teslamate
DROP SCHEMA public CASCADE
CREATE SCHEMA public
CREATE EXTENSION cube WITH SCHEMA public
CREATE EXTENSION earthdistance WITH SCHEMA public

psql -U DBUSERNAME -h IPADDRESS -p 5432 -d teslamate < /var/lib/postgresql/data/teslamate.bck

 

 

however, i am still getting no data showing in grafana, so i'm still where i started unfortunately

Thanks for this post. I was able to dump my database ~2.1GB, running on Postgres 11 and migrate the data to Postgres 17. I didn't have to drop or reinitialize anything though. My old db was named teslamate, and so was the new one. I kept the username and passwords the same between the two databases and it just worked.

My process was :

Start PG11 docker

run pg_dump (keeping in mind the last "postgres" in the command is the name of the database, so I changed it to "teslamate" in my case.)

Stop PG11 docker

Create PG 17 docker and set the default db to 'teslamate' as well as the username/pw the same as my pg11 docker.

Look at the logs and make sure it created the db.

Stop PG17 docker.

Copy the teslamate.bck to the appdata for the new db.

Start PG17 docker again.

run the psql command the import the teslamate.bck file.

Start teslamate, verify it works.

Start grafana and verify old data is still there.

Everything went off without a hitch, so thanks for helping me to drag myself into a much more supported database version.

Edited June 2, 20251 yr by kwiksilver