Db upgrade v15 to v17 - unable to upgrade to tcp, received 409

[sjayanna01]

My teslamate is setup on portainer, things were working fine with db version 15.

Followed backup instructions, generated bkp file.
Updated docker compose db version.

Facing 'unable to upgrade to tcp, received 409' while executing the below command to restore.

docker exec teslamate-db sh -c 'psql -U teslamate teslamate' << .
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
CREATE EXTENSION cube WITH SCHEMA public;
CREATE EXTENSION earthdistance WITH SCHEMA public;
.

Docker log shows this:
"FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 15, which is not compatible with this version 17.0 (Debian 17.0-1.pgdg120+1)."

Please help, thanks in advance!

[cwanja]

Did you delete the database volume? https://docs.teslamate.org/docs/maintenance/upgrading_postgres/

[BluSpanner]

Hi, It looks like you forgot to delete the database volume. Did you complete step 3? https://docs.teslamate.org/docs/maintenance/upgrading_postgres

…

On Fri, 8 Nov 2024 at 16:12 Shreevathsa Jayanna ***@***.***> wrote: My teslamate is setup on portainer, things were working fine with db version 15. Followed backup instructions, generated bkp file. Updated docker compose db version. Facing 'unable to upgrade to tcp, received 409' while executing the below command to restore. docker exec teslamate-db sh -c 'psql -U teslamate teslamate' << . DROP SCHEMA public CASCADE; CREATE SCHEMA public; CREATE EXTENSION cube WITH SCHEMA public; CREATE EXTENSION earthdistance WITH SCHEMA public; . Docker log shows this: "FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 15, which is not compatible with this version 17.0 (Debian 17.0-1.pgdg120+1)." Please help, thanks in advance! — Reply to this email directly, view it on GitHub <#4363>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHYNAYBF3TMCX3DG2UCKQDTZ7QTY5AVCNFSM6AAAAABRMSGBYCVHI2DSMVQWIX3LMV43ERDJONRXK43TNFXW4OZXGQ2DGMBRHE> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[sjayanna01]

I tried, but docker volume ls or docker volume ls -qf dangling=true doesn't show any volumes related to teslamate or orphaned. How do i find it if its still lurking around somewhere in the background?

[BluSpanner]

Hi, Remove the *_teslamate-db volume, with the following command docker volume rm "$(basename "$PWD")_teslamate-db"

…

On Sat, 9 Nov 2024 at 10:01 Shreevathsa Jayanna ***@***.***> wrote: I tried, but docker volume ls or docker volume ls -qf dangling=true doesn't show any volumes related to teslamate or orphaned. How do i find it if its still lurking around somewhere in the background? — Reply to this email directly, view it on GitHub <#4363 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHYNAYELD2PODXUYI5KQ3SLZ7URD5AVCNFSM6AAAAABRMSGBYCVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTCMJZGMZTENI> . You are receiving this because you commented.Message ID: ***@***.*** com>

[sjayanna01]

Thanks @BluSpanner , got below response.

docker volume rm "$(basename "$PWD")_teslamate-db"
Error: No such volume: username_teslamate-db

[sjayanna01]

This is my docker compose part for teslamate-db

  database:
    image: postgres:17
    container_name: teslamate-db
    restart: on-failure:5
    environment:
      - POSTGRES_USER=teslamate
      - POSTGRES_PASSWORD=sdgljadhgweriuowvxvmn
      - POSTGRES_DB=teslamate
    volumes:
      - /volume1/docker/teslamate/teslamate-db:/var/lib/postgresql/data:rw
    networks:
      - caddy_network

Does manually deleting everything inside teslamate-db folder help?

[BluSpanner]

What does the following show? *docker volume ls* Find the volume that ends in -db, and remove it.

…

On Sat, 9 Nov 2024 at 11:44 Shreevathsa Jayanna ***@***.***> wrote: Thanks @BluSpanner <https://github.com/BluSpanner> , got below response. docker volume rm "$(basename "$PWD")_teslamate-db" Error: No such volume: _teslamate-db — Reply to this email directly, view it on GitHub <#4363 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHYNAYGRD4FWPDJV3X55SDDZ7U5E7AVCNFSM6AAAAABRMSGBYCVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTCMJZGM3TQOI> . You are receiving this because you were mentioned.Message ID: ***@***.*** com>

[sjayanna01]

Nothing related.

$ docker volume ls
DRIVER    VOLUME NAME
local     186fe031a9f9a77739628a7d6e73f414bc88163f228aa067ef173411b74054b5
local     immich_immich_cache
local     immich_immich_postgres
local     immich_immich_upload

[BluSpanner]

Yes. Either delete everything inside the database volume OR delete the database volume, so that it gets re-created when the container restarts.

…

On Sat, 9 Nov 2024 at 11:52 Shreevathsa Jayanna ***@***.***> wrote: This is my docker compose part for teslamate-db database: image: postgres:17 container_name: teslamate-db restart: on-failure:5 environment: - POSTGRES_USER=teslamate - POSTGRES_PASSWORD=sdgljadhgweriuowvxvmn - POSTGRES_DB=teslamate volumes: - /volume1/docker/teslamate/teslamate-db:/var/lib/postgresql/data:rw networks: - caddy_network Does manually deleting everything inside teslamate-db folder help? — Reply to this email directly, view it on GitHub <#4363 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHYNAYDKXXBXBWVGBERXPP3Z7U6ETAVCNFSM6AAAAABRMSGBYCVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTCMJZGM4DENI> . You are receiving this because you were mentioned.Message ID: ***@***.*** com>

[sjayanna01]

Thanks to @BluSpanner for your responses.
Alright here is what I finally did and works on Synology and portainer. I guess it's similar to any other docker setup.

1. If you can't find the teslamate-db volume:
   1.1. Ensure you have followed the backup guidelines and backed up the db. Verify and keep the file safe!
   Synology SSH: docker exec -it <CONTAINER_NAME> pg_dump -U teslamate teslamate > /<YOUR_PATH>/teslamate.bck
   1.2. Navigate to your teslamate-db folder, delete everything (don't panic as long as your bkp is safe!)
   1.3 Update teslamate-db.xx in your docker file to the desired postgresql version. Redeploy the stack, select repull images.

2. Execute the restore commands, I had to slightly modify for synology docker compatibility.
   2.1. Ensure the teslmate-db container is up.
   2.2. # Drop existing data and reinitialize (Don't forget to replace first teslamate if using different TM_DB_USER)
   # Moved the DB statements into a temporary sql file for each of execution (optional).
   docker exec -i teslamate-db psql -U teslamate teslamate -f /tmp/tsrestorecommands.sql
   2.3. # Restore
   docker exec -i teslamate-db psql -U teslamate teslamate < teslamate.bck

IMPORTANT! : You should see some logs after executing each step, if not then something didn't go well.

3. Restart the teslamate-db container.
   Navigate to your grafana and teslamate and ensure everything works and your data is restored.

4. Delete the backup file and any other temp files created if you no longer need them.

Cheers!