pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHot Standby setup - collation version mismatch
7+ messages / 5 participants
[nested] [flat]
* Hot Standby setup - collation version mismatch
@ 2025-02-25 17:43 Sbob <sbob@quadratum-braccas.com>
0 siblings, 2 replies; 7+ messages in thread
From: Sbob @ 2025-02-25 17:43 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
All;
I have 2 aws ec2 nodes and I want to setup a hot standby.
The master is running amazon linux and here are the postgres packages
installed:
*[root@ip-10-0-3-55 ~]# rpm -qa | grep postgres
postgresql-private-libs-15.2-1.module_el9+264+92dde3f0.x86_64
postgresql-15.2-1.module_el9+264+92dde3f0.x86_64
postgresql-server-15.2-1.module_el9+264+92dde3f0.x86_64*
The standby is running Alma 8 and here are the postgres packages installed:
*[root@ip-10-0-3-228 ~]# rpm -qa | grep postgres
postgresql15-server-15.11-1PGDG.rhel8.x86_64
postgresql15-libs-15.11-1PGDG.rhel8.x86_64
postgresql15-15.11-1PGDG.rhel8.x86_64
postgresql15-contrib-15.11-1PGDG.rhel8.x86_64*
*
*
Once I run pg_basebackup I start the standby and get this:
[postgres@ip-10-0-3-228 ~]$ /usr/pgsql-15/bin/pg_ctl start
waiting for server to start....2025-02-24 07:07:26.800 UTC [180985]
LOG: redirecting log output to logging collector process
2025-02-24 07:07:26.800 UTC [180985] HINT: Future log output will
appear in directory "log".
done
server started
[postgres@ip-10-0-3-228 ~]$ psql
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.
and once the db is online I check and the standby is not in recovery mode:
psql (15.11)
Type "help" for help.
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
postgres=#
Thanks in advance for any advice
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Hot Standby setup - collation version mismatch
@ 2025-02-25 17:47 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Sbob <sbob@quadratum-braccas.com>
1 sibling, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2025-02-25 17:47 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, Feb 25, 2025 at 12:43 PM Sbob <sbob@quadratum-braccas.com> wrote:
> All;
>
>
> I have 2 aws ec2 nodes and I want to setup a hot standby.
>
>
> The master is running amazon linux and here are the postgres packages
> installed:
>
>
>
>
> *[root@ip-10-0-3-55 ~]# rpm -qa | grep postgres
> postgresql-private-libs-15.2-1.module_el9+264+92dde3f0.x86_64
> postgresql-15.2-1.module_el9+264+92dde3f0.x86_64
> postgresql-server-15.2-1.module_el9+264+92dde3f0.x86_64*
>
>
> The standby is running Alma 8 and here are the postgres packages installed:
>
>
>
>
>
> *[root@ip-10-0-3-228 ~]# rpm -qa | grep postgres
> postgresql15-server-15.11-1PGDG.rhel8.x86_64
> postgresql15-libs-15.11-1PGDG.rhel8.x86_64
> postgresql15-15.11-1PGDG.rhel8.x86_64
> postgresql15-contrib-15.11-1PGDG.rhel8.x86_64*
>
>
> Once I run pg_basebackup I start the standby and get this:
>
>
> [postgres@ip-10-0-3-228 ~]$ /usr/pgsql-15/bin/pg_ctl start
> waiting for server to start....2025-02-24 07:07:26.800 UTC [180985] LOG:
> redirecting log output to logging collector process
> 2025-02-24 07:07:26.800 UTC [180985] HINT: Future log output will appear
> in directory "log".
> done
> server started
> [postgres@ip-10-0-3-228 ~]$ psql
> 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.
>
>
> and once the db is online I check and the standby is not in recovery mode:
>
> psql (15.11)
> Type "help" for help.
>
> postgres=# select pg_is_in_recovery();
> pg_is_in_recovery
> -------------------
> f
> (1 row)
>
> postgres=#
>
>
> Thanks in advance for any advice
>
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.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Hot Standby setup - collation version mismatch
@ 2025-02-25 18:25 J T <jorge.torralba@gmail.com>
parent: Sbob <sbob@quadratum-braccas.com>
1 sibling, 0 replies; 7+ messages in thread
From: J T @ 2025-02-25 18:25 UTC (permalink / raw)
To: Sbob <sbob@quadratum-braccas.com>; +Cc: pgsql-admin@lists.postgresql.org
Most likely due to a different C lib version on the different OS.
On Tue, Feb 25, 2025 at 9:43 AM Sbob <sbob@quadratum-braccas.com> wrote:
> All;
>
>
> I have 2 aws ec2 nodes and I want to setup a hot standby.
>
>
> The master is running amazon linux and here are the postgres packages
> installed:
>
>
>
>
> *[root@ip-10-0-3-55 ~]# rpm -qa | grep postgres
> postgresql-private-libs-15.2-1.module_el9+264+92dde3f0.x86_64
> postgresql-15.2-1.module_el9+264+92dde3f0.x86_64
> postgresql-server-15.2-1.module_el9+264+92dde3f0.x86_64*
>
>
> The standby is running Alma 8 and here are the postgres packages installed:
>
>
>
>
>
> *[root@ip-10-0-3-228 ~]# rpm -qa | grep postgres
> postgresql15-server-15.11-1PGDG.rhel8.x86_64
> postgresql15-libs-15.11-1PGDG.rhel8.x86_64
> postgresql15-15.11-1PGDG.rhel8.x86_64
> postgresql15-contrib-15.11-1PGDG.rhel8.x86_64*
>
>
> Once I run pg_basebackup I start the standby and get this:
>
>
> [postgres@ip-10-0-3-228 ~]$ /usr/pgsql-15/bin/pg_ctl start
> waiting for server to start....2025-02-24 07:07:26.800 UTC [180985] LOG:
> redirecting log output to logging collector process
> 2025-02-24 07:07:26.800 UTC [180985] HINT: Future log output will appear
> in directory "log".
> done
> server started
> [postgres@ip-10-0-3-228 ~]$ psql
> 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.
>
>
> and once the db is online I check and the standby is not in recovery mode:
>
> psql (15.11)
> Type "help" for help.
>
> postgres=# select pg_is_in_recovery();
> pg_is_in_recovery
> -------------------
> f
> (1 row)
>
> postgres=#
>
>
> Thanks in advance for any advice
>
>
>
>
--
Thanks,
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential
information. If you are not the intended recipient, please do not print,
copy, retransmit, disseminate or otherwise use the information. Please
indicate to the sender that you have received this email in error and
delete the copy you received. Thank You.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Hot Standby setup - collation version mismatch
@ 2025-02-25 19:17 Joe Conway <mail@joeconway.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Joe Conway @ 2025-02-25 19:17 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Hot Standby setup - collation version mismatch
@ 2025-02-25 19:46 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2025-02-25 19:46 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, Feb 25, 2025 at 2:17 PM Joe Conway <mail@joeconway.com> wrote:
> 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.
>
Isn't "_el9" in the package names a reference to RHEL9 compatibility?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Hot Standby setup - collation version mismatch
@ 2025-02-25 21:11 Joe Conway <mail@joeconway.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Joe Conway @ 2025-02-25 21:11 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
(Please reply-all on these lists per community norms -- i.e. do not
remove recipients from the To: or CC:)
On 2/25/25 14:46, Ron Johnson wrote:
> On Tue, Feb 25, 2025 at 2:17 PM Joe Conway <mail@joeconway.com
> <mailto:mail@joeconway.com>> wrote:
> 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
> He actually said Amazon Linux (based on glibc version I would guess
> AL2023) & Alma 8, but the point remains the same.
>
> Isn't "_el9" in the package names a reference to RHEL9 compatibility?
Sort of. The Postgres packages appear to be from CentOS Stream:
https://rpmfind.net/linux/RPM/centos-stream/9/appstream/x86_64/postgresql-private-libs-15.2-1.module...
I don't have RHEL 9 handy at the moment to check what its repo has for
Postgres packages.
But the OP clearly stated they were running Amazon Linux on the primary,
so perhaps these Postgres packages were downloaded from somewhere that
does not match the aforementioned distro -- yet another potential issue
for various reasons.
Or perhaps they got their own distro wrong ¯\_(ツ)_/¯
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Hot Standby setup - collation version mismatch
@ 2025-02-26 09:37 Juan Rincon Gonzalez <juanrincongonzalez347@gmail.com>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 7+ messages in thread
From: Juan Rincon Gonzalez @ 2025-02-26 09:37 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
I am tire of begging for people to be part of my life am not going to hold
anyone or force anyone no body to be part of me or keep forcing to use my
brain fuck this am not doing that anymore or look for the system i don't
keep it is all yours if that crown is to heavy for someone dont look for me
to help find your own way am tire of ignorant people.
Juan Rincon
On Tue, Feb 25, 2025, 1:11 PM Joe Conway <mail@joeconway.com> wrote:
> (Please reply-all on these lists per community norms -- i.e. do not
> remove recipients from the To: or CC:)
>
> On 2/25/25 14:46, Ron Johnson wrote:
> > On Tue, Feb 25, 2025 at 2:17 PM Joe Conway <mail@joeconway.com
> > <mailto:mail@joeconway.com>> wrote:
> > 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
>
> > He actually said Amazon Linux (based on glibc version I would guess
> > AL2023) & Alma 8, but the point remains the same.
> >
> > Isn't "_el9" in the package names a reference to RHEL9 compatibility?
>
> Sort of. The Postgres packages appear to be from CentOS Stream:
>
>
> https://rpmfind.net/linux/RPM/centos-stream/9/appstream/x86_64/postgresql-private-libs-15.2-1.module...
>
> I don't have RHEL 9 handy at the moment to check what its repo has for
> Postgres packages.
>
> But the OP clearly stated they were running Amazon Linux on the primary,
> so perhaps these Postgres packages were downloaded from somewhere that
> does not match the aforementioned distro -- yet another potential issue
> for various reasons.
>
> Or perhaps they got their own distro wrong ¯\_(ツ)_/¯
>
>
> --
> Joe Conway
> PostgreSQL Contributors Team
> RDS Open Source Databases
> Amazon Web Services: https://aws.amazon.com
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-02-26 09:37 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-25 17:43 Hot Standby setup - collation version mismatch Sbob <sbob@quadratum-braccas.com>
2025-02-25 17:47 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-02-25 19:17 ` Joe Conway <mail@joeconway.com>
2025-02-25 19:46 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-02-25 21:11 ` Joe Conway <mail@joeconway.com>
2025-02-26 09:37 ` Juan Rincon Gonzalez <juanrincongonzalez347@gmail.com>
2025-02-25 18:25 ` J T <jorge.torralba@gmail.com>
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