Обсуждение: Index Corrupted ?

Поиск
Список
Период
Сортировка

Index Corrupted ?

От
Sébastien Boutté
Дата:
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



Re: Index Corrupted ?

От
Julien Rouhaud
Дата:
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).

AW: Index Corrupted ?

От
"Dischner, Anton"
Дата:
Hi,

maybe running thhe exact same Software in a docker container on your Windows makes sense?

best,

xx

-----Ursprüngliche Nachricht-----
Von: Julien Rouhaud <rjuju123@gmail.com> 
Gesendet: Freitag, 15. Oktober 2021 10:42
An: Sébastien Boutté <sebastien.boutte@smalltox.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Betreff: Re: Index Corrupted ?

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
collatabledatatypes 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).

Re: Index Corrupted ?

От
Johannes Truschnigg
Дата:
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/
Вложения

Re: Index Corrupted ?

От
Sébastien Boutté
Дата:
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/