Content-Type: multipart/alternative;
 boundary="------------Pd0nx16ieba90iX0pJAiLukC"
Message-ID: <9f5996c1-abab-40da-8dd5-d56f483b22b1@gmail.com>
Date: Thu, 8 Jan 2026 17:18:53 +0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Collation again here
To: Dominique Devienne <ddevienne@gmail.com>
Cc: pgsql-general General <pgsql-general@postgresql.org>
References: <200eccb1-188e-49ee-9360-c3a7acb19c2c@gmail.com>
 <CAFCRh--H4a9kV4R=22DB6cL+KxcJ3A6-3tg_Y77KgTdDJECBwA@mail.gmail.com>
Content-Language: en-US
From: Rihad <grihad@gmail.com>
In-Reply-To: <CAFCRh--H4a9kV4R=22DB6cL+KxcJ3A6-3tg_Y77KgTdDJECBwA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/9f5996c1-abab-40da-8dd5-d56f483b22b1%40gmail.com>
Precedence: bulk

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

On 1/8/26 4:48 PM, Dominique Devienne wrote:
> On Thu, Jan 8, 2026 at 1:39 PM Rihad<grihad@gmail.com> wrote:
>> 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.
>> 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.
> Depends what Provider you used in those DBs:
> https://www.postgresql.org/docs/current/locale.html#LOCALE-PROVIDERS
> Having the same ICU is good, but do your DBs use that provider? If
> not, and you're using the libc one, then yes, the libc version
> matters.
> Myself I use the new builtin provider to avoid OS portability issues. --DD

Hi, our locale/collate/whatever is en_US.UTF-8 (as set by these 
pg_upgrade flags: --encoding=utf-8 --locale=en_US.UTF-8)


Looking into pg_collation system table that collation has 
collprovide="c". First I thought "c" meant libc, but this article states 
that "c" means PG Internal provider, and libc would have been "l".

https://medium.com/@adarsh2801/understanding-collations-in-postgresql-648e4fa333e1

 1. */PostgreSQL Internal Provider (‘c’) /*: Introduced in Postgres 15.
    This built-in collation support is System/OS agnostic.
 2. */System Library Provider (‘l’) : /*Uses GNU C library and hence is
    OS locale dependent.
 3. */ICU — International Components for Unicode (‘i’) : /*Uses ICU
    library for unicode-aware collation.


We only have "i" & "c" in pg_collation. And we aren't using any of "i" 
it seems. All this locale/encoding/collate stuff is too much for me to 
handle, sorry)

So if we are using the internal (builtin) "c" provider how come the PG 
18.1 run on FreeBSD 13.5 version shows warnings that the system version 
is 34.0?

The article must be wrong I guess.

Then upgrading 13.5 to 14.3 is our only option.

--------------Pd0nx16ieba90iX0pJAiLukC
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>
    <div class="moz-cite-prefix">On 1/8/26 4:48 PM, Dominique Devienne
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAFCRh--H4a9kV4R=22DB6cL+KxcJ3A6-3tg_Y77KgTdDJECBwA@mail.gmail.com">
      <pre wrap="" class="moz-quote-pre">On Thu, Jan 8, 2026 at 1:39 PM Rihad <a class="moz-txt-link-rfc2396E" href="mailto:grihad@gmail.com">&lt;grihad@gmail.com&gt;</a> wrote:
</pre>
      <blockquote type="cite">
        <pre wrap="" class="moz-quote-pre">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.
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.
</pre>
      </blockquote>
      <pre wrap="" class="moz-quote-pre">
Depends what Provider you used in those DBs:
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/current/locale.html#LOCALE-PROVIDERS">https://www.postgresql.org/docs/current/locale.html#LOCALE-PROVIDERS</a>
Having the same ICU is good, but do your DBs use that provider? If
not, and you're using the libc one, then yes, the libc version
matters.
Myself I use the new builtin provider to avoid OS portability issues. --DD
</pre>
    </blockquote>
    <p>Hi, our locale/collate/whatever is 
      <span style="font-family:monospace"><span
          style="color:#000000;background-color:#ffffff;">en_US.UTF-8</span> (as
        set by these pg_upgrade flags: --encoding=utf-8
        --locale=en_US.UTF-8)</span></p>
    <p><br>
    </p>
    <p><br>
    </p>
    <p>Looking into pg_collation system table that collation has
      collprovide="c". First I thought "c" meant libc, but this article
      states that "c" means PG Internal provider, and libc would have
      been "l".</p>
    <p><a class="moz-txt-link-freetext" href="https://medium.com/@adarsh2801/understanding-collations-in-postgresql-648e4fa333e1">https://medium.com/@adarsh2801/understanding-collations-in-postgresql-648e4fa333e1</a></p>
    <ol class="">
      <li id="d444"
class="mu mv hl mw b mx my mz na nb nc nd ne nf ng nh ni nj nk nl nm nn no np nq nr qv qo qp bl"
        data-selectable-paragraph=""><strong class="mw hm"><em
            class="ns">PostgreSQL Internal Provider (‘c’) </em></strong>:
        Introduced in Postgres 15. This built-in collation support is
        System/OS agnostic.</li>
      <li id="9dd1"
class="mu mv hl mw b mx qq mz na nb qr nd ne nf qs nh ni nj qt nl nm nn qu np nq nr qv qo qp bl"
        data-selectable-paragraph=""><strong class="mw hm"><em
            class="ns">System Library Provider (‘l’) : </em></strong>Uses
        GNU C library and hence is OS locale dependent.</li>
      <li id="6cfb"
class="mu mv hl mw b mx qq mz na nb qr nd ne nf qs nh ni nj qt nl nm nn qu np nq nr qv qo qp bl"
        data-selectable-paragraph=""><strong class="mw hm"><em
            class="ns">ICU — International Components for Unicode (‘i’)
            : </em></strong>Uses ICU library for unicode-aware
        collation.</li>
    </ol>
    <p><br>
    </p>
    <p>We only have "i" &amp; "c" in pg_collation. And we aren't using
      any of "i" it seems. All this locale/encoding/collate stuff is too
      much for me to handle, sorry)</p>
    <p>So if we are using the internal (builtin) "c" provider how come
      the PG 18.1 run on FreeBSD 13.5 version shows warnings that the
      system version is 34.0?</p>
    <p>The article must be wrong I guess.</p>
    <p>Then upgrading 13.5 to 14.3 is our only option.</p>
  </body>
</html>

--------------Pd0nx16ieba90iX0pJAiLukC--