Streaming Replication, can't select row in backup without specifying collation

From: Kelly Burkhart <kelly(dot)burkhart(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Streaming Replication, can't select row in backup without specifying collation
Date: 2022-07-25 22:36:51
Message-ID: CAND8VyCTY9g4J==p0bmiYN2ekJ_QNDxmjoJkW97i8oWFxrTnbA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a primary and backup database running 12.10 and synced with
streaming replication. I have a simple table that is returning different
results for the same query in the primary vs backup database.

On the primary DB:

> select tag::bytea, * from sentinel where tag =
'quote_merge_locs_mnj_NSX_20220719';
tag |
tag | event_ts
----------------------------------------------------------------------+-----------------------------------+-------------------------------
\x71756f74655f6d657267655f6c6f63735f6d6e6a5f4e53585f3230323230373139 |
quote_merge_locs_mnj_NSX_20220719 | 2022-07-19 20:29:14.114238-05
(1 row)

On the backup:

> select tag::bytea, * from sentinel where tag =
'quote_merge_locs_mnj_NSX_20220719';
tag | tag | event_ts
-----+-----+----------
(0 rows)

> select tag::bytea, * from sentinel where tag =
'quote_merge_locs_mnj_NSX_20220719' collate "C";
tag |
tag | event_ts
----------------------------------------------------------------------+-----------------------------------+-------------------------------
\x71756f74655f6d657267655f6c6f63735f6d6e6a5f4e53585f3230323230373139 |
quote_merge_locs_mnj_NSX_20220719 | 2022-07-19 20:29:14.114238-05
(1 row)

Can anyone explain why the backup does not show the row without specifying
a collation?

The primary is running CentOS 7.4, the backup is running CentOS 8. The
backup was created from a basebackup. The postgres binaries were built on
the OS on which they're running. Neither were built with ICU (which I
didn't know existed until tracking this down...). Both databases show
LC_COLLATE=en_US.UTF-8.

The query "select * from pg_collation where collname like '%en_US%';"
returns identical results for both databases.

I'm concerned that somehow I've created a backup that is not compatible in
some mysterious way with my primary.

Can anyone provide any insight on what is going on?

Thank you,

-Kelly

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2022-07-25 22:43:02 Re: Streaming Replication, can't select row in backup without specifying collation
Previous Message Alvaro Herrera 2022-07-25 20:40:22 Re: Password reset link / 'less' does not exit in psql version 13.4