Add Backup & Restore docs for manual installations (FreeBSD)

[ivanovd]

Can someone add backup & restore docs for manual installations?

[DrMichael]

With a manual installation it is just:

Backup
pg_dump -U teslamate teslamate > ./teslamate.bck

Restore
psql -U teslamate -d teslamate < teslamate.bck

[ivanovd]

Am I supposed to drop the DB before restoring? I have two instances - one on Azure (docker container) and one local (FreeBSD manual). My local server was down for a few days and I want to sync it with the missing data for these few days. I dumped the data from the Azure instance and on the local one (FreeBSD manual installation) but I am getting errors for existing data and constraints:

root@teslamate:/ # psql -U teslamate -d teslamate < teslamate20250108.bck 
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
ALTER SCHEMA
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
ERROR:  type "billing_type" already exists
ALTER TYPE
ERROR:  type "range" already exists
ALTER TYPE
ERROR:  type "states_status" already exists
ALTER TYPE
ERROR:  type "unit_of_length" already exists
ALTER TYPE
ERROR:  type "unit_of_pressure" already exists
ALTER TYPE
ERROR:  type "unit_of_temperature" already exists
ALTER TYPE
ERROR:  function "convert_celsius" already exists with same argument types
ALTER FUNCTION
ERROR:  function "convert_km" already exists with same argument types
ALTER FUNCTION
ERROR:  function "convert_m" already exists with same argument types
ALTER FUNCTION
ERROR:  function "convert_tire_pressure" already exists with same argument types
ALTER FUNCTION
SET
SET
ERROR:  relation "addresses" already exists
ALTER TABLE
ERROR:  relation "addresses_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR:  relation "car_settings" already exists
ALTER TABLE
ERROR:  relation "car_settings_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR:  relation "cars" already exists
ALTER TABLE
ERROR:  relation "cars_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR:  relation "charges" already exists
ALTER TABLE
ERROR:  relation "charges_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR:  relation "charging_processes" already exists
ALTER TABLE
ERROR:  relation "charging_processes_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR:  relation "drives" already exists
ALTER TABLE
ERROR:  relation "geofences" already exists
ALTER TABLE
ERROR:  relation "geofences_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR:  relation "positions" already exists
ALTER TABLE
ERROR:  relation "positions_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR:  relation "schema_migrations" already exists
ALTER TABLE
ERROR:  relation "settings" already exists
ALTER TABLE
ERROR:  relation "settings_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR:  relation "states" already exists
ALTER TABLE
ERROR:  relation "states_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR:  relation "tokens" already exists
ALTER TABLE
ERROR:  relation "tokens_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR:  relation "trips_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ERROR:  relation "updates" already exists
ALTER TABLE
ERROR:  relation "updates_id_seq" already exists
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
ERROR:  duplicate key value violates unique constraint "addresses_osm_id_osm_type_index"
DETAIL:  Key (osm_id, osm_type)=(221315519, way) already exists.
CONTEXT:  COPY addresses, line 1
ERROR:  duplicate key value violates unique constraint "car_settings_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY car_settings, line 1
ERROR:  duplicate key value violates unique constraint "cars_eid_index"
DETAIL:  Key (eid)=(1493036967800483) already exists.
CONTEXT:  COPY cars, line 1
ERROR:  duplicate key value violates unique constraint "charges_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY charges, line 1
ERROR:  duplicate key value violates unique constraint "charging_processes_pkey"
DETAIL:  Key (id)=(25) already exists.
CONTEXT:  COPY charging_processes, line 1
ERROR:  duplicate key value violates unique constraint "trips_pkey"
DETAIL:  Key (id)=(924) already exists.
CONTEXT:  COPY drives, line 1
ERROR:  duplicate key value violates unique constraint "geofences_pkey"
DETAIL:  Key (id)=(4) already exists.
CONTEXT:  COPY geofences, line 1
ERROR:  duplicate key value violates unique constraint "positions_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY positions, line 1
ERROR:  duplicate key value violates unique constraint "schema_migrations_pkey"
DETAIL:  Key (version)=(20190330150000) already exists.
CONTEXT:  COPY schema_migrations, line 1
ERROR:  duplicate key value violates unique constraint "settings_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY settings, line 1
ERROR:  duplicate key value violates unique constraint "states_pkey"
DETAIL:  Key (id)=(62) already exists.
CONTEXT:  COPY states, line 1
ERROR:  duplicate key value violates unique constraint "tokens_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY tokens, line 1
ERROR:  duplicate key value violates unique constraint "updates_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY updates, line 1
 setval 
