TeslaMate [megathread]

[cwanja]

Hello,

currently i am running teslamate in raspberry pi with Ubuntu OS and docker installation. I want to move my entire setup to windows. I have installed Docker desktop in windows 11 and installed teslamate.
how do i restore the database that was taken backup from raspberry pi setup and restore it on windows docker setup ?

i was able to successfully restore the db on Unix machine using the commands which was laid out in teslamate documentation
but the same commands are not working for windows

can someone help me here to restore the db on windows docker setup

What are the errors on Windows? I imagine Docker is running through the CLI on Windows, thus the commands should work.

 

[rsunny2012]

What are the errors on Windows? I imagine Docker is running through the CLI on Windows, thus the commands should work.

Below is the error while executing the restore command

C:\teslamate>docker-compose stop teslamate
[+] Stopping 1/1
✔ Container teslamate-teslamate-1 Stopped 2.6s

C:\teslamate>docker-compose exec -T database psql -U teslamate << .
<< was unexpected at this time.

C:\teslamate>drop schema public cascade;
'drop' is not recognized as an internal or external command,
operable program or batch file.

C:\teslamate>create schema public;
'create' is not recognized as an internal or external command,
operable program or batch file.

C:\teslamate>create extension cube;
'create' is not recognized as an internal or external command,
operable program or batch file.

C:\teslamate>create extension earthdistance;
'create' is not recognized as an internal or external command,
operable program or batch file.

C:\teslamate>CREATE OR REPLACE FUNCTION public.ll_to_earth(float8, float8)
'CREATE' is not recognized as an internal or external command,
operable program or batch file.

C:\teslamate> RETURNS public.earth
'RETURNS' is not recognized as an internal or external command,
operable program or batch file.

C:\teslamate> LANGUAGE SQL
'LANGUAGE' is not recognized as an internal or external command,
operable program or batch file.

C:\teslamate> IMMUTABLE STRICT
'IMMUTABLE' is not recognized as an internal or external command,
operable program or batch file.

C:\teslamate> PARALLEL SAFE
'PARALLEL' is not recognized as an internal or external command,
operable program or batch file.

C:\teslamate> AS 'SELECT public.cube(public.cube(public.cube(public.earth()*cos(radians(\$1))*cos(radians(\$2))),public.earth()*cos(radians(\$1))*sin(radians(\$2))),public.earth()*sin(radians(\$1))):ublic.earth';
'AS' is not recognized as an internal or external command,
operable program or batch file.

C:\teslamate>.
'.' is not recognized as an internal or external command,
operable program or batch file.

C:\teslamate>

 

[cwanja]

Windows is breaking your restore command into individual lines. Not sure if the dot works for Windows. Give this a try.

docker compose exec -T database psql -U teslamate teslamate << drop schema public cascade; create schema public; create extension cube; create extension earthdistance; CREATE OR REPLACE FUNCTION public.ll_to_earth(float8, float8) RETURNS public.earth LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE AS 'SELECT public.cube(public.cube(public.cube(public.earth()*cos(radians(\$1))*cos(radians(\$2))),public.earth()*cos(radians(\$1))*sin(radians(\$2))),public.earth()*sin(radians(\$1)))::public.earth';

Make sure it is on a single line.

 

[rsunny2012]

Windows is breaking your restore command into individual lines. Not sure if the dot works for Windows. Give this a try.

docker compose exec -T database psql -U teslamate teslamate << drop schema public cascade; create schema public; create extension cube; create extension earthdistance; CREATE OR REPLACE FUNCTION public.ll_to_earth(float8, float8) RETURNS public.earth LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE AS 'SELECT public.cube(public.cube(public.cube(public.earth()*cos(radians(\$1))*cos(radians(\$2))),public.earth()*cos(radians(\$1))*sin(radians(\$2))),public.earth()*sin(radians(\$1)))::public.earth';

Make sure it is on a single line.

Thanks for the response. I tried this but getting the below error

