Re: Upgrading from Postgresql 9.3.8 to 9.6.10

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.