From: Joe Conway <mail@joeconway.com>
To: Ron Johnson <ronljohnsonjr@gmail.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Hot Standby setup - collation version mismatch
Date: Tue, 25 Feb 2025 14:17:20 -0500
Message-ID: <4331b9ed-9d8f-4ce0-9ad1-7d18d0178230@joeconway.com> (raw)
In-Reply-To: <CANzqJaB8VZi__-8r9f1ncZPYLS58vKx8ia9H06jkP_WA4Zi-fQ@mail.gmail.com>
References: <b1280a26-bf73-4c0b-9f99-99294dee4d4f@quadratum-braccas.com>
<CANzqJaB8VZi__-8r9f1ncZPYLS58vKx8ia9H06jkP_WA4Zi-fQ@mail.gmail.com>
On 2/25/25 12:47, Ron Johnson wrote:
> On Tue, Feb 25, 2025 at 12:43 PM Sbob wrote:
> I have 2 aws ec2 nodes and I want to setup a hot standby.
>
> The master is running amazon linux
> The standby is running Alma 8
> Once I run pg_basebackup I start the standby and get this:
> WARNING: database "postgres" has a collation version mismatch
> DETAIL: The database was created using collation version 2.34, but
> the operating system provides version 2.28.
> 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.
> One is RHEL 8, and the other is RHEL 9. Never do that using streaming
> replication, for the very reason you're seeing:
> WARNING: database "postgres" has a collation version mismatch
>
> Use logical replication, or upgrade the hot standby server to Alma 9.
He actually said Amazon Linux (based on glibc version I would guess
AL2023) & Alma 8, but the point remains the same.
Do not run a streaming replica which does not match the primary with
respect to glibc version. The two versions of glibc have different sort
orders, which will potentially result in incorrect query results on your
replica (for indexes on collatable columns, and depending on the actual
characters in use). In fact, if possible it would be best to match the
replica distro to the primary distro.
Additionally if you ever promote the replica to a writable primary, the
indexes are effectively corrupt and will allow actual heap data
corruption occur in that you could end up with duplicate values in
otherwise unique columns/primary keys.
If you are interested in the gory details, you can watch them here:
https://www.youtube.com/watch?v=kvsQza6TA70
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mail@joeconway.com, ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Hot Standby setup - collation version mismatch
In-Reply-To: <4331b9ed-9d8f-4ce0-9ad1-7d18d0178230@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox