On 3/26/19 12:51 AM, Yuri Niyazov wrote:
> We have an old cluster, running 9.3.8, that we are trying to upgrade to
> 9.6.10.
>
> After running pg_upgrade and starting the server, and testing some common
> queries against it, we experienced the following error:
>
> PG::IndexCorrupted: ERROR: index "table_pkey" contains unexpected zero
> page at block 17021871 HINT: Please REINDEX it.
>
> So, if I am reading this correctly, there is an index that 9.3.8 created,
> and it recognizes that index as OK, but 9.6.10 thinks that index is
> corrupt. Since this happened on one index, it seems reasonable to assume
> that this could happen on any index in that database,
Yes, *could*.
> and we need to reindex the entire database.
That does not seem justified.
> Now, this is a live application, and we would like to minimize continuous
> downtime (multiple short downtimes are fine), so what we are considering
> doing is a manual reindex: for each index, create a new one, and then drop
> the old one, and after all that, upgrade.
Won't work if you've got lots of FK constraints.
But "REINDEX INDEX foo;" works just fine.
> However, we are leery of doing this reindexing using 9.3.8, since it's
> already demonstrated itself to be unreliable.
>
> Which version should we use to reindex? Just the latest 9.3.X? Was there a
> known bug with older versions missing corrupted pages in indices?
>
> PS: Just in case I am glaringly doing something wrong:
>
> Here's our mechanism of doing the upgrade: we create a streaming replica
> from a basebackup, and then at some point turn off writes to the primary,
> convert the replica to another primary, and then run pg_upgrade on this
> new primary.
--
Angular momentum makes the world go 'round.