MIME-Version: 1.0
References: <SA1PR09MB73117897B54EBA26D2530CC1B9C82@SA1PR09MB7311.namprd09.prod.outlook.com>
In-Reply-To: <SA1PR09MB73117897B54EBA26D2530CC1B9C82@SA1PR09MB7311.namprd09.prod.outlook.com>
From: Kenneth Barber <kbarber@salesforce.com>
Date: Thu, 20 Jun 2024 19:31:49 +1000
Message-ID: <CAP2OPAWB1F5a2Oy9GL63xhtzfTEDvXuYVjvw2sXSN4OSw6ATyw@mail.gmail.com>
Subject: Re: Help. The database was created using collation version 2.17, but
 the operating system provides version 2.34.
To: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAP2OPAWB1F5a2Oy9GL63xhtzfTEDvXuYVjvw2sXSN4OSw6ATyw%40mail.gmail.com>
Precedence: bulk

> I am in the process of migrating DB to Alma9 host. The databse
> is rather large - few TBs.
>
> I have run pg_basebackup on Alma9 host and established replication from production to it. The idea is to quickly switch from master to this new host during downtime.
>
> Establishing replication went fine. Source postgresql version is 15.6, destination is 15.7

What replication did you use? If it's streaming, you will have this
problem. If you can use logical replication and something like:

https://github.com/dimitri/pgcopydb

It will allow you to use the logical replication trick to lower
downtime. It involves copying the database using a parallel transfer
(and some pg_dump/pg_restore wrapping for the structural stuff), and
using logical replication to keep the target up to date. It will allow
you to keep writing to your source DB while it moves, but at the cost
of disabling DDL while it's happening.

Look at the pgcopydb clone --follow documentation for more info.

I'm sure there are other options people can recommend also.

I would test it thoroughly beforehand to make sure it's a fit.

> When I psql into replica I get:
>
> WARNING:  database "xxx" has a collation version mismatch
> DETAIL:  The database was created using collation version 2.17, but the operating system provides version 2.34.
> HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE xxx REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
>
> Looking up the issue the solution seems to be
>
>   REINDEX database xxx
>   ALTER DATABASE xxx REFRESH COLLATION VERSION

Here we've taken two approaches when we just "copied the disks over"
so to speak:

* Backport the collation library (tricky, I can explain this deeper,
but it's tricky)
* Reindex after migration (slow but less tricky)

We had this problem going from xenial to focal, and we had to pin to
something compatible with the xenial libc.

> But this defeats the whole idea of having short downtime because REINDEX will take forever.
>
> What is this "or build PostgreSQL with the right library version"?
> Is this about 15.7 vs 15.6 or is it about different glibc version between RH7 and Alma9?
>
> Is there a better way to handle it? I cannot afford long downtime.
> This came up rather unexpectedly and I am now in a tight situation having to find solution fast. I do not recall having similar issue when going from RH6 to RH7.

Unfortunately you've hit a bad problem that a few of us have probably
already been through. At least you don't have thousands of these
things :-).

ken.