Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux