Pingu
Computer MySQL PostgreSQL Books Publications
Spielereien Kanu Business TopoDB POI Klettersteigen History TransPool Thermal Baden Brokenstuben Goldwaschen
Blog Contact
Shinguz
Google
/ch/open

PostgreSQL for MariaDB and MySQL Admins - Upgrade and collations

Problem

After a system upgrade there can be a problem with the collations:

Example:

$ psql
WARNING:  database "postgres" has a collation version mismatch
DETAIL:  The database was created using collation version 2.27, but the operating system provides version 2.35.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
psql (15.2 (Ubuntu 15.2-1.pgdg18.04+1))
Type "help" for help.

Changing the database:

test=# \c test
WARNING:  database "test" has a collation version mismatch
DETAIL:  The database was created using collation version 2.27, but the operating system provides version 2.35.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE test REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
You are now connected to database "test" as user "postgres".

Investigation

GLIBC of O/S:

$ ldd --version | grep LIBC
ldd (Ubuntu GLIBC 2.35-0ubuntu3.8) 2.35

$ /usr/lib/x86_64-linux-gnu/libc.so.6 --version | head -n 1
GNU C Library (Ubuntu GLIBC 2.35-0ubuntu3.8) stable release version 2.35.

GLIBC of PostgreSQL database???

test=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 test      | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |            | libc            |
(4 rows)

Queries:

SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
       pg_describe_object(classid, objid, objsubid) AS "Object"
  FROM pg_depend d JOIN pg_collation c
       ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
  WHERE c.collversion <> pg_collation_actual_version(c.oid)
  ORDER BY 1, 2
;

SELECT DISTINCT indrelid::regclass::text, indexrelid::regclass::text, collname, pg_get_indexdef(indexrelid)
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s
  JOIN pg_collation c ON coll=c.oid
WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX')
;
SELECT datname AS "Name", encoding AS "Encoding", datlocprovider AS "Locale Provider", datcollate AS "Collate", datctype AS "Ctype", daticulocale AS "ICU Locale", datcollversion AS "Collation Version"
  FROM pg_database
 WHERE datistemplate = false
;
   Name   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | Collation Version
----------+----------+-----------------+-------------+-------------+------------+-------------------
 postgres |        6 | c               | en_GB.UTF-8 | en_GB.UTF-8 |            | 2.35
 test     |        6 | c               | en_GB.UTF-8 | en_GB.UTF-8 |            | 2.35
(2 rows)

Solution

test=# ALTER DATABASE postgres REFRESH COLLATION VERSION;
NOTICE:  changing version from 2.27 to 2.35
ALTER DATABASE

test=# ALTER DATABASE test REFRESH COLLATION VERSION;
NOTICE:  changing version from 2.27 to 2.35
ALTER DATABASE

Sources