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 inconsistent
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/>
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 inconsistentAnd 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):
Show quoted text
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.comThe usefulness of a cup is in its emptiness, Bruce Lee