Thread: Streaming Replication, can't select row in backup without specifying collation
Streaming Replication, can't select row in backup without specifying collation
From
Kelly Burkhart
Date:
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)
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)
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
Re: Streaming Replication, can't select row in backup without specifying collation
From
Adrian Klaver
Date:
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