database "teslamate" has a collation version mismatch - after upgrading debian 12 to 13

[kongomongo]

Is there an existing issue for this?

• I have checked the existing issues and discussions, and I can confirm that there are no duplicates.

What happened?

Hi there, after upgrading from debian 12 to 13 I had the OOM repeatedly kill beam.smp and started investigating. I am running 3 instances of teslamate so I thought maybe its time to increase the 2GB RAM to 4DB. didnt help. So i stopped all instances except one.

Now I am seeing this in my log:

database-1 | 2025-08-18 11:57:57.244 UTC [77] WARNING: database "teslamate" has a collation version mismatch
database-1 | 2025-08-18 11:57:57.244 UTC [77] DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.41.
database-1 | 2025-08-18 11:57:57.244 UTC [77] HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE teslamate REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

I was running the latest 1.x of teslamate (1.33.0), upgraded that to 2.1.1. Didnt help.

I am seeing no other issues so asking here.

This might be related?
#3583

Expected Behavior

No response

Can the issue reliably be reproduced?

Yes

Steps To Reproduce the issue

No response

Relevant log output

database-1   | 2025-08-18 11:57:57.244 UTC [77] WARNING:  database "teslamate" has a collation version mismatch
database-1   | 2025-08-18 11:57:57.244 UTC [77] DETAIL:  The database was created using collation version 2.36, but the operating system provides version 2.41.
database-1   | 2025-08-18 11:57:57.244 UTC [77] HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE teslamate REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

Screenshots

No response

Additional data

No response

Type of installation

Docker (https://docs.teslamate.org/docs/installation/docker/)

Version

2.1.1

PostgreSQL version

postgresql-16/now 16.10-1.pgdg13+1

Are you running latest major supported PostgreSQL version?

• I run the latest major supported PostgreSQL version

Are you using a reverse Proxy for TeslaMate?

No

Details about your reverse Proxy if applicable

No response

[kongomongo]

seems like that was causing the memory usage:

#3045 (reply in thread)

was using 2.2GB in the teslamate container, after the "fix"

services:
  teslamate:
    **ulimits:
      nofile: 65536**

only 10% -> down to 220MB RAM!

[kongomongo]

I really think that should be put somewhere in a knowledge base or release notes, unfortunately I don't exactly know what upgrade caused this going from debian 12 to 13

[kongomongo]

as to the initial reason of the issue: the warning messages from postgresql seem to have vanished after the docker containers could start without being killed by OOM

[JakobLichterfeld]

Thanks for your report and your deep diving!

This seems to be a duplicate of #3717

When using correct upgrade process of PostgreSQL this couldn't be reproduced on any system. If not, you need to include this: #3717 (comment)

[accolon]

The collation version changed between Postgres 17 on Bookworm vs. Postgres 17 on Trixie.

This also affects TeslaMate Docker setups, because the image tag postgres:17 used in the docs is now an alias for postgres:17-trixie.

I posted about this in the TFF support thread (sorry, German only) shortly before finding this issue (which was not there yet when I researched the issue during my lunch break 😉).

I don't know if the collation change causes problems, but it can easily be fixed either by following the instructions from the HINT or by "upgrading" Postgres (from 17.6 on Bookworm to 17.6 on Trixie) according to TeslaMate's docs.

EDIT -- to elaborate on this a little more:

If the user follows the standard steps for upgrading, he doesn't expect the necessity for a database upgrade since the Postgres version effectively stays the same ("17" according to the docs) -- the changes are caused by the Docker base image switch from Bookworm to Trixie.

[JakobLichterfeld]

@swiffer What do you think? Can we use a migration in TeslaMate to update collation like ALTER DATABASE teslamate REFRESH COLLATION VERSION" (#3717 (comment))

Just stick with it (not preferred, imo). Or update our image reference (but current users (24M docker pulls) will be affected anyway, as they do not update their docker-compose.yml).

We are at the point where I really consider the idea from @parkr to ship an own PostgreSQL container like we do with Grafana to avoid such issues (see discussion here and here).

Edit: I assume it will be solved when we use debian:trixie-slimas base image for TeslaMate container, see #4889

[swiffer]

As per: docker-library/postgres#1356 (comment)

I suggest to pin to 17-trixie in the docs for new installs now and stay away from publishing our own container.

Leaves open the question on what to do for existing users...

As it's a warning only we could stay as is for now and pin this issue suggesting to do a full backup & restore, for those who notice the warning.

Once postgres 18 is out we could enforce usage of 18-trixie and fail to start if pg version is below pg 18?

Alternative would be extending our db compatibility check and avoiding to start as we do if pg min version req is not met.

[kimkatgh]

Edit: I got the post from @accolon backwards and I initially posted nonsense related to Ubuntu 24.04 LTS.

For someone looking for exact steps, here is my process for correcting the issue was (probably some unnecessary steps related to stopping containers):

$ docker compose down
$ docker compose up -d database
$ docker compose exec -T database psql -U teslamate -d teslamate -c "REINDEX DATABASE teslamate;"
$ docker compose exec -T database psql -U teslamate -d teslamate -c "ALTER DATABASE postgres REFRESH COLLATION VERSION;"
$ docker compose exec -T database psql -U teslamate -d teslamate -c "ALTER DATABASE teslamate REFRESH COLLATION VERSION;"
$ docker compose exec -T database psql -U teslamate -d teslamate -c "ALTER DATABASE template1 REFRESH COLLATION VERSION;"
$ docker compose logs database
$ docker compose down
$ docker compose up -d

Took backups before and after.

The question remains, were the collation rules of Postgres changed so that it affects the Teslamate DB? As far as I understand, these steps only remove the warning but as the db wasn't rebuilt there may (not likely) be some weird effects.

[accolon]

The question remains, were the collation rules of Postgres changed so that it affects the Teslamate DB? As far as I understand, these steps only remove the warning but as the db wasn't rebuilt there may (not likely) be some weird effects.

The ALTER DATABASE commands actually change the collation version which should suffice.

You can check the correct outcome yourself:
docker compose exec -it database psql -U teslamate

followed by:

teslamate=# SELECT collname, collversion
FROM pg_database, pg_collation
WHERE datcollate = collname AND datname = current_database();

which should give the now correct collation version (after executing the ALTER commands):

  collname  | collversion 
------------+-------------
 en_US.utf8 | 2.41
(1 row)

[thunder2k]

Edit: I got the post from @accolon backwards and I initially posted nonsense related to Ubuntu 24.04 LTS.

For someone looking for exact steps, here is my process for correcting the issue was (probably some unnecessary steps related to stopping containers):

$ docker compose down
$ docker compose up -d database
$ docker compose exec -T database psql -U teslamate -d teslamate -c "REINDEX DATABASE teslamate;"
$ docker compose exec -T database psql -U teslamate -d teslamate -c "ALTER DATABASE postgres REFRESH COLLATION VERSION;"
$ docker compose exec -T database psql -U teslamate -d teslamate -c "ALTER DATABASE teslamate REFRESH COLLATION VERSION;"
$ docker compose exec -T database psql -U teslamate -d teslamate -c "ALTER DATABASE template1 REFRESH COLLATION VERSION;"
$ docker compose logs database
$ docker compose down
$ docker compose up -d

Took backups before and after.

The question remains, were the collation rules of Postgres changed so that it affects the Teslamate DB? As far as I understand, these steps only remove the warning but as the db wasn't rebuilt there may (not likely) be some weird effects.

Hi,
This didn't fix it for me, as after running teslamate=# SELECT collname, collversion
FROM pg_database, pg_collation
WHERE datcollate = collname AND datname = current_database();, I got the following:

collname | collversion
------------+-------------
en_US.utf8 | 2.36
(1 row)

Any hints?

Thanks!