C:\teslamate>docker compose exec -T database psql -U teslamate teslamate << drop schema public cascade; create schema public; create extension cube; create extension earthdistance; CREATE OR REPLACE FUNCTION public.ll_to_earth(float8, float8) RETURNS public.earth LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE AS 'SELECT public.cube(public.cube(public.cube(public.earth()*cos(radians(\$1))*cos(radians(\$2))),public.earth()*cos(radians(\$1))*sin(radians(\$2))),public.earth()*sin(radians(\$1))):ublic.earth';
<< was unexpected at this time.
C:\teslamate>

 

[cwanja]

Thanks for the response. I tried this but getting the below error

C:\teslamate>docker compose exec -T database psql -U teslamate teslamate << drop schema public cascade; create schema public; create extension cube; create extension earthdistance; CREATE OR REPLACE FUNCTION public.ll_to_earth(float8, float8) RETURNS public.earth LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE AS 'SELECT public.cube(public.cube(public.cube(public.earth()*cos(radians(\$1))*cos(radians(\$2))),public.earth()*cos(radians(\$1))*sin(radians(\$2))),public.earth()*sin(radians(\$1))):ublic.earth';
<< was unexpected at this time.
C:\teslamate>

Pretty much reached my experience with Windows CLI. I will have to research more about how Docker on Windows can take in the commands after the less than symbol. Until then, if someone else has ideas please post them

 

[MrFixit1094]

Until then, if someone else has ideas please post them

Windows does not understand the '<<' operator as that is strictly a Linux feature.
Your first attempt crashed because the initial command 'C:\teslamate>docker-compose exec -T database psql -U teslamate <<' failed due to the '<<'

I would try again, only leave out the '<<'. I think the subsequent commands may work (they are commands to docker, not Windows). I am not sure about the semicolons... You might not want them.

 

[init6]

I'd log in to the database manually and then run the drop etc commands one by one.

ChatGPT suggests the following for importing the backup:

ChatGPT
To restore a backup created using pg_dump in a Docker container running PostgreSQL on Windows, you can follow these steps:

1. Copy the Backup File to the Docker Container: You need to copy the backup file (usually with a .sql extension) into the Docker container. You can use the docker cp command to achieve this. Assuming your backup file is named backup.sql and your PostgreSQL container is named postgres_container, you would run:
   
   docker cp backup.sql postgres_container:/backup.sql
2. Access the Docker Container's Shell: You need to access the shell of the PostgreSQL Docker container to run pg_restore inside it. You can do this using the docker exec -it command:
   
   docker exec -it postgres_container bash
   Replace postgres_container with the name or ID of your PostgreSQL Docker container.
3. Restore the Backup using pg_restore: Once you're inside the container's shell, you can use pg_restore to restore the backup file. Assuming you have a PostgreSQL database named my_database, you would run:
   
   pg_restore -U postgres -d my_database /backup.sql
   Replace my_database with the name of your target database and /backup.sql with the path to your backup file inside the container.
4. Exit the Container's Shell: After the restoration is complete, you can exit the container's shell by typing exit and pressing Enter.

That's it! Your backup should now be restored into your PostgreSQL database running in a Docker container on Windows. Make sure to replace the placeholder values (e.g., postgres_container, my_database, backup.sql) with your actual container name, database name, and backup file path.

 

[cwanja]

Thank you @init6 and @MrFixit1094 (ironic name in this situation). @rsunny2012 give it a go.

 

[rsunny2012]

Windows does not understand the '<<' operator as that is strictly a Linux feature.
Your first attempt crashed because the initial command 'C:\teslamate>docker-compose exec -T database psql -U teslamate <<' failed due to the '<<'

I would try again, only leave out the '<<'. I think the subsequent commands may work (they are commands to docker, not Windows). I am not sure about the semicolons... You might not want them.

i tried without << . it connected me to database . but command will fail at the below step

(public.cube(public.cube(public.earth()*cos(radians(\$1))*cos(radians(\$2))),public.earth()*cos(radians(\$1))*sin(radians(\$2))),public.earth()*sin(radians(\$1))):ublic.earth';

 

[MrFixit1094]

i tried without << . it connected me to database . but command will fail at the below step

So, all of the other commands worked OK, and it only failed on the last one?

That command is actually AS 'SELECT public.cube etc...

When you said it failed, it looks like maybe you didn't have the AS ' SELECT as part of that [rather complicated] command?

 

[rsunny2012]

Quote

So, all of the other commands worked OK, and it only failed on the last one?

That command is actually AS 'SELECT public.cube etc...

When you said it failed, it looks like maybe you didn't have the AS ' SELECT as part of that [rather complicated] command?

This is the issue i am facing

C:\teslamate>docker-compose exec database psql teslamate teslamate
psql (15.6 (Debian 15.6-1.pgdg120+2))
Type "help" for help.

teslamate=# drop schema public cascade;
create schema public;
create extension cube;
create extension earthdistance;
CREATE OR REPLACE FUNCTION public.ll_to_earth(float8, float8)
RETURNS public.earth
LANGUAGE SQL
IMMUTABLE STRICT
PARALLEL SAFE
AS 'SELECT public.cube(public.cube(public.cube(public.earth()*cos(radians(\$1))*cos(radians(\$2))),public.earth()*cos(radians(\$1))*sin(radians(\$2))),public.earth()*sin(radians(\$1))):ublic.earth';
NOTICE: drop cascades to 25 other objects
DETAIL: drop cascades to extension cube
drop cascades to extension earthdistance
drop cascades to table schema_migrations
drop cascades to table cars
drop cascades to table drives
drop cascades to table positions
drop cascades to type states_status
drop cascades to table states
drop cascades to table charging_processes
drop cascades to table charges
drop cascades to table updates
drop cascades to table addresses
drop cascades to table tokens
drop cascades to table settings
drop cascades to type unit_of_length
drop cascades to type unit_of_temperature
drop cascades to table geofences
drop cascades to function convert_m(double precision,text)
drop cascades to type range
drop cascades to table car_settings
drop cascades to type billing_type
drop cascades to function convert_km(numeric,text)
drop cascades to function convert_celsius(numeric,text)
drop cascades to function convert_tire_pressure(numeric,character varying)
drop cascades to type unit_of_pressure
DROP SCHEMA
CREATE SCHEMA
CREATE EXTENSION
CREATE EXTENSION
ERROR: syntax error at or near "\"
LINE 1: ...ublic.cube(public.cube(public.earth()*cos(radians(\$1))*cos(...
^
QUERY: SELECT public.cube(public.cube(public.cube(public.earth()*cos(radians(\$1))*cos(radians(\$2))),public.earth()*cos(radians(\$1))*sin(radians(\$2))),public.earth()*sin(radians(\$1))):ublic.earth
teslamate=#

 

[init6]

Try it without the \ before the $1,$2 etc.

I think the \ is just to prevent the shell from reading the $1 as a parameter.

 

[MrFixit1094]

Try it without the \ before the $1,$2 etc

This is the issue i am facing

It seems like you are very close !

 

[shyboy]

Can someone who has already done, provide the sql statements for which tables I need to create records for missing charge and missing drives.
I believe its more than one table.

if you don't have sql statements, dropping just the table names would be helpful as well.

I had power outage and teslamate was down for few days and I am missing a charge and few drives.

 

[rsunny2012]

It seems like you are very close !

I guess it worked. can you please check the code and let me know whether i got all the data restored ?

C:\teslamate>docker-compose exec database psql teslamate teslamate
psql (15.6 (Debian 15.6-1.pgdg120+2))
Type "help" for help.

teslamate=# drop schema public cascade;
create schema public;
create extension cube;
create extension earthdistance;
CREATE OR REPLACE FUNCTION public.ll_to_earth(float8, float8)
RETURNS public.earth
LANGUAGE SQL
IMMUTABLE STRICT
PARALLEL SAFE
AS 'SELECT public.cube(public.cube(public.cube(public.earth()*cos(radians($1))*cos(radians($2))),public.earth()*cos(radians($1))*sin(radians($2))),public.earth()*sin(radians($1))):ublic.earth';
.
NOTICE: drop cascades to 25 other objects
DETAIL: drop cascades to table schema_migrations
drop cascades to table cars
drop cascades to table drives
drop cascades to table positions
drop cascades to type states_status
drop cascades to table states
drop cascades to table charging_processes
drop cascades to table charges
drop cascades to table updates
drop cascades to table addresses
drop cascades to table tokens
drop cascades to table settings
drop cascades to type unit_of_length
drop cascades to type unit_of_temperature
drop cascades to table geofences
drop cascades to function convert_m(double precision,text)
drop cascades to type range
drop cascades to extension cube
drop cascades to extension earthdistance
drop cascades to table car_settings
drop cascades to type billing_type
drop cascades to function convert_km(numeric,text)
drop cascades to function convert_celsius(numeric,text)
drop cascades to function convert_tire_pressure(numeric,character varying)
drop cascades to type unit_of_pressure
DROP SCHEMA
CREATE SCHEMA
CREATE EXTENSION
CREATE EXTENSION
CREATE FUNCTION
teslamate-# quit
Use \q to quit.
teslamate-# \q

C:\teslamate>docker-compose stop teslamate
[+] Stopping 1/1
✔ Container teslamate-teslamate-1 Stopped 1.4s

C:\teslamate>docker-compose exec -T database psql -U teslamate -d teslamate < teslamate.bck
SET
SET
SET
SET
SET
set_config
------------

(1 row)

SET
SET
SET
SET
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE TYPE
ALTER TYPE
CREATE TYPE
ALTER TYPE
CREATE TYPE
ALTER TYPE
CREATE TYPE
ALTER TYPE
CREATE TYPE
ALTER TYPE
CREATE TYPE
ALTER TYPE
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
COPY 1873
COPY 2
COPY 2
COPY 382144
COPY 893
COPY 10505
COPY 943
COPY 16205395
COPY 90
COPY 1
COPY 13651
COPY 1
COPY 50
setval
--------
1873
(1 row)

setval
--------
2
(1 row)

setval
--------
2
(1 row)

setval
--------
383748
(1 row)

setval
--------
893
(1 row)

setval
--------
944
(1 row)

setval
----------
16207034
(1 row)

setval
--------
1
(1 row)

setval
--------
15477
(1 row)

setval
--------
1
(1 row)

setval
--------
12581
(1 row)

setval
--------
50
(1 row)

ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

C:\teslamate>docker-compose start teslamate
[+] Running 1/1
✔ Container teslamate-teslamate-1 Started 0.3s

C:\teslamate>

 

[init6]

We can't see the data, so can't confirm it's ok. Would be much easier to check Grafana

 

[rsunny2012]

yes i can see the data under grafana

 

[init6]

Can someone who has already done, provide the sql statements for which tables I need to create records for missing charge and missing drives.
I believe its more than one table.

if you don't have sql statements, dropping just the table names would be helpful as well.

I had power outage and teslamate was down for few days and I am missing a charge and few drives.

If teslamate wasn't connected to the Tesla servers for a period of time then you can't re-create the drives/charges. It records in real time.
A drive is a summary of the positions table *. so while technically you could add in a drive to the drives table it would be pretty meaning less as you wouldn't have any of the data for that drive such as average speed , consumption, etc.
Ditto for charges.

* forgot to add this bit to see what tables are in the database run this from the sql prompt:

\dt

 

[Kietrax]

There's already two discussions about this:

Seeing offline rather than sleep after updating to 2024.8.4 and Teslamate version 1.28.4 · teslamate-org teslamate · Discussion #3753

Hello dev & user community. This teslamate open source project is a marvellous effort and I appreciate it very much. Been using it for 2 years. I can't figure out by myself what is going on and nee...

github.com

After updating to 2024.8.4 all sleeps are marked as off-line. · teslamate-org teslamate · Discussion #3751

I'm running the 3672 preview on a model year 2022 Model Y in the US. After updating to 2024.8.4 on 18-Mar-2024 (see image below) all sleeps are marked as off-line (see image below). I'm running the...

github.com

I am seeing this behaviour with TeslaMate 1.28.5 so was reading through the above discussions. Looks like the first one listed has been closed as answered but subsequent comments indicate that 1.28.0 and 1.28.5 differ in behaviour for this (1.28.0 showing sleep as expected, 1.28.5 not) so wondered if this was going to be looked at any further or if we just have to live with the "sleep reported as offline" behaviour until there is more clarity please?

 

[init6]

You'd best ask that question on the Github page. That's the 'official' home of teslamate. This thread is just a subset of teslamate users.