Обсуждение: Index Corrupted ?
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
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 ...
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
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).
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).
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/
Вложения
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/