MIME-Version: 1.0
From: Ekaterina Amez Gonzalez <registrosekaterina@gmail.com>
Date: Fri, 20 Dec 2024 11:06:29 +0100
Message-ID: <CAC5zuTqssvYehaG3maYNeWYmvURN+h5xG7KiOoSeYtQKhCLV_g@mail.gmail.com>
Subject: Streaming replication problem with collation
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008eb4390629b0cd33"
Archived-At: <https://www.postgresql.org/message-id/CAC5zuTqssvYehaG3maYNeWYmvURN%2Bh5xG7KiOoSeYtQKhCLV_g%40mail.gmail.com>
Precedence: bulk

--0000000000008eb4390629b0cd33
Content-Type: text/plain; charset="UTF-8"

Hi List,

I'm making some tests in order to prepare a db migration. We have version
9.6 over CentOS 7 and we're going to migrate to version 15 over Rocky Linux
9. Of course there is a no downtime requirement or I wouldn't be here
asking.

I was previously aware of the problem with different glibc version between
systems before I started my tests, but I tried it anyway. I first upgraded
CentOS version to 15 and then made a streaming replication to the other
server (Rocky). After that I encountered the next warning when connecting
to new hot standby database:

WARNING: database my_db 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 my_db REFRESH COLLATION VERSION, or build PostgreSQL
with the right library version.

I tried what was suggested: reindexing and running "refresh collation"
alter after that and everything seems to work ok so this looks like an easy
wat to migrate from one server to another. Plus I feel more comfortable
using streaming replication than logical replication, and also I find it
more useful when you need to replicate the whole cluster.

So my question is: is there anything I'm missing here, some kind of problem
that could hit my face after moving to the new server?

Thanks in advance,

Ekaterina

--0000000000008eb4390629b0cd33
Content-Type: text/html; charset="UTF-8"

<div dir="ltr"><div>Hi List,</div><div><br></div><div>I&#39;m making some tests in order to
 prepare a db migration. We have version 9.6 over CentOS 7 and we&#39;re 
going to migrate to version 15 over Rocky Linux 9. Of course there is a 
no downtime requirement or I wouldn&#39;t be here asking.<br></div><div><br></div><div>I
 was previously aware of the problem with different glibc version 
between systems before I started my tests, but I tried it anyway. I 
first upgraded CentOS version to 15 and then made a streaming 
replication to the other server (Rocky). After that I encountered the 
next warning when connecting to new hot standby database:</div><div><p>WARNING:  database my_db has a collation version mismatch<br>DETAIL:  The database was created using collation version 2.17, but the operating system provides version 2.34.<br>HINT:
  Rebuild all objects in this database that use the default collation 
and run ALTER DATABASE my_db REFRESH COLLATION VERSION, or build 
PostgreSQL with the right library version.</p><p>I tried what was 
suggested: reindexing and running &quot;refresh collation&quot; alter after that 
and everything seems to work ok so this looks like an easy wat to 
migrate from one server to another. Plus I feel more comfortable using 
streaming replication than logical replication, and also I find it more 
useful when you need to replicate the whole cluster. <br></p><p>So my 
question is: is there anything I&#39;m missing here, some kind of problem 
that could hit my face after moving to the new server?</p><p>Thanks in advance,</p><p>Ekaterina</p></div></div>

--0000000000008eb4390629b0cd33--