--------
    630
(1 row)

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

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

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

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

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

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

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

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

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

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

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

ERROR:  multiple primary keys for table "addresses" are not allowed
ERROR:  multiple primary keys for table "car_settings" are not allowed
ERROR:  multiple primary keys for table "cars" are not allowed
ERROR:  multiple primary keys for table "charges" are not allowed
ERROR:  multiple primary keys for table "charging_processes" are not allowed
ERROR:  multiple primary keys for table "geofences" are not allowed
ERROR:  multiple primary keys for table "positions" are not allowed
ERROR:  multiple primary keys for table "schema_migrations" are not allowed
ERROR:  multiple primary keys for table "settings" are not allowed
ERROR:  multiple primary keys for table "states" are not allowed
ERROR:  multiple primary keys for table "tokens" are not allowed
ERROR:  multiple primary keys for table "drives" are not allowed
ERROR:  multiple primary keys for table "updates" are not allowed
ERROR:  relation "addresses_osm_id_osm_type_index" already exists
ERROR:  relation "cars_eid_index" already exists
ERROR:  relation "cars_settings_id_index" already exists
ERROR:  relation "cars_vid_index" already exists
ERROR:  relation "cars_vin_index" already exists
ERROR:  relation "charges_charging_process_id_index" already exists
ERROR:  relation "charges_date_index" already exists
ERROR:  relation "charging_processes_address_id_index" already exists
ERROR:  relation "charging_processes_car_id_index" already exists
ERROR:  relation "charging_processes_position_id_index" already exists
ERROR:  relation "drives_end_geofence_id_index" already exists
ERROR:  relation "drives_end_position_id_index" already exists
ERROR:  relation "drives_start_geofence_id_index" already exists
ERROR:  relation "drives_start_position_id_index" already exists
CREATE INDEX
ERROR:  relation "positions_car_id_index" already exists
ERROR:  relation "positions_date_index" already exists
ERROR:  relation "positions_drive_id_date_index" already exists
ERROR:  relation "states_car_id__end_date_IS_NULL_index" already exists
ERROR:  relation "states_car_id_index" already exists
ERROR:  relation "trips_car_id_index" already exists
ERROR:  relation "trips_end_address_id_index" already exists
ERROR:  relation "trips_start_address_id_index" already exists
ERROR:  relation "updates_car_id_index" already exists
ERROR:  constraint "cars_settings_id_fkey" for relation "cars" already exists
ERROR:  constraint "charges_charging_process_id_fkey" for relation "charges" already exists
ERROR:  constraint "charging_processes_address_id_fkey" for relation "charging_processes" already exists
ERROR:  constraint "charging_processes_car_id_fkey" for relation "charging_processes" already exists
ERROR:  constraint "charging_processes_geofence_id_fkey" for relation "charging_processes" already exists
ERROR:  constraint "charging_processes_position_id_fkey" for relation "charging_processes" already exists
ERROR:  constraint "drives_car_id_fkey" for relation "drives" already exists
ERROR:  constraint "drives_end_address_id_fkey" for relation "drives" already exists
ERROR:  constraint "drives_end_geofence_id_fkey" for relation "drives" already exists
ERROR:  constraint "drives_end_position_id_fkey" for relation "drives" already exists
ERROR:  constraint "drives_start_address_id_fkey" for relation "drives" already exists
ERROR:  constraint "drives_start_geofence_id_fkey" for relation "drives" already exists
ERROR:  constraint "drives_start_position_id_fkey" for relation "drives" already exists
ERROR:  constraint "positions_car_id_fkey" for relation "positions" already exists
ERROR:  constraint "positions_drive_id_fkey" for relation "positions" already exists
ERROR:  constraint "states_car_id_fkey" for relation "states" already exists
ERROR:  constraint "updates_car_id_fkey" for relation "updates" already exists
REVOKE
root@teslamate:/ # Killed

[ivanovd]

Yep, you are right. I will still create a PR to add that to the docs so it is consistent with the install and upgrade docs.

[tpd-opitz]

Still we ( @ivanovd and me) need a procedure to merge the content of two teslamate instances...
see here