Subject: Re: Upgrading from Postgresql 9.3.8 to 9.6.10
To: pgsql-admin@lists.postgresql.org
References: 
 <CACuBw0i+mWJHT5-wO+SnERiED-oEGGSFkxw_M+4dGbnNm5VgAw@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <0e3636ec-ba56-a765-2a5a-84ef01072604@gmail.com>
Date: Tue, 26 Mar 2019 01:14:32 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.4.0
MIME-Version: 1.0
In-Reply-To: 
 <CACuBw0i+mWJHT5-wO+SnERiED-oEGGSFkxw_M+4dGbnNm5VgAw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

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.