Re: Clarification on the release notes of postgresql 12 regarding pg_upgrade

But the problem in this case is probably this note:

> This means that a REINDEX
<https://www.postgresql.org/docs/12/sql-reindex.html> operation on an
index pg_upgrade'd from a previous release could potentially fail.

You can't REINDEX safely regarding that note.

pá 4. 10. 2019 v 16:06 odesílatel Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
napsal:

> On Fri, 2019-10-04 at 10:00 -0300, Marcelo Lacerda wrote:
> > There are a few instances where the release notes seem to indicate
> > that the administrator should use pg_dump to upgrade a database so
> > that improvements on btree can be available.
> >
> > Here are they:
> >
> > 1.
> >
> > >In new btree indexes, the maximum index entry length is reduced by
> > eight bytes, to improve handling of duplicate entries (Peter
> > Geoghegan)
> > > This means that a REINDEX operation on an index pg_upgrade'd from a
> > previous release could potentially fail.
> >
> >
> > 2.
> > >Improve performance and space utilization of btree indexes with many
> > duplicates (Peter Geoghegan, Heikki Linnakangas)
> > >...
> > >Indexes pg_upgrade'd from previous releases will not have these
> > benefits.
> >
> > 3.
> > >Allow multi-column btree indexes to be smaller (Peter Geoghegan,
> > Heikki Linnakangas)
> > >...
> > >Indexes pg_upgrade'd from previous releases will not have these
> > benefits.
> >
> >
> > My questions are:
> >
> > 1. Is this a current limitation of pg_upgrade that will be dealt
> > afterwards?
> >
> > 2. Are we going to see more of such cases were pg_upgrade leaves the
> > database incompatible with newer features.
> >
> > 3. What's the recommendation for administrators with databases that
> > are too large to be upgraded with pg_dump?
>
> pg_upgrade doesn't touch the index data, so it cannot rewrite indexes
> to take advantage of these improvements.
>
> There is no incompatibility involved.
>
> You can always REINDEX some indexes later.
> Now that we have REINDEX CONCURRENTLY, it shouldn't hurt as much.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
>