Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

On Fri, Oct 28, 2022 at 2:41 AM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:
>
> > Up until 11.17, the source of the docker images was tag "postgres:11"
> > (from https://hub.docker.com/_/postgres) for 11.17 the tag became
> > "postgres:11-bullseye" but as far as i could tell it was just a
> > difference of tagging policy there. Everything else is kept the same
> > when building our custom docker image (with pg_cron, wal2json and
> > oracle_fdw). But.. I can see for example, that the PG 11.12 docker
> > image used Debian 9.13 (PG 11.17 uses Debian 11.5 according to
> > /etc/debian_version).
>
> Ok, So that's an ugrade from Debian 9 to Debian 11. That's definitely
> not just a "difference of tagging policy", That's two major versions of
> the OS!
>
> I don't remember exactly when the big incompatible libc upgrade was, but
> it was very likely somewhere between Debian 9 and Debian 11, so you have
> to rebuild all you indexes. Since you didn't do that immediately after
> the upgrade you now have data corruption which you have to fix manually.

Well, I am going to remember this lesson for the rest of my life.
After using this same scheme for upgrading the pg-s for over 3 years,
this really caught me as a surprise - apparently I got comfortable and
I did not pay close attention to this particular change with the
docker hub images.

We have reverted back to our previous version as the comparison based
on queries showed the data to be more intact with it.

By the way, index rebuild while completing successfully did not fix
the indexes - the data in the tables was still missing even after the
successful rebuild command. I guess the only option left is to drop
and re-create these one by one.

Thank you all for feedback and help!

With best regards,
--
Kristjan Mustkivi

Email: kristjan(dot)mustkivi(at)gmail(dot)com