collation version mismatch

[GaPhi]

Is there an existing issue for this?

• I have searched the existing issues

What happened?

During a restore operation:

WARNING:  database "teslamate" has a collation version mismatch
DETAIL:  The database was created using collation version 2.31, but the operating system provides version 2.36.
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.
C

Expected Behavior

No warning

Steps To Reproduce

Apply restoration procedure

Relevant log output

WARNING:  database "teslamate" has a collation version mismatch
DETAIL:  The database was created using collation version 2.31, but the operating system provides version 2.36.
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.
C

Screenshots

No response

Additional data

No response

Type of installation

Docker

Version

v1.28.3

[GaPhi]

After restoration, I launched the following command to remove the warning:

docker exec -i teslamate-db psql -U teslamate <<<"ALTER DATABASE teslamate REFRESH COLLATION VERSION"

[JakobLichterfeld]

Can you please share your restore steps in detail? As https://docs.teslamate.org/docs/maintenance/backup_restore should cover the migration

[GaPhi]

Backup achieved with:

docker exec teslamate-db pg_dump -U teslamate teslamate | gzip --rsyncable - >teslamate-database.sql.gz

Restoration with:

docker exec -i teslamate-db psql -U teslamate <<EOF
 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';
EOF
zcat teslamate-database.sql.gz | docker exec -i teslamate-db psql -U teslamate

# Now with this additional step
docker exec -i teslamate-db psql -U teslamate <<<"ALTER DATABASE teslamate REFRESH COLLATION VERSION"        

[JakobLichterfeld]

Thanks a lot!

So we need to include the Collation version in the migration. Thanks for your research!