ERROR: multiple primary keys...

[SylvainGa]

Hi, I'm upgrading my Teslamate RaspberryPi from Bookworm to Trixie. Teslamate on Bookworm has been running Teslamate 2.2.0 with Postgres 18 no problem since last week (was at 2.0.0 before with Postgres 17).

I reinstalled Docker, Teslamate and while restoring the Teslamate backups, I'm seeing these:
ERROR: schema "private" already exists
ERROR: relation "tokens" already exists
ERROR: relation "tokens_id_seq" already exists
ERROR: multiple primary keys for table "tokens" are not allowed

mixed, with the usual
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
...

Is that a cause for concerned? The restore hasn't finished yet (4+ years worth of data takes a while to restore lol)

I upgraded to a new SD card so I can easily return back to the old Bookworm system if needed...

[SylvainGa]

Well, the restore has finished and after a brief look at the dashboard, everything seems to be there. Again, should these errors while restoring be a cause for alarm?

[JakobLichterfeld]

Looks a bit similar to #5035.

Seems like the migration already created the tables before your restore. I can only assume you didn't follow the steps 1by 1.

[SylvainGa]

Hi, thanks for taking the time to respond. "follow the steps 1by 1" for what? The restore or the migration? I did follow the restore steps one by one (there are not many steps) and as for the migration, see here, and just like I mentioned, it wasn't clear about the order of operations and I had to assume how to proceed. It could be clearer IMHO.

As for #5035, what should I do? Unlike them, my system loads fine and new drives and charges are being recorded. Treat it as cosmetic or does it need database alterations?

[JakobLichterfeld]

Not sure what could be clearer here?
https://docs.teslamate.org/docs/maintenance/upgrading_postgres
https://docs.teslamate.org/docs/maintenance/backup_restore#restore

PRs are welcome. Order is advised in Changelog, so upgrade postgresql and then upgrade TeslaMate. Thats what you did, right?

[SylvainGa]

Those are fine never had an issue with the upgrade before by following the procedure. It's the release notes of 2.2.0 that created confusion for me, especially these sentences "We use the latest dependencies and support PostgreSQL 18" and "Volume mounts have changed in PostgreSQL18".

So does that mean that V2.2.0 only supports PostgreSQL 18 since you use the latest dependencies and the volume mounts have changed? Is PostgreSQL 17 still supported in V2.2.0? Will PostgreSQL 18 work with version prior to V2.2.0? So when do we upgrade to PostgreSQL 18? Before the upgrade to V2.2.0 or after?

That line in the docker configuration also brought some confusion about losing data if misconfigured:

Important Note: (for PostgreSQL 17 and below) Mount the data volume at /var/lib/postgresql/data and not at /var/lib/postgresql because mounts at the latter path WILL NOT PERSIST database data when the container is re-created.

Hence why I mention the update path wasn't clear. I made a post about it earlier and added what I did (see #5107 (comment))

[JakobLichterfeld]

Ty for clarification.

So does that mean that V2.2.0 only supports PostgreSQL 18

As 2.2.0 is not a breaking change release according to semver, PostgreSQL 17 is still supported.

Is PostgreSQL 17 still supported in V2.2.0?

Yes, see above.

Will PostgreSQL 18 work with version prior to V2.2.0?

No, that's why it's clearly stated in v2.2.0

So when do we upgrade to PostgreSQL 18?

You can't before upgrading, but this could be stated clearer.

brought some confusion about losing data if misconfigured:

No confusing, misconfiguring it will result in losing the db.

[hmouridsen]

Hi, I'm upgrading my Teslamate RaspberryPi from Bookworm to Trixie. Teslamate on Bookworm has been running Teslamate 2.2.0 with Postgres 18 no problem since last week (was at 2.0.0 before with Postgres 17).

I reinstalled Docker, Teslamate and while restoring the Teslamate backups, I'm seeing these:

ERROR: schema "private" already exists

ERROR: relation "tokens" already exists

ERROR: relation "tokens_id_seq" already exists

ERROR: multiple primary keys for table "tokens" are not allowed

mixed, with the usual

CREATE TABLE

ALTER TABLE

CREATE SEQUENCE

...

Is that a cause for concerned? The restore hasn't finished yet (4+ years worth of data takes a while to restore lol)

I upgraded to a new SD card so I can easily return back to the old Bookworm system if needed...

Hi

Did you find a solution for this?

I've got the exactly same errors during restore, using the official guide

[JakobLichterfeld]

--> #5035

[hmouridsen]

--> #5035

I did run

Drop existing data and reinitialize (Don't forget to replace first teslamate if using different TM_DB_USER)

docker compose exec -T database psql -U teslamate teslamate << .
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
CREATE EXTENSION cube WITH SCHEMA public;
CREATE EXTENSION earthdistance WITH SCHEMA public;
.

I've tried deleting the database before import, and then restoring. It's giving zero errors, however then Teslamate is not responding on port 4000

[brianmay]

As mentioned in the linked discussion, I think you will also have to do:

DROP SCHEMA private CASCADE;

To drop the private schema also. It is possible the instructions are missing this; if so they should be amended.

Based on the error:

ERROR:  schema "private" already exists

The database restore automatically recreates the schema. If so there is no need to manually recreate it.

[hmouridsen]

Thanks a million

I'll try it out...

But what about the other errors - are they related to this as well?

So basically I just have to add a line to the official - like this:

Stop the teslamate container to avoid write conflicts

docker compose stop teslamate

Drop existing data and reinitialize (Don't forget to replace first teslamate if using different TM_DB_USER)

docker compose exec -T database psql -U teslamate teslamate << .
DROP SCHEMA public CASCADE;
DROP SCHEMA private CASCADE;
CREATE SCHEMA public;
CREATE EXTENSION cube WITH SCHEMA public;
CREATE EXTENSION earthdistance WITH SCHEMA public;
.

Restore

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

Restart the teslamate container

docker compose start teslamate

[brianmay]

I believe so, yes.

Basically you already have the private schema before you did the restore. And the tokens and tokens_id_seq are part of the private schema.

So when the backup tries to restore the new private schema from the backup it conflicts with the pre-existing schema.

But we want to replace, not conflict, so we have to delete it beforehand, same as we do with the public schema.

[brianmay]

I submitted a pull request to fix the docs: #5190

[SylvainGa]

Hi, I'm upgrading my Teslamate RaspberryPi from Bookworm to Trixie. Teslamate on Bookworm has been running Teslamate 2.2.0 with Postgres 18 no problem since last week (was at 2.0.0 before with Postgres 17).

I reinstalled Docker, Teslamate and while restoring the Teslamate backups, I'm seeing these:

ERROR: schema "private" already exists

ERROR: relation "tokens" already exists

ERROR: relation "tokens_id_seq" already exists

ERROR: multiple primary keys for table "tokens" are not allowed

mixed, with the usual

CREATE TABLE

ALTER TABLE

CREATE SEQUENCE

...

Is that a cause for concerned? The restore hasn't finished yet (4+ years worth of data takes a while to restore lol)

I upgraded to a new SD card so I can easily return back to the old Bookworm system if needed...

Hi

Did you find a solution for this?

I've got the exactly same errors during restore, using the official guide

For me, after the restore, everything seems fine anyway. I didn't do anything else. It's been a few weeks now and all my drives and charges are accounted for.