Restore data after update.

[Sieg-hart]

Hello,

Sorry if this question has already been asked.

I recently upgraded from version 1.30.0 to version 1.32.0.

I made a backup of the database using this documentation, via the command docker-compose exec -T database pg_dump -U teslamate teslamate > ./teslamate.bck

I have a 60mb teslamate.bck file that seems to contain all the data.

I deleted the stack containing teslamate and recreated a new stack from scratch.

I now have a teslamate instance in 1.32.0 with a postgreSQL database version 17.

The problem is that when I try to restore the data... nothing happens.
I run the command docker-compose exec -T database psql -U teslamate -d teslamate < teslamate.bck.
I get the prompt without any message.

root@raspberrypi:/home/sieg# docker-compose exec -T database psql -U teslamate -d teslamate < teslamate.bck
root@raspberrypi:/home/sieg#

When I look in Grafana, I have no data.

And when I look at the database logs, I have error messages related to SQL syntax errors but in queries that are not in my .bck.

For example :
2024-12-02 22:49:12.203 UTC [90] ERROR: syntax error at or near ")" at character 295
2024-12-02 22:49:12.203 UTC [90] STATEMENT: SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY since_last_update) FROM (
SELECT extract(EPOCH FROM start_date - lag(start_date) OVER (ORDER BY start_date)) AS since_last_update
FROM updates
WHERE start_date BETWEEN '2014-12-02T22:49:13.069Z' AND '2024-12-02T22:49:13.069Z' AND car_id =
) d;
2024-12-02 22:49:12.241 UTC [91] ERROR: syntax error at or near "and" at character 145

There is no such select in my file, just create, and copy.

I tried to execute the SQL queries directly in the database.
The "creates" worked (with a few ; less).
The "copies" didn't work.
I tried to transform the copies into insert into, some data is added to the database, but does not appear in Grafana.

I don't know what to do anymore. :(

[eliluong]

What was the old Postgres version? What you may need to do is stop all containers, delete the "new" database volume that is not working. Then start only the database container. Then try the restore command here: https://docs.teslamate.org/docs/maintenance/backup_restore

If that does not work, you may have to get into the Docker database container (something like https://stackoverflow.com/questions/30172605/how-do-i-get-into-a-docker-containers-shell), then use chatgpt or something similar to help you format the commands correctly. You will probably have copy the backup file into the container. Then you can see whatever error messages show up during import.

I was on Postgres 13 for a long time before upgrading to Postgres 15, and it was not straightforward.

[eliluong]

I think you have no data showing in Grafana because the log you posted indicate that (probably) Grafana is querying the database, but there is an issue with the underlying data. All those SELECT statement I think are queries that Grafana does on the database. If you check the dev console or Grafana container when you load a Grafana page, are there errors?

I'm not sure if you can create a new backup, but that would be easiest.

I would delete all the containers/volumes. Start fresh, only start up the database container, and then try restoring the backup inside the container. It might show more informative information if it runs into an error.

[Sieg-hart]

Are you sure grafana is looking at the correct database?

This is the question I'm asking myself, but I only have a postgreSQL database on my raspberry.
I connect to the teslamate-database-1 database, with the login and password, and to the port indicated in docker-compose.yml.
I don't see any other database. :/

root@raspberrypi:/home/sieg# docker ps
CONTAINER ID   IMAGE                           COMMAND                  CREATED        STATUS                  PORTS                                                                                                               NAMES
f19037177bf2   postgres:16                     "docker-entrypoint.s…"   15 hours ago   Up 15 hours             0.0.0.0:5432->5432/tcp, :::5432->5432/tcp                                                                           teslamate-database-1
c63ce45919b9   teslamate/grafana:latest        "/run.sh"                15 hours ago   Up 14 hours             0.0.0.0:3000->3000/tcp, :::3000->3000/tcp                                                                           teslamate-grafana-1
b27eb5892ee9   eclipse-mosquitto:2             "/docker-entrypoint.…"   15 hours ago   Up 14 hours             1883/tcp                                                                                                            teslamate-mosquitto-1
5accd86da191   teslamate/teslamate:latest      "tini -- /bin/sh /en…"   15 hours ago   Up 14 hours             0.0.0.0:4000->4000/tcp, :::4000->4000/tcp                                                                           teslamate-teslamate-1
a39925d02d5f   portainer/portainer-ce:2.21.4   "/portainer"             2 days ago     Up 21 hours             0.0.0.0:8000->8000/tcp, :::8000->8000/tcp, 0.0.0.0:9443->9443/tcp, :::9443->9443/tcp, 9000/tcp                      portainer
cb70dec2d0d8   pihole/pihole:latest            "/s6-init"               2 months ago   Up 21 hours (healthy)   0.0.0.0:53->53/tcp, 0.0.0.0:53->53/udp, :::53->53/tcp, :::53->53/udp, 67/udp, 0.0.0.0:800->80/tcp, :::800->80/tcp   pihole

[Sieg-hart]

I think you have no data showing in Grafana because the log you posted indicate that (probably) Grafana is querying the database, but there is an issue with the underlying data. All those SELECT statement I think are queries that Grafana does on the database. If you check the dev console or Grafana container when you load a Grafana page, are there errors?

I'm not sure if you can create a new backup, but that would be easiest.

I would delete all the containers/volumes. Start fresh, only start up the database container, and then try restoring the backup inside the container. It might show more informative information if it runs into an error.

I have no error message in the dev console, and in the grafana logs I only have lines msg="Request Completed", msg="Completed cleanup jobs" or msg="Usage stats are ready to report"

I deleted my initial stack, I can no longer create a new backup.

I wanted to look at what the new data was like to possibly reformat the old ones if the model had changed but I don't see the new data in the database.

I reinstalled the stack from scratch, without trying to integrate my old data.
I put the requested information to connect to the tesla api.
I see the data showing on the web interface on port 4000, as well as in grafana. so far, nothing unusual.

But still nothing in the database, the public database remains empty, and I still have SQL errors in the database logs.

[Sieg-hart]

I think I found the problem.

Unable to import the data via the command lines, I modified the dump to change the copy to insert into (using a custom script), and modified the function declarations to remove the ; after END.

By exemple :

CREATE FUNCTION public.convert_m(n double precision, unit text) RETURNS double precision
    LANGUAGE sql IMMUTABLE STRICT
    AS $_$
  SELECT
    CASE WHEN $2 = 'm' THEN $1
         WHEN $2 = 'ft' THEN $1 * 3.28084
    END
$_$;

And I tried to connect to the database from outside.
The tool I used (heidiSQL) did not show me all the databases (the tool seems compatible more with mySQL databases than postgreSQL.).
I only saw the "postgres" base and not "teslamate".

So I put the data in the wrong database.

So I recreated the stack from scratch, stopped all the containers except the database, (important otherwise the tables and foreign keys are created and prevent the addition of data) I connected to it with pgAdmin4. By running the dump, table by table, on the correct base, I was able to add my old data (except the contents of the token table which generated errors).

After restarting the containers and configuring the API tokens, it works!

Thank you for your help.

[brianmay]

My limited experience was that plain sql files don't work so well when changing psql versions.

e.g. not tested.

pg_dumpall --format tar > db,dump
pg_restore --create --format tar db.dump

Wonder if we need to update the docs.