Content-Type: multipart/alternative;
 boundary="------------4LmsFseVFRdOgL7bSYZVydeF"
Message-ID: <200eccb1-188e-49ee-9360-c3a7acb19c2c@gmail.com>
Date: Thu, 8 Jan 2026 16:34:40 +0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
From: Rihad <grihad@gmail.com>
Subject: Collation again here
To: pgsql-general General <pgsql-general@postgresql.org>
Content-Language: en-US
Archived-At: <https://www.postgresql.org/message-id/200eccb1-188e-49ee-9360-c3a7acb19c2c%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------4LmsFseVFRdOgL7bSYZVydeF
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hi, guys. Just pg_upgraded our PG from 13.x to 18.1, rebuilt all indexes 
concurrently and issued ALTER DATABASE foo REFRESH COLLATION VERSION

Everything's fine on the master server, no warnings etc. Then I set up a 
replica using pg_basebackup, and there when trying to access the DB 
using psql I get:


WARNING:  database "foo" has a collation version mismatch
DETAIL:  The database was created using collation version 43.0, but the 
operating system provides version 34.0.
HINT:  Rebuild all objects in this database that use the default 
collation and run ALTER DATABASE foo REFRESH COLLATION VERSION, or build 
PostgreSQL with the right library version.
psql (18.1)
Type "help" for help.


(and I can't issue those same commands on the read-only replica).


The OS are both FreeBSD, but the master runs 14.3, the replica runs 
13.5. PostgreSQL packages are built with ICU support by default, the 
versions used are identical: icu-76.1


Could it be that the OS (libc) needs to be the same version? I hoped it 
would be enough to use the same ICU.


Thanks for any tips.

--------------4LmsFseVFRdOgL7bSYZVydeF
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi, guys. Just pg_upgraded our PG from 13.x to 18.1, rebuilt all
      indexes concurrently and issued  <span
        style="font-family:monospace"><span
          style="color:#ff54ff;background-color:#ffffff;">ALTER DATABASE
          foo REFRESH COLLATION VERSION</span><br>
      </span></p>
    <p>Everything's fine on the master server, no warnings etc. Then I
      set up a replica using pg_basebackup, and there when trying to
      access the DB using psql I get:</p>
    <p><br>
    </p>
    <p> <span style="font-family:monospace"><span
          style="color:#000000;background-color:#ffffff;">WARNING:
           database "foo" has a collation version mismatch</span><span
          style="color:#000000;background-color:#ffffff;"> </span><br>
        <span style="color:#000000;background-color:#ffffff;">DETAIL:
           The database was created using collation version 43.0, but
          the operating system provides version 34.0.</span><span
          style="color:#000000;background-color:#ffffff;"> </span><br>
        <span style="color:#000000;background-color:#ffffff;">HINT:
           Rebuild all objects in this database that use the default
          collation and run ALTER DATABASE foo REFRESH COLLATION
          VERSION, or build PostgreSQL with the right library version.</span><span
          style="color:#000000;background-color:#ffffff;"> </span><br>
        <span style="color:#000000;background-color:#ffffff;">psql
          (18.1)</span><span
          style="color:#000000;background-color:#ffffff;"> </span><br>
        <span style="color:#000000;background-color:#ffffff;">Type
          "help" for help.</span><br>
        <span style="color:#000000;background-color:#ffffff;"></span><br>
      </span></p>
    <p><span style="font-family:monospace"><br>
      </span></p>
    <p><span style="font-family:monospace">(and I can't issue those same
        commands on the read-only replica).</span></p>
    <p><span style="font-family:monospace"><br>
      </span></p>
    <p>The OS are both FreeBSD, but the master runs 14.3, the replica
      runs 13.5. PostgreSQL packages are built with ICU support by
      default, the versions used are identical:  <span
        style="font-family:monospace"><span
          style="color:#000000;background-color:#ffffff;">icu-76.1</span><br>
      </span></p>
    <p><span style="font-family:monospace"><br>
      </span></p>
    <p>Could it be that the OS (libc) needs to be the same version? I
      hoped it would be enough to use the same ICU.</p>
    <p><br>
    </p>
    <p>Thanks for any tips.</p>
  </body>
</html>

--------------4LmsFseVFRdOgL7bSYZVydeF--