From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Kelly Burkhart <kelly(dot)burkhart(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Streaming Replication, can't select row in backup without specifying collation |
Date: | 2022-07-25 22:43:02 |
Message-ID: | 73263e4e-7f78-5540-af05-bae1aaaf2741@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Rama Krishnan | 2022-07-26 13:27:06 | |
Previous Message | Kelly Burkhart | 2022-07-25 22:36:51 | Streaming Replication, can't select row in backup without specifying collation |