[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Streaming Replication, can't select row in backup without specifying collation
From:       Adrian Klaver <adrian.klaver () aklaver ! com>
Date:       2022-07-25 22:43:02
Message-ID: 73263e4e-7f78-5540-af05-bae1aaaf2741 () aklaver ! com
[Download RAW message or body]

On 7/25/22 15:36, Kelly Burkhart wrote:
> 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?

See:

https://wiki.postgresql.org/wiki/Locale_data_changes

In particular:

https://wiki.postgresql.org/wiki/Locale_data_changes#RHEL.2FCentOS

> 
> Thank you,
> 
> -Kelly
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


[prev in list] [next in list] [prev in thread] [next in thread]