pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex Corrupted ?
4+ messages / 3 participants
[nested] [flat]
* Index Corrupted ?
@ 2021-10-15 08:18 Sébastien Boutté <sebastien.boutte@smalltox.com>
2021-10-15 08:41 ` Re: Index Corrupted ? Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Sébastien Boutté @ 2021-10-15 08:18 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
I've a strange problem with my upgrade of postgresql 13 (Previously 9.5).
I've two machines Server and Dev :
Server
CENTOS 7
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=
Postgresql 13.4
Encoding UTF8
Collation en_US.TF8
CharacterType en_US.UTF8
Dev
Windows 10 English
Postgresql 13.4
Restore via pg_base_backup + wals reintegration standby.signal
Encoding UTF8
Collation en_US.UTF8
CharacterType en_US.UTF8
Here is my problem :
Server
select * from table where indexed_column = 'XXX' => 1 Row
select * from table where indexed_column like 'XXX' => 1 Row
Dev
select * from table where indexed_column = 'XXX' => 0 Row. Strange ???
select * from table where indexed_column like 'XXX' => 1 Row
reindex index indexed_column;
select * from table where indexed_column = 'XXX' => 1 Row. It's working ...
I've tried to reindex production database but it has not resolved anything.
Have you some ideas ?
Thanks
Sebastien
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index Corrupted ?
2021-10-15 08:18 Index Corrupted ? Sébastien Boutté <sebastien.boutte@smalltox.com>
@ 2021-10-15 08:41 ` Julien Rouhaud <rjuju123@gmail.com>
2021-10-15 08:47 ` Re: Index Corrupted ? Johannes Truschnigg <johannes@truschnigg.info>
0 siblings, 1 reply; 4+ messages in thread
From: Julien Rouhaud @ 2021-10-15 08:41 UTC (permalink / raw)
To: Sébastien Boutté <sebastien.boutte@smalltox.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, Oct 15, 2021 at 4:19 PM Sébastien Boutté
<sebastien.boutte@smalltox.com> wrote:
>
> Server
> CENTOS 7
>
> Dev
> Windows 10 English
> Postgresql 13.4
> Restore via pg_base_backup + wals reintegration standby.signal
Your problem is here. You can't use physical between different system
unless they have exactly the same collation library, with the same
version, and that's clearly not the case between GNU/Linux and
Windows. Your indexes on collatable datatypes are pretty much
guaranteed to be immediately corrupted.
> I've tried to reindex production database but it has not resolved anything.
Indeed, only rebuilding all impacted indexes on the dev server will
work, or using logical replication (or a pg_dump / pg_restore).
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index Corrupted ?
2021-10-15 08:18 Index Corrupted ? Sébastien Boutté <sebastien.boutte@smalltox.com>
2021-10-15 08:41 ` Re: Index Corrupted ? Julien Rouhaud <rjuju123@gmail.com>
@ 2021-10-15 08:47 ` Johannes Truschnigg <johannes@truschnigg.info>
2021-10-15 10:20 ` Re: Index Corrupted ? Sébastien Boutté <sebastien.boutte@smalltox.com>
0 siblings, 1 reply; 4+ messages in thread
From: Johannes Truschnigg @ 2021-10-15 08:47 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: Sébastien Boutté <sebastien.boutte@smalltox.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, Oct 15, 2021 at 04:41:41PM +0800, Julien Rouhaud wrote:
> [...]
> Indeed, only rebuilding all impacted indexes on the dev server will
> work, or using logical replication (or a pg_dump / pg_restore).
Using the same ICU release on both platforms should be an OK workaround/fix,
too. That might require installation of alternative postgres packages however;
maybe even custom compilation.
--
with best regards:
- Johannes Truschnigg ( johannes@truschnigg.info )
www: https://johannes.truschnigg.info/
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index Corrupted ?
2021-10-15 08:18 Index Corrupted ? Sébastien Boutté <sebastien.boutte@smalltox.com>
2021-10-15 08:41 ` Re: Index Corrupted ? Julien Rouhaud <rjuju123@gmail.com>
2021-10-15 08:47 ` Re: Index Corrupted ? Johannes Truschnigg <johannes@truschnigg.info>
@ 2021-10-15 10:20 ` Sébastien Boutté <sebastien.boutte@smalltox.com>
0 siblings, 0 replies; 4+ messages in thread
From: Sébastien Boutté @ 2021-10-15 10:20 UTC (permalink / raw)
To: Johannes Truschnigg <johannes@truschnigg.info>; +Cc: Julien Rouhaud <rjuju123@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Thanks for your answers i will check to find a viable solution.
Le ven. 15 oct. 2021 à 10:47, Johannes Truschnigg <johannes@truschnigg.info>
a écrit :
> On Fri, Oct 15, 2021 at 04:41:41PM +0800, Julien Rouhaud wrote:
> > [...]
> > Indeed, only rebuilding all impacted indexes on the dev server will
> > work, or using logical replication (or a pg_dump / pg_restore).
>
> Using the same ICU release on both platforms should be an OK
> workaround/fix,
> too. That might require installation of alternative postgres packages
> however;
> maybe even custom compilation.
>
> --
> with best regards:
> - Johannes Truschnigg ( johannes@truschnigg.info )
>
> www: https://johannes.truschnigg.info/
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-10-15 10:20 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-15 08:18 Index Corrupted ? Sébastien Boutté <sebastien.boutte@smalltox.com>
2021-10-15 08:41 ` Julien Rouhaud <rjuju123@gmail.com>
2021-10-15 08:47 ` Johannes Truschnigg <johannes@truschnigg.info>
2021-10-15 10:20 ` Sébastien Boutté <sebastien.boutte@smalltox.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox