Postgresql Help

Operating Systems & Open Source Open Source & Web-Based
,
1

Hi, Apologies if this is the wrong category, I just need some guidance from some postgres wizards.

I’m getting the following when interacting with a database (name obfuscated, but it does have the - in it)

WARNING:  database "database-name" has a collation version mismatch
DETAIL:  The database was created using collation version 2.38, but the operating system provides version 2.40.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE "database-name" REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

As far as I can find online, the command it tells me to run is the correct one and should be working, but I get

ALTER DATABASE "database-name" REFRESH COLLATION VERSION;
ERROR:  syntax error at or near "REFRESH"
LINE 1: ALTER DATABASE "database-name" REFRESH COLLATION VERSION;

Anyone able to tell me what I’m doing wrong? I’m able to check the collation version and that comes back as up to date

postgres=# ALTER COLLATION "en_GB.utf8" REFRESH VERSION;
NOTICE:  version has not changed
ALTER COLLATION
2

You need to rebuild indexes first according to the PostgreSQL documentation.

"[…]all objects depending on the collation should be rebuilt, for example, using REINDEX. When that is done, the collation version can be refreshed using the command ALTER COLLATION … REFRESH VERSION. "

Use REINDEX (VERBOSE) DATABASE <dbname>;.
Can take a long time for a large database. The VERBOSE option prints each updated index so you can follow progress.

1 Like
3

Thank you, will give this a go!