pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16593: pg_upgrade make corrupt UK indexes
5+ messages / 5 participants
[nested] [flat]
* BUG #16593: pg_upgrade make corrupt UK indexes
@ 2020-08-25 22:55 PG Bug reporting form <noreply@postgresql.org>
2020-08-26 05:37 ` Re: BUG #16593: pg_upgrade make corrupt UK indexes Nick Cleaton <nick@cleaton.net>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2020-08-25 22:55 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: hali19790320@gmail.com
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-t...
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16593: pg_upgrade make corrupt UK indexes
2020-08-25 22:55 BUG #16593: pg_upgrade make corrupt UK indexes PG Bug reporting form <noreply@postgresql.org>
@ 2020-08-26 05:37 ` Nick Cleaton <nick@cleaton.net>
2020-08-26 07:49 ` Re: BUG #16593: pg_upgrade make corrupt UK indexes Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 5+ messages in thread
From: Nick Cleaton @ 2020-08-26 05:37 UTC (permalink / raw)
To: hali19790320@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16593: pg_upgrade make corrupt UK indexes
2020-08-25 22:55 BUG #16593: pg_upgrade make corrupt UK indexes PG Bug reporting form <noreply@postgresql.org>
2020-08-26 05:37 ` Re: BUG #16593: pg_upgrade make corrupt UK indexes Nick Cleaton <nick@cleaton.net>
@ 2020-08-26 07:49 ` Magnus Hagander <magnus@hagander.net>
2020-08-26 22:57 ` Re: BUG #16593: pg_upgrade make corrupt UK indexes Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 5+ messages in thread
From: Magnus Hagander @ 2020-08-26 07:49 UTC (permalink / raw)
To: Nick Cleaton <nick@cleaton.net>; +Cc: hali19790320@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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.
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16593: pg_upgrade make corrupt UK indexes
2020-08-25 22:55 BUG #16593: pg_upgrade make corrupt UK indexes PG Bug reporting form <noreply@postgresql.org>
2020-08-26 05:37 ` Re: BUG #16593: pg_upgrade make corrupt UK indexes Nick Cleaton <nick@cleaton.net>
2020-08-26 07:49 ` Re: BUG #16593: pg_upgrade make corrupt UK indexes Magnus Hagander <magnus@hagander.net>
@ 2020-08-26 22:57 ` Bruce Momjian <bruce@momjian.us>
2020-08-27 07:13 ` Re: BUG #16593: pg_upgrade make corrupt UK indexes László Tóth <hali19790320@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bruce Momjian @ 2020-08-26 22:57 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: Nick Cleaton <nick@cleaton.net>; hali19790320@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16593: pg_upgrade make corrupt UK indexes
2020-08-25 22:55 BUG #16593: pg_upgrade make corrupt UK indexes PG Bug reporting form <noreply@postgresql.org>
2020-08-26 05:37 ` Re: BUG #16593: pg_upgrade make corrupt UK indexes Nick Cleaton <nick@cleaton.net>
2020-08-26 07:49 ` Re: BUG #16593: pg_upgrade make corrupt UK indexes Magnus Hagander <magnus@hagander.net>
2020-08-26 22:57 ` Re: BUG #16593: pg_upgrade make corrupt UK indexes Bruce Momjian <bruce@momjian.us>
@ 2020-08-27 07:13 ` László Tóth <hali19790320@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: László Tóth @ 2020-08-27 07:13 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Magnus Hagander <magnus@hagander.net>; Nick Cleaton <nick@cleaton.net>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-08-27 07:13 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-25 22:55 BUG #16593: pg_upgrade make corrupt UK indexes PG Bug reporting form <noreply@postgresql.org>
2020-08-26 05:37 ` Nick Cleaton <nick@cleaton.net>
2020-08-26 07:49 ` Magnus Hagander <magnus@hagander.net>
2020-08-26 22:57 ` Bruce Momjian <bruce@momjian.us>
2020-08-27 07:13 ` László Tóth <hali19790320@gmail.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