Error when restoring backup "invalid input syntax for type timestamp: "2021-"

[ErikSarrazin]

Hello,

First of all I would like to say that I'm a total newbie to Unix, but I've managed to successfully setup a RPi4 running Teslamate, a Unifi Controller, and Pi-Hole (thank god for online guides ;)

Today I decided to try and reinstall my RPi4 from debian version 10 buster to 11 bullseye to be able to run the latest version of Teslamate. I made backup of the teslamate database and also an image of the SD-card before continuing, making a fresh install of Raspberry Pi OS Lite.

I've followed the restore/backup article in Docs to the letter, but I get an error when importing the backup database using this command:

docker-compose exec -T database psql -U teslamate -d teslamate < ./backup/teslamate.bck

ERROR: invalid input syntax for type timestamp: "2021-"
CONTEXT: COPY positions, line 237797, column date: "2021-"

What am I doing wrong? Does it have to do anything with locale setting in Raspberry Pi?

Thank you for helping!

---

pi@raspberrypi:/teslamate $ docker-compose stop teslamate
Stopping teslamate_teslamate_1 ... done
pi@raspberrypi:/teslamate $ docker-compose exec -T database psql -U 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';
.
NOTICE: drop cascades to 23 other objects
DETAIL: drop cascades to extension cube
drop cascades to extension earthdistance
drop cascades to type billing_type
drop cascades to type range
drop cascades to type states_status
drop cascades to type unit_of_length
drop cascades to type unit_of_temperature
drop cascades to function convert_celsius(numeric,text)
drop cascades to function convert_km(numeric,text)
drop cascades to function convert_m(double precision,text)
drop cascades to table addresses
drop cascades to table car_settings
drop cascades to table cars
drop cascades to table charges
drop cascades to table charging_processes
drop cascades to table drives
drop cascades to table geofences
drop cascades to table positions
drop cascades to table schema_migrations
drop cascades to table settings
drop cascades to table states
drop cascades to table tokens
drop cascades to table updates
DROP SCHEMA
CREATE SCHEMA
CREATE EXTENSION
CREATE EXTENSION
CREATE FUNCTION
pi@raspberrypi:~/teslamate $ docker-compose exec -T database psql -U teslamate -d teslamate < ./backup/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 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 276
COPY 1
COPY 1
COPY 69643
COPY 213
COPY 852
COPY 10
ERROR: invalid input syntax for type timestamp: "2021-"
CONTEXT: COPY positions, line 237797, column date: "2021-"

[kazemaru64]

I just had this exact issue. My SD card filled up and TeslaMate just completely blew up on me. I'm rebuilding it and trying to restore the last backup. When I open the very last backup in a text editor, it appears that it's incomplete, so maybe the SD card filled up and it died at that point. I found a different backup that was complete and it worked normally.