Thread: BUG #16593: pg_upgrade make corrupt UK indexes
The following bug has been logged on the website: Bug reference: 16593 Logged by: László Tóth Email address: hali19790320@gmail.com PostgreSQL version: 9.6.19 Operating system: centos7 Description: We upgrade from 9.6 Centos7 to 12.3 Centos 8 1. Stop 9.6 2. rsync 9.6 data and bin files from Centos7 to Centos8 (pre installed 12) 3. pg_upgrade 4. ./analyze_new_cluster.sh 5. ./delete_old_cluster.sh We found amcheck and throwed exception! But yes, by amcheck wiki https://www.postgresql.org/docs/10/amcheck.html Whe select is a little bit missunderstandable. I think it will be a little bit more understandable if write the function is for check one index. So the query where statement (LIMIT 10 and AND n.nspname = 'pg_catalog' ) ignores a lot of serious index validation! It will be very useful a checklist how can we be sure the upgrade is succesful! Thank you! Here documented it: https://stackoverflow.com/questions/63588123/postgres-pg-upgrade-corrupt-indexes-uk-missing-values-though-missing-records-b
On Tue, 25 Aug 2020 at 23:55, PG Bug reporting form <noreply@postgresql.org> wrote:
We upgrade from 9.6 Centos7 to 12.3 Centos 8
This from https://www.postgresql.org/docs/12/amcheck.html#id-1.11.7.11.9 might be relevent:
> Comparisons of datums of a collatable type like
text
must be immutable (just as all comparisons used for B-Tree index scans must be immutable), which implies that operating system collation rules must never change. Though rare, updates to operating system collation rules can cause these issues. More commonly, an inconsistency in the collation order between a master server and a standby server is implicated, possibly because the major operating system version in use is inconsistentOn Wed, Aug 26, 2020 at 7:38 AM Nick Cleaton <nick@cleaton.net> wrote:
On Tue, 25 Aug 2020 at 23:55, PG Bug reporting form <noreply@postgresql.org> wrote:We upgrade from 9.6 Centos7 to 12.3 Centos 8This from https://www.postgresql.org/docs/12/amcheck.html#id-1.11.7.11.9 might be relevent:> Comparisons of datums of a collatable type liketext
must be immutable (just as all comparisons used for B-Tree index scans must be immutable), which implies that operating system collation rules must never change. Though rare, updates to operating system collation rules can cause these issues. More commonly, an inconsistency in the collation order between a master server and a standby server is implicated, possibly because the major operating system version in use is inconsistent
And in particular also see https://wiki.postgresql.org/wiki/Locale_data_changes, which shows that the step from Centos7 to Centos8 is one that causes this problem. You need to reindex all text/varchar indexes when you make that upgrade.
On Wed, Aug 26, 2020 at 09:49:00AM +0200, Magnus Hagander wrote: > > > On Wed, Aug 26, 2020 at 7:38 AM Nick Cleaton <nick@cleaton.net> wrote: > > On Tue, 25 Aug 2020 at 23:55, PG Bug reporting form <noreply@postgresql.org > > wrote: > > We upgrade from 9.6 Centos7 to 12.3 Centos 8 > > > This from https://www.postgresql.org/docs/12/amcheck.html#id-1.11.7.11.9 > might be relevent: > > > Comparisons of datums of a collatable type like text must be immutable > (just as all comparisons used for B-Tree index scans must be immutable), > which implies that operating system collation rules must never change. > Though rare, updates to operating system collation rules can cause these > issues. More commonly, an inconsistency in the collation order between a > master server and a standby server is implicated, possibly because the > major operating system version in use is inconsistent > > > And in particular also see https://wiki.postgresql.org/wiki/Locale_data_changes > , which shows that the step from Centos7 to Centos8 is one that causes this > problem. You need to reindex all text/varchar indexes when you make that > upgrade. And we should have a script ready for people to do this. :-) -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Agreed!
Bruce Momjian <bruce@momjian.us> ezt írta (időpont: 2020. aug. 27., Cs, 0:57):
On Wed, Aug 26, 2020 at 09:49:00AM +0200, Magnus Hagander wrote:
>
>
> On Wed, Aug 26, 2020 at 7:38 AM Nick Cleaton <nick@cleaton.net> wrote:
>
> On Tue, 25 Aug 2020 at 23:55, PG Bug reporting form <noreply@postgresql.org
> > wrote:
>
> We upgrade from 9.6 Centos7 to 12.3 Centos 8
>
>
> This from https://www.postgresql.org/docs/12/amcheck.html#id-1.11.7.11.9
> might be relevent:
>
> > Comparisons of datums of a collatable type like text must be immutable
> (just as all comparisons used for B-Tree index scans must be immutable),
> which implies that operating system collation rules must never change.
> Though rare, updates to operating system collation rules can cause these
> issues. More commonly, an inconsistency in the collation order between a
> master server and a standby server is implicated, possibly because the
> major operating system version in use is inconsistent
>
>
> And in particular also see https://wiki.postgresql.org/wiki/Locale_data_changes
> , which shows that the step from Centos7 to Centos8 is one that causes this
> problem. You need to reindex all text/varchar indexes when you make that
> upgrade.
And we should have a script ready for people to do this. :-)
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee