Managed Postgres database upgrade - Collation issue

[matmicro]

As a Managed PosgreSQL Database, I am facing an issue which looks related to Posgres Version upgrade through Managed DB (i started using Managed DB on pg10, and i am now migrated to pg16).

I am trying to create a new database :

CREATE DATABASE "test" ENCODING 'unicode'  TEMPLATE "template1";

The template is:

          Name          |  Owner   | Encoding |   Collate         |    Ctype          | ICU Locale | Locale Provider |   Access privileges
--------------------+----------+----------+---------------+--------------+------------+-----------------+-----------------------
 template1             | postgres | UTF8        | en_US.UTF-8 | en_US.UTF-8 |                   | libc                    | postgres=CTc/postgres+=c/postgres

Then it throws the error:

ERROR: template database "template1" has a collation version mismatch
DETAIL:  The template database was created using collation version 2.36, but the operating system provides version 2.37.
HINT:  Rebuild all objects in the template database that use the default collation and run ALTER DATABASE template1 REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

If i try to refresh collation, i cannot connect to database template1 as it is own by postgres, and we don't have priviledges on it.

> ALTER DATABASE template1 REFRESH COLLATION VERSION;
ERROR:  must be owner of database template1

Could you please investigate on this and provide:

• a wait to fix my current impacted database
• a fix to permanently fix this upgrade related issue

Thanks and regards

[matmicro]

Anyone form OVH can cast a look on this problem please ?

[frabe1579]

As a personal consideration, not OVH related, but strongly recommended by PostgreSQL, never do a major version upgrade directly on live data. Always do a backup, followed by a clean installation and a restoration from previously backed up data. This is primarly necessary because of low level changes in data structures. AFAIK, ovh does not manage backup-restore during major upgrades. If Backup-Clean-Restore is not possible, try pg_upgrade.

[thild42]

The issue was solved in February. If you have this issue on an existing PG, check you have applied the latest maintenance. If the issue persists get in touch with our